Tuesday 5 November 2013
Restore Standby Database by increment backup of rman if Primary server failed.
Suppose you have already create standby server , it is not synchronized with primary server for one .
If you have kept the rman increment or full backup archive log from backup time to failure time. and auto backup of control file, then you can restore the standby server upto the failure point.
To login on standby use
sys@sid as sysdba
password: enter the password of standby sys(in my case it is slate)
To login on rman use
connect target sys@sid
password
Restore control file to standby from latest auto backup.
sqlplus>startup nomount
C:\Users\Administrator>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 5 14:37:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys@slate2
target database Password:
connected to target database: SLATE (DBID=3523425568, not open)
RMAN>restore standby controlfile from 'c:/backup/C-3523425568-20131105-08';
before starting mount , first change backup location, other wise control file automatic restore the backup.
sqlplus>startup mount;
No catalog your backup
if you already catalog other backup, then first crosscheck them and delete them.
rman target /
crosscheck backup;
delete expired backup;
now catalog your backup
catalog start with 'c:/backup/';
RMAN> catalog start with 'c:/backup1/';
searching for all files that match the pattern c:/backup1/
List of Files Unknown to the Database
=====================================
File Name: C:\BACKUP1\STD_7BOO6T9E_1_1
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-EXAMPLE_FNO-5_75OO6RJP
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SLATE_FNO-6_77OO6RJU
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSAUX_FNO-2_72OO6RI9
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSTEM_FNO-1_73OO6RJ3
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-UNDOTBS1_FNO-3_74OO6RJI
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-USERS_FNO-4_76OO6RJS
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\BACKUP1\STD_7BOO6T9E_1_1
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-EXAMPLE_FNO-5_75OO6RJP
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SLATE_FNO-6_77OO6RJU
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSAUX_FNO-2_72OO6RI9
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSTEM_FNO-1_73OO6RJ3
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-UNDOTBS1_FNO-3_74OO6RJI
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-USERS_FNO-4_76OO6RJS
Point time in Recovery
If you want to recover up to specific time then first catalog backup and archive log till that time.
then enter the following command.
run{
set until time "to_date('2014 Sep 25 10:43','yyyy mon dd hh24:mi')";
restore database ;
recover database;
}
if you first want to recover database and then want to apply log manually then use following command
for point and time recovery do not issue following command, it is only to recover manually
If you have kept the rman increment or full backup archive log from backup time to failure time. and auto backup of control file, then you can restore the standby server upto the failure point.
To login on standby use
sys@sid as sysdba
password: enter the password of standby sys(in my case it is slate)
To login on rman use
connect target sys@sid
password
Restore control file to standby from latest auto backup.
sqlplus>startup nomount
C:\Users\Administrator>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 5 14:37:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys@slate2
target database Password:
connected to target database: SLATE (DBID=3523425568, not open)
RMAN>restore standby controlfile from 'c:/backup/C-3523425568-20131105-08';
before starting mount , first change backup location, other wise control file automatic restore the backup.
sqlplus>startup mount;
No catalog your backup
if you already catalog other backup, then first crosscheck them and delete them.
rman target /
crosscheck backup;
delete expired backup;
now catalog your backup
catalog start with 'c:/backup/';
RMAN> catalog start with 'c:/backup1/';
searching for all files that match the pattern c:/backup1/
List of Files Unknown to the Database
=====================================
File Name: C:\BACKUP1\STD_7BOO6T9E_1_1
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-EXAMPLE_FNO-5_75OO6RJP
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SLATE_FNO-6_77OO6RJU
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSAUX_FNO-2_72OO6RI9
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSTEM_FNO-1_73OO6RJ3
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-UNDOTBS1_FNO-3_74OO6RJI
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-USERS_FNO-4_76OO6RJS
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\BACKUP1\STD_7BOO6T9E_1_1
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-EXAMPLE_FNO-5_75OO6RJP
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SLATE_FNO-6_77OO6RJU
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSAUX_FNO-2_72OO6RI9
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-SYSTEM_FNO-1_73OO6RJ3
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-UNDOTBS1_FNO-3_74OO6RJI
File Name: C:\BACKUP1\STD_DATA_D-SLATE_I-3523425568_TS-USERS_FNO-4_76OO6RJS
RMAN> catalog start with 'c:/backup/';
searching for all files that match the pattern c:/backup/
no files found to be unknown to the database
if you find this error, then make sure backup is available there
if backup available there then change location of backup and recatalog it.
Point time in Recovery
If you want to recover up to specific time then first catalog backup and archive log till that time.
then enter the following command.
run{
set until time "to_date('2014 Sep 25 10:43','yyyy mon dd hh24:mi')";
restore database ;
recover database;
}
if you first want to recover database and then want to apply log manually then use following command
RMAN> restore database;
Starting restore at 05-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=351 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-SYSTEM_FNO-1_73OO6RJ3
destination for restore of datafile 00001: C:\ORACLE\ORADATA\SLATE\SYSTEM01.DBF
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=C:\ORACLE\ORADATA\SLATE\SYSTEM01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=350 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-SYSAUX_FNO-2_72OO6RI9
destination for restore of datafile 00002: C:\ORACLE\ORADATA\SLATE\SYSAUX01.DBF
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=C:\ORACLE\ORADATA\SLATE\SYSAUX01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=352 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-UNDOTBS1_FNO-3_74OO6RJI
destination for restore of datafile 00003: C:\ORACLE\ORADATA\SLATE\UNDOTBS01.DB
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=C:\ORACLE\ORADATA\SLATE\UNDOTBS01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=353 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-USERS_FNO-4_76OO6RJS
destination for restore of datafile 00004: C:\ORACLE\ORADATA\SLATE\USERS01.DBF
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=C:\ORACLE\ORADATA\SLATE\USERS01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=348 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-EXAMPLE_FNO-5_75OO6RJP
destination for restore of datafile 00005: C:\ORACLE\ORADATA\SLATE\EXAMPLE01.DB
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=C:\ORACLE\ORADATA\SLATE\EXAMPLE01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=349 STAMP=830699653 file name=C:\BACKUP1\STD_DATA_D-S
ATE_I-3523425568_TS-SLATE_FNO-6_77OO6RJU
destination for restore of datafile 00006: C:\ORACLE\ORADATA\SLATE\SLATE
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=C:\ORACLE\ORADATA\SLATE\SLATE RECID=0 STAMP=0
Finished restore at 05-NOV-13
RMAN> recover database;
Starting recover at 05-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\ORACLE\ORADATA\SLATE\SYSTEM01.DBF
destination for restore of datafile 00002: C:\ORACLE\ORADATA\SLATE\SYSAUX01.DBF
destination for restore of datafile 00003: C:\ORACLE\ORADATA\SLATE\UNDOTBS01.DB
destination for restore of datafile 00004: C:\ORACLE\ORADATA\SLATE\USERS01.DBF
destination for restore of datafile 00005: C:\ORACLE\ORADATA\SLATE\EXAMPLE01.DB
destination for restore of datafile 00006: C:\ORACLE\ORADATA\SLATE\SLATE
channel ORA_DISK_1: reading from backup piece C:\BACKUP\STD_7BOO6T9E_1_1
channel ORA_DISK_1: errors found reading piece handle=C:\BACKUP\STD_7BOO6T9E_1_
channel ORA_DISK_1: failover to piece handle=C:\BACKUP1\STD_7BOO6T9E_1_1 tag=IN
R_UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
unable to find archived log
archived log thread=1 sequence=168
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2013 13:55:27
RMAN-06054: media recovery requesting unknown archived log for thread 1 with se
uence 168 and starting SCN of 3806855
on sqlplus enter this command.
place the archive archive at location which is defined for standby server . then issue following command.
or catalog the archive log file then issue following command.
catalog start with 'c:/backup/';
or catalog the archive log file then issue following command.
catalog start with 'c:/backup/';
for point and time recovery do not issue following command, it is only to recover manually
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
check that
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
if successfully applied all logs, then switch over the standby to primary .....
Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE;
Subscribe to:
Posts (Atom)