Case two -- Recover a datafile in primary site from the backup taken in standby site without using c

The same configuration as the case one.[@more@]

1 1> Take a full backup at Standby site
[oracle@RAC02 lyon_Dont_touch]$ rman target / catalog

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 11:07:37 2008

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

connected to target database: O01DMS0A (DBID=231561342, not open)
connected to recovery catalog database

RMAN> backup database;

Starting backup at 20-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ddms02/dms/o02dms0suprt1/sales_02.O01DMS0A
input datafile fno=00007 name=/ddms02/dms/o02dms0suprt1/sales_01.O01DMS0A
input datafile fno=00008 name=/ddms02/dms/o02dms0suprt1/ndx_01.O01DMS0A
input datafile fno=00005 name=/ddms02/dms/o02dms0suprt1/system_02.O01DMS0A
input datafile fno=00003 name=/ddms02/dms/o02dms0suprt1/sysaux_01.O01DMS0A
input datafile fno=00002 name=/ddms02/dms/o02dms0suprt1/undo_01.O01DMS0A
input datafile fno=00001 name=/ddms02/dms/o02dms0suprt1/system_01.O01DMS0A
input datafile fno=00006 name=/ddms02/dms/o02dms0suprt1/business_01.O01DMS0A
input datafile fno=00009 name=/ddms02/dms/o02dms0suprt1/ndx_02.O01DMS0A
input datafile fno=00010 name=/ddms02/dms/o02dms0suprt1/ndx_03.O01DMS0A
input datafile fno=00011 name=/ddms02/dms/o02dms0suprt1/tools_01.O01DMS0A
channel ORA_DISK_1: starting piece 1 at 20-JUN-08
channel ORA_DISK_1: finished piece 1 at 20-JUN-08
piece handle=/archbk/O01DMS0A_44jjd6lh_1_1_BK.bak tag=TAG20080620T111247 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:14:07
Finished backup at 20-JUN-08

Starting Control File and SPFILE Autobackup at 20-JUN-08
piece handle=/ddms02/dms/oracle/10.2.0-64/dbs/c-231561342-20080620-00 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUN-08

RMAN> exit

2>Check information on standby site.

SQL> select group#,sequence#,used,archived,status,FIRST_CHANGE#,LAST_CHANGE# from v$standby_log;

GROUP# SEQUENCE# USED ARC STATUS FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- --- ---------- ------------- ------------
4 550 9795072 YES ACTIVE 2242219 2259436
5 0 512 NO UNASSIGNED 0 0
6 0 512 YES UNASSIGNED 0 0

SQL> select SEQUENCE#,APPLIED,DELETED,STATUS from v$archived_log;

SEQUENCE# APP DEL S
---------- --- --- -
544 YES YES D
545 YES YES D
546 YES YES D
547 YES YES D
548 YES YES D
549 YES YES D

50 rows selected.

2 @Priamry Site.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 550 52428800 1 NO CURRENT
2242219 20-JUN-08

2 1 549 52428800 1 YES INACTIVE
2205458 19-JUN-08

3 1 548 52428800 1 YES INACTIVE
2204995 19-JUN-08

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@RAC01 o01dms0arch]$ mv /ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A /ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A.bak

SQL> startup mount;
ORACLE instance started.

Total System Global Area 683671552 bytes
Fixed Size 1221228 bytes
Variable Size 213912980 bytes
Database Buffers 457179136 bytes
Redo Buffers 11358208 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A'

[oracle@RAC01 o01dms0suprt1]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 15:11:49 2008

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

connected to target database: O01DMS0A (DBID=231561342, not open)

RMAN> catalog start with '/archbk/O01DMS0A';

searching for all files that match the pattern /archbk/O01DMS0A

List of Files Unknown to the Database
=====================================
File Name: /archbk/O01DMS0A_44jjd6lh_1_1_BK.bak

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /archbk/O01DMS0A_44jjd6lh_1_1_BK.bak

RMAN> run
{
set newname for datafile 11 to '/ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A';
restore datafile 11;
recover datafile 11;
}2> 3> 4> 5> 6>

executing command: SET NEWNAME

Starting restore at 20-JUN-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A
channel ORA_DISK_1: reading from backup piece /archbk/O01DMS0A_44jjd6lh_1_1_BK.bak

channel ORA_DISK_1: restored backup piece 1
piece handle=/archbk/O01DMS0A_44jjd6lh_1_1_BK.bak tag=TAG20080620T111247
channel ORA_DISK_1: restore complete, elapsed time: 00:02:08
Finished restore at 20-JUN-08

Starting recover at 20-JUN-08
using channel ORA_DISK_1

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

Finished recover at 20-JUN-08

RMAN> exit

Recovery Manager complete.
[oracle@RAC01 o01dms0suprt1]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 20 15:24:20 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

SQL> alter database open;

Database altered.

SQL> select count(*) from lyon.aaa;

COUNT(*)
----------
12

SQL>

3 Checking after recovery.

@Priamry DB.

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 553 52428800 1 NO CURRENT
2259957 20-JUN-08

2 1 552 52428800 1 YES ACTIVE
2259681 20-JUN-08

3 1 551 52428800 1 YES INACTIVE
2259680 20-JUN-08

@Standby DB.

SQL> select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC9: Archival started
ARCa: Archival started

MESSAGE
--------------------------------------------------------------------------------
ARCb: Archival started
ARCc: Archival started
ARCd: Archival started
ARCe: Archival started
ARCa: Becoming the 'no FAL' ARCH
ARCa: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 2933
RFS[1]: Identified database type as 'physical standby'

MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary thread 1 already marked as open; setting 'closed'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 2935
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary

MESSAGE
--------------------------------------------------------------------------------
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 2937
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Attempt to start background Managed Standby Recovery process

MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /ddms02/dms/o02dms0suprt1/redolog1.O01DMS0A
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /ddms02/dms/o02dms0suprt1/redolog2.O01DMS0A
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /ddms02/dms/o02dms0suprt1/redolog3.O01DMS0A
Clearing online redo logfile 3 complete
Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_531.arc
Media Recovery Waiting for thread 1 sequence 532 (in transit)
Redo Shipping Client Connected as PUBLIC

MESSAGE
--------------------------------------------------------------------------------
-- Connected User is Valid
RFS[4]: Assigned to RFS process 2941
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_532.arc
Media Recovery Waiting for thread 1 sequence 533 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode

MESSAGE
--------------------------------------------------------------------------------
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_533.arc
Media Recovery Waiting for thread 1 sequence 534 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

MESSAGE
--------------------------------------------------------------------------------
Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_534.arc
Media Recovery Waiting for thread 1 sequence 535 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_535.arc
Media Recovery Waiting for thread 1 sequence 536 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary

MESSAGE
--------------------------------------------------------------------------------
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_536.arc
Media Recovery Waiting for thread 1 sequence 537 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_537.arc

MESSAGE
--------------------------------------------------------------------------------
Media Recovery Waiting for thread 1 sequence 538 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_538.arc
Media Recovery Waiting for thread 1 sequence 539 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0

MESSAGE
--------------------------------------------------------------------------------
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_539.arc
Media Recovery Waiting for thread 1 sequence 540 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_540.arc
Media Recovery Waiting for thread 1 sequence 541 (in transit)

MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_541.arc
Media Recovery Waiting for thread 1 sequence 542 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

MESSAGE
--------------------------------------------------------------------------------

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_542.arc
Media Recovery Waiting for thread 1 sequence 543 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_543.arc
Media Recovery Waiting for thread 1 sequence 544 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode

MESSAGE
--------------------------------------------------------------------------------
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_544.arc
Media Recovery Waiting for thread 1 sequence 545 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

MESSAGE
--------------------------------------------------------------------------------
Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_545.arc
Media Recovery Waiting for thread 1 sequence 546 (in transit)
Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_546.arc
Media Recovery Waiting for thread 1 sequence 547
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 4567
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Redo Shipping Client Connected as PUBLIC

MESSAGE
--------------------------------------------------------------------------------
-- Connected User is Valid
RFS[6]: Assigned to RFS process 4569
RFS[6]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[6]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0
1DMS0A'

Redo Shipping Client Connected as PUBLIC

MESSAGE
--------------------------------------------------------------------------------
-- Connected User is Valid
RFS[7]: Assigned to RFS process 4571
RFS[7]: Identified database type as 'physical standby'
RFS[7]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_547.arc
Media Recovery Waiting for thread 1 sequence 548 (in transit)
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 4573

MESSAGE
--------------------------------------------------------------------------------
RFS[8]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[6]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O0
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_548.arc
Media Recovery Waiting for thread 1 sequence 549 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[6]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O0

MESSAGE
--------------------------------------------------------------------------------
1DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_549.arc
Media Recovery Waiting for thread 1 sequence 550 (in transit)
Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_550.arc
Media Recovery Waiting for thread 1 sequence 551
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 6917
RFS[9]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode

MESSAGE
--------------------------------------------------------------------------------
Standby controlfile consistent with primary
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[10]: Assigned to RFS process 6919
RFS[10]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[10]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O
01DMS0A'

MESSAGE
--------------------------------------------------------------------------------

Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[11]: Assigned to RFS process 6921
RFS[11]: Identified database type as 'physical standby'
RFS[11]: Successfully opened standby log 5: '/ddms02/dms/o02dms0suprt1/stdby05.O
01DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_551.arc
Media Recovery Waiting for thread 1 sequence 552 (in transit)
Redo Shipping Client Connected as PUBLIC

MESSAGE
--------------------------------------------------------------------------------
-- Connected User is Valid
RFS[12]: Assigned to RFS process 6928
RFS[12]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[10]: Successfully opened standby log 4: '/ddms02/dms/o02dms0suprt1/stdby04.O
01DMS0A'

Media Recovery Log /ddms02/dms/o02dms0arch/O01DMS0B_arch_655475902_1_552.arcMedia Recovery Waiting for thread 1 sequence 553 (in transit)

195 rows selected.

Reference:

Cataloging an Archived Redo Log: Example

This example assumes that you made operating system copies of archived logs or transferred them from another location, and then added them to the RMAN repository:

CATALOG ARCHIVELOG '?/oradata/archive1_30.dbf', '?/oradata/archive1_31.dbf', 
                   '?/oradata/archive1_32.dbf';

Cataloging a File Copy as an Incremental Backup: Example

The following example catalogs datafile copy users01.bak as an incremental level 0 backup:

CATALOG DATAFILECOPY '?/oradata/users01.bak' LEVEL 0;

Note that you can create datafile copies both with the RMAN BACKUP AS COPY command and by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.

Cataloging Multiple Copies in a Directory: Example

The following example catalogs a directory full of archived logs that were copied into the /tmp/arch_logs directory with an operating system utility:

CATALOG START WITH '/tmp/arch_logs';

Cataloging File in the Flash Recovery Area: Example

The following example catalogs all files in the currently enabled flash recovery area without prompting the user for each one:

CATALOG RECOVERY AREA NOPROMPT;

Cataloging Backup Pieces: Example

The following example catalogs a backup piece that was copied with an operating system utility:

CATALOG BACKUPPIECE '?/oradata/01dmsbj4_1_1.bcp';