Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, June 30, 2010

Restore database with SPFILE and Controlfile

when SPFILE and controlfile backup location are not available or not sure then do the following to do the restore of controlfile and spfile:
but YOU SHOULD KNOW the DBID

when no init.ora or spfile available
use RMAN
>export ORACLE_SID=ABC
>rman

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jun 30 14:16:48 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/UNIX/u0001/oracle/product/10.2.0.1/db_1/dbs/initABC.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1977112 bytes
Variable Size 71308520 bytes
Database Buffers 79691776 bytes
Redo Buffers 6406144 bytes

now restoring the controlfile:

'set controlfile autobackup format' is only necessary if the controlfile autobackup is in a NON default location – set accordingly

RMAN> set dbid=123456789012;
run {
set controlfile autobackup format for device type disk to '\%F'; -----if disk else change type to SBT if TAPE
restore spfile from autobackup;
restore controlfile from autobackup;
startup force mount; --startup FORCE mount' is needed because the instance was originally started without an spfile
}

once DB is mounted .. list the backup files and you all set with restore and recover commands
RMAN> list backup summary;

Friday, June 25, 2010

Trace a SID

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>918,serial_num=>32481);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>918,serial_num=>32481, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>918,serial_num=>32481);

Increase INI_TRANS for heavily logging tables

if the tables are transaction intensive like used for logging then increase ini_trans from default upto 4 or as you deem correct.

verify ini_trans using the below query
select table_name,ini_trans,freelists,freelist_groups from dba_tables
where owner='SIEBEL' AND TABLE_NAME IN
(
'S_DOCK_TXN_LOG')

Monitor Table Usage

I used the below query to get the total physical reads/write by day:

select distinct
to_char(begin_interval_time,'mm/dd') c1,
sum(physical_reads_total) reads,
sum(physical_writes_total) writes
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o
,dba_hist_snapshot sn
where
o.owner = 'SIEBEL'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
object_name = 'S_DOCK_TXN_LOG'
group by to_char(begin_interval_time,'mm/dd')
order by 1;

Wednesday, June 16, 2010

Change DB_NAME and DBID

>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 16 23:36:30 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='/evm05/u0001/oracle/product/10.2.0/db_1/dbs/initNEWDB.ora' mount;
ORACLE instance started.

Total System Global Area 2013265920 bytes
Fixed Size 2041720 bytes
Variable Size 922753160 bytes
Database Buffers 1073741824 bytes
Redo Buffers 14729216 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
HOSTNAME(NEWDB) /export/home/oracle
>nid target=sys/dba2hire@NEWDB dbname=NEWDB

DBNEWID: Release 10.2.0.4.0 - Production on Wed Jun 16 23:37:05 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database OLDDB (DBID=1847680250)

Connected to server version 10.2.0

Control Files in database:
+EVM03_REDO_01/NEWDB/controlfile/current.262.698751897
+EVM03_REDO_02/NEWDB/controlfile/current.257.698751897

Change database ID and database name OLDDB to NEWDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1847680250 to 2196540583
Changing database name from OLDDB to NEWDB
Control File +EVM03_REDO_01/NEWDB/controlfile/current.262.698751897 - modified
Control File +EVM03_REDO_02/NEWDB/controlfile/current.257.698751897 - modified
Datafile +EVM03_DATA_01/NEWDB/datafile/system.264.698752361 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/undo.310.698757225 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/sysaux.266.698752335 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.282.698754487 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.294.698755755 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/tools.290.698754583 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/users.314.698757431 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.292.698755751 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_idx.303.698756719 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.283.698754489 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.302.698756717 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.272.698751923 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.280.698754481 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.300.698756275 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.298.698755761 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.313.698757427 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.312.698757409 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.285.698754493 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.304.698756721 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.275.698753195 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.297.698755759 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.311.698757279 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_idx.270.698751929 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.276.698753197 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.305.698756723 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.271.698751927 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.293.698755753 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.307.698756725 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.287.698754495 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.274.698753193 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.268.698751931 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.299.698756141 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_idx.278.698753199 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.260.698753191 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.273.698751921 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.291.698755747 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.279.698753407 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_idx.306.698756723 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/undo.267.698752293 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.277.698753197 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.269.698751929 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.309.698757203 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_data.281.698754483 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.261.698753187 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/cbar_default_idx.286.698754495 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.262.698753183 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/mydvr_data.288.698754569 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.265.698751915 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.301.698756713 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/undo.296.698755757 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.284.698754491 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.308.698756725 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/large_data.289.698754577 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.263.698751919 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_index.295.698755757 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.317.719316783 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/datafile/evm_data.318.719316813 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/tempfile/temp.316.699704527 - dbid changed, wrote new name
Datafile +EVM03_DATA_01/NEWDB/tempfile/temp.315.699704525 - dbid changed, wrote new name
Control File +EVM03_REDO_01/NEWDB/controlfile/current.262.698751897 - dbid changed, wrote new name
Control File +EVM03_REDO_02/NEWDB/controlfile/current.257.698751897 - dbid changed, wrote new name
Instance shut down

Database name changed to NEWDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWDB changed to 2196540583.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

HOSTNAME(NEWDB) /export/home/oracle
>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 16 23:40:22 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='/evm05/u0001/oracle/product/10.2.0/db_1/dbs/initNEWDB.ora' mount;
ORACLE instance started.

Total System Global Area 2013265920 bytes
Fixed Size 2041720 bytes
Variable Size 922753160 bytes
Database Buffers 1073741824 bytes
Redo Buffers 14729216 bytes
Database mounted.
SQL> show parameter db_name

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_name string
NEWDB
SQL> alter database open resetlogs;

Database altered.

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;