Oracle DBA Tips and Techniques

By Arjun Raja

Archive for December, 2009

AUTOMATIC BLOCK REPAIR IN 11G RELEASE 2

Posted by Arjun Raja on December 29, 2009

SET UP ACTIVE DATAGUARD AND TEST AUTOMATIC BLOCK CORRUPTION REPAIR.

Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.

In addition, RECOVER BLOCK is enhanced to restore blocks from a physical standby database. The physical standby database must be in real-time query mode – Also called active dataguard.

1 – SETUP ACTIVE DATAGUARD –

Firstly ON STANDBY DATABASE –

alter database recover managed standby database cancel;

alter database open read only;

alter database recover managed standby database using current logfile disconnect;

Confirm that standby database is open –

SQL> select file_name from dba_data_files;

FILE_NAME

FILE_NAME
——————————————————————————–
/u02/oradata/bozo/users01.dbf
/u02/oradata/bozo/undotbs01.dbf
/u02/oradata/bozo/sysaux01.dbf
/u02/oradata/bozo/system01.dbf

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL>

Try to create a table in the standby database –

SQL> create table test (col_1 number(10));
create table test (col_1 number(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Check for table test on STANDBY –

SQL> desc test ;
ERROR:
ORA-04043: object test does not exist

Check for table test on PRIMARY

SQL> desc test;
ERROR:
ORA-04043: object test does not exist

Create table test on PRIMARY –

SQL> create table test (col_1 number(10));

Table created.

SQL>

Now desc test on both primary and standby –

Primary and standby –

Desc test;

SQL> Desc test;
Name Null? Type
—————————————– ——– —————————-
COL_1 NUMBER(10)

SQL>

So while the standby database is open in READ ONLY mode, media recovery is still in progress.

2 – BLOCK CORRUPTION – AUTOMATIC MEDIA RECOVERY USING BLOCK FROM STANDBY SITE.

Simulate a block corruption –

Firstly check the file users01.dbf for any corruption –

cd /u02/oradata/arjun

[oracle@redhat346 arjun]$ dbv file=users01.dbf

DBVERIFY: Release 11.2.0.1.0 – Production on Wed Dec 23 11:03:47 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = /u02/oradata/arjun/users01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 1280
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1153
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1303084 (0.1303084)
[oracle@redhat346 arjun]$

Run following command to corrupt datafile – /u02/oradata/arjun/users01.dbf

vi corrupt.sh

dd if=/dev/zero of=/u02/oradata/arjun/users01.dbf bs=8192 seek=139 count=2 conv=notrunc < backup validate tablespace users format ‘/u02/oradat/arjun/%U%s%d%t’;

Starting backup at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oradata/arjun/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 FAILED 0 1134 1280 1374339
File Name: /u02/oradata/arjun/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 11
Index 0 0
Other 2 135

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc for details
Finished backup at 24-DEC-09

Backup validate proves block corruption.

Also can be confirmed via this view –

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

Now since this database is a primary database in a dataguard configuration with active dataguard in place, AUTO BMR( Block Media Recovery) will kick in on the primary database box and fetch a non-corrupt block from the standby database and restore and recover this block.

Check of alert log on PRIMARY database –

Thu Dec 24 10:30:52 2009
Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during validation
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during validation
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data

Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008b (file 5, block 139)
Reread (file 5, block 139) found same corrupt data
Starting background process ABMR
Thu Dec 24 11:17:55 2009
ABMR started with pid=36, OS id=19550
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 139)
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008c (file 5, block 140)
Reread (file 5, block 140) found same corrupt data
Requesting Auto BMR for (file# 5, block# 140)
Waiting Auto BMR response for (file# 5, block# 139)
Auto BMR successful
Waiting Auto BMR response for (file# 5, block# 140)
Auto BMR response already received
Auto BMR successful

Now check for corrupt blocks –

cd /u02/oradata/arjun

dbv file=users01.dbf

INCASE THE DATAFILE HEADER ITSELF IS CORRUPT, THEN AUTOMATIC BLOCK MEDIA RECOVERY IS NOT POSSIBLE.

Starting background process ABMR
Wed Dec 23 11:17:08 2009
ABMR started with pid=31, OS id=5840
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 3)
Waiting Auto BMR response for (file# 5, block# 3)
Wed Dec 23 11:17:09 2009
Read of datafile ‘/u02/oradata/arjun/arjun01.dbf’ (fno 5) header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Rereading datafile 5 header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Errors in file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: ‘/u02/oradata/arjun/users01.dbf’
ORA-01210: data file header is media corrupt
Auto BMR failed for (file# 5, block# 3); error=Invalid file header
Wed Dec 23 11:17:10 2009
Corrupt Block Found
TSN = 6, TSNAME = ARJUN
RFN = 5, BLK = 3, RDBA = 20971523
OBJN = 4, OBJD = -1, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Wed Dec 23 11:17:10 2009
Checker run found 1 new persistent data failures

In this case – restoration of the datafile from a backup is required –

OFCOURSE DATABASE BLOCK CORRUPTIONS CAN BE RECOVERED USING RMAN – again RMAN gets the good blocks from the standby site incase active dataguard is running.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

[oracle@redhat346 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Dec 24 11:11:08 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ARJUN (DBID=3469830310)

RMAN> RECOVER CORRUPTION LIST;

RMAN> recover corruption list;

Starting recover at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
finished standby search, restored 2 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 24-DEC-09

RMAN>

SQL > SQL> select * from v$database_block_corruption;

no rows selected

SQL> SQL>

Posted in High Availability, Oracle 11g | Leave a Comment »

DSMC – RETAIN FOR 2 YEARS AND QUERY MANAGEMENT CLASS

Posted by Arjun Raja on December 1, 2009

To query the MANAGMENT CLASS set up for your machine by the TSM administrator –

dsmc q mgmt

Domain Name : XXXUNIX
Activated Policy Set Name : STANDARD
Activation date/time : 05/11/09 13:16:54
Default Mgmt Class Name : STANDARD
Grace Period Backup Retn. : 30 day(s)
Grace Period Archive Retn.: 730 day(s)

MgmtClass Name : XXXXXXX_DB
Description : Dont include in daily incremental

MgmtClass Name : RETAIN2YEARS
Description : Retention period of Two Years

ON XXXXXX –

cd $COMMON_TOP/admin/out/XXXXXX

dsmc archive -archmc=RETAIN2YEARS -description=”ebus_logs”
“/u01/fXXXXX/finXXXXX/admin/out/XXXXXX/*.*” -su=yes > EBUS_COPY.logs

cd $COMMON_TOP/admin/log/XXXXXX

dsmc archive -archmc=RETAIN2YEARS -description=”ebus_admin_logs” “/u01/XXXX/XXXXXcomn/admin/log/XXXXXXX/*.*” -su=yes > EBUS_ADMIN_COPY.logs

On XXXXXXX – 2 YEARS RETENTION.

cd /u01/XX/XX/admin/log/XXXXX

nohup dsmc archive -archmc=ARCY2 -description=”ebus_web_admin_logs” “/u01/fXX/fXXn/admin/log/XXX/*.*” -su=yes > EBUS_WEB_ADMIN_COPY.logs &

To query archived files –

dsmc query archive -description=”ebus_web_admin_logs” “/u01/fXX/fXXn/admin/log/XXX/*.*” -su=yes > archived_files.logs

Posted in Backup and Recovery | Leave a Comment »

RMAN WILL NOT DELETE ARCHIVELOG NEEDED FOR RECOVERY ON STANDBY DATABASE

Posted by Arjun Raja on December 1, 2009

RMAN command issued to backup and delete all archivelogs on PRIMARY database.

RMAN > BACKUP ARCHIVELOG ALL DELETE INPUT;

archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_22/o1_mf_1_5182_5fzsdwgv_.arc recid=10340 stamp=700923294
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_23/o1_mf_1_5183_5g3b7jt1_.arc recid=10342 stamp=701038835
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_25/o1_mf_1_5184_5g6pzq0g_.arc recid=10344 stamp=701150202
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_26/o1_mf_1_5185_5gc5c5kw_.arc recid=10346 stamp=701295975
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_27/o1_mf_1_5186_5gdp4vvn_.arc recid=10348 stamp=701345949
RMAN-08137: WARNING: archive log not deleted as it is still needed archive log
filename=/u02/oradata/flash_recovery_area/Test/archivelog/2009_10_27/o1_mf_1_5187_5gdpby1j_.arc thread=1 sequence=5187
channel ORA_SBT_TAPE_1: deleting archive log(s)
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_25/o1_mf_1_5128_596rhksp_.arc recid=10232 stamp=695822449
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_26/o1_mf_1_5129_59bdjhbv_.arc recid=10234 stamp=695941266
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_28/o1_mf_1_5130_59frckcs_.arc recid=10236 stamp=696051699
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_29/o1_mf_1_5131_59jkd348_.arc recid=10238 stamp=696142854

In this case, archivelog sequence 5187 was not yet applied on standby database – hence RMAN will not delete it from the PRIMARY database disk.

This is a safety measure to make sure primary and standby databases can remain in sync

Posted in High Availability | Tagged: , | Leave a Comment »

11g RELEASE 2 – RMAN DUPLICATION OF DATABASE

Posted by Arjun Raja on December 1, 2009

11g release 2 has a very good feature for RMAN duplication which will allow you to duplicate a database even if the target database is not available.

In case a disk backup is available, there is no need to even connect to a catalog, otherwise a conenction to the catalog is necessary to get info from tape.

11g release 1 –

We will try the duplicate command without connecting to the target database – this will fail as can be seen below.

connect catalog rman10d/d10rman@rcatd
connect auxiliary /

run {
allocate auxiliary channel ch1 device type sbt_tape;

SET UNTIL TIME “TO_DATE (’23-11-09 10:57:00′, ‘DD-MM-YY HH24:MI:SS’)”; duplicate target database to test
nofilenamecheck;
}
connected to recovery catalog database

connected to auxiliary database: TEST (not mounted)

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 11/30/2009 13:32:01
RMAN-06171: not connected to target database

Recovery Manager complete.
~
~
11g RELEASE 2 –

export ORACLE_SID=TESTDB

TAKE FULL BACKUP TO DISK –

RMAN> run {
configure channel device type disk format
‘/u02/oradata/backup_TESTDB/TESTDB_%U’;
backup database plus archivelog;
}

piece handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-NOV-09

RMAN>

NOW CREATE DUPLICATE DATABASE – WITHOUT CONNECTING TO TARGET OR CATALOG –

The backup must be accessible on disk in this case.

Ascertain entry for TESTDB1 – duplicate database exisit in tnsnames.ora and listener.ora

Now create an init.ora file for the new duplicate database –

cd $ORACLE_HOME/dbs

export ORACLE_SID=TESTDB1

copy the init.ora of the target database as initTESTDB1.ora – and make changes to reflect TESTDB1

Add foll in new init.ora –

*.db_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′ *.log_file_name_convert=’/u02/oradata/TESTDB’,’/u02/oradata/TESTDB1′

create spfile from pfile;

startup nomount duplicate database – TESTDB1.

export ORACLE_SID=TESTDB1

sql > startup nomount;

exit;

Now begin duplication by only connecting to auxiliary – no connection to target or even catalog –

export ORACLE_SID=TESTDB1

[oracle@redhat346 ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Nov 30 14:06:32 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: TESTDB1 (not mounted)

RMAN> RUN {
DUPLICATE DATABASE TO TESTDB1
BACKUP LOCATION ‘/u02/oradata/backup_TESTDB’; – specify backup location on disk.
}
2> 3> 4>
Starting Duplicate Db at 30-NOV-09

contents of Memory Script:
{
sql clone “alter system set db_name =
”TESTDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”TESTDB1” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from
‘/u02/oradata/backup_TESTDB/TESTDB_07kvld2d_1_1’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”TESTDB” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”TESTDB1” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

Starting restore at 30-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oradata/TESTDB1/control01.ctl
output file name=/u02/oradata/flash_recovery_area/TESTDB1/control02.ctl Finished restore at 30-NOV-09

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
set until scn 1000239;
set newname for datafile 1 to
“/u02/oradata/TESTDB1/system01.dbf”;
set newname for datafile 2 to
“/u02/oradata/TESTDB1/sysaux01.dbf”;
set newname for datafile 3 to
“/u02/oradata/TESTDB1/undotbs01.dbf”;
set newname for datafile 4 to
“/u02/oradata/TESTDB1/users01.dbf”;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 30-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u02/oradata/TESTDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/u02/oradata/TESTDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u02/oradata/TESTDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u02/oradata/TESTDB1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_06kvld1u_1_1
tag=TAG20091130T134638
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 30-NOV-09

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=704297238 file
name=/u02/oradata/TESTDB1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=704297238 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=704297238 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=704297238 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
set until scn 1000239;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 30-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece
/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
channel ORA_AUX_DISK_1: piece
handle=/u02/oradata/backup_TESTDB/TESTDB_08kvld2f_1_1
tag=TAG20091130T134655
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file
name=/u02/oradata/flash_recovery_area/TESTDB1/archivelog/2009_11_30/o1_mf_1_8_5k6r8qnm_.arc RECID=1 STAMP=704297239
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-NOV-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set db_name =
”TESTDB1” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”; shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes

sql statement: alter system set db_name = ”TESTDB1” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes
Variable Size 390074360 bytes
Database Buffers 226492416 bytes
Redo Buffers 7544832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “TESTDB1” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u02/oradata/TESTDB1/redo01.log’ ) SIZE 50 M REUSE, GROUP 2 ( ‘/u02/oradata/TESTDB1/redo02.log’ ) SIZE 50 M REUSE, GROUP 3 ( ‘/u02/oradata/TESTDB1/redo03.log’ ) SIZE 50 M REUSE DATAFILE
‘/u02/oradata/TESTDB1/system01.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/TESTDB1/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u02/oradata/TESTDB1/sysaux01.dbf”, “/u02/oradata/TESTDB1/undotbs01.dbf”,
“/u02/oradata/TESTDB1/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/TESTDB1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/sysaux01.dbf RECID=1 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/undotbs01.dbf RECID=2 STAMP=704297257
cataloged datafile copy
datafile copy file name=/u02/oradata/TESTDB1/users01.dbf RECID=3 STAMP=704297257

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=704297257 file
name=/u02/oradata/TESTDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=704297257 file
name=/u02/oradata/TESTDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=704297257 file
name=/u02/oradata/TESTDB1/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 30-NOV-09

RMAN> exit

Recovery Manager complete.

Posted in Backup and Recovery | Tagged: , , | Leave a Comment »