Backup and recovery solution for small and medium databases RMAN catalog (III)

In the first two articles, we described the backup and recovery solutions for Small and Medium databases using RMAN catalog, all related scripts are provided to simulate Oracle Data guard to a certain extent in a car to reduce database losses on the prod Server caused by hardware faults. This article mainly describes how to restore the changes of the prod database on the Bak server.

Backup and recovery solution for small and medium databases RMAN catalog (1)
Backup and recovery solution for small and medium databases RMAN catalog (2)

 

1. Recovery prerequisites
As described in the previous two articles, we developed a daily level 0 backup and FTP the entire backup set to the Bak server. Meanwhile, regularly archive FTP prod logs to the Bak server.
Second, the database on the Bak server is restored (Restore) every day. Therefore, for Bak server to implement data recovery, you must archive the application logs (including the archive logs of timed FTP)
Refresh the database to the latest time. You can also define the backup interval, for example, every two days. The following are prerequisites for restoration. Otherwise, you must manually back up or restore the data.
RMAN backup has been completed using the RMAN backup script, and the backup is FTP to the backup server
The recovery script has been successfully restored on the backup server.

 

2. Prepare test data on prod DB

SQL> select * from V $ version where rownum <2; banner executes Oracle Database 10g Release 10.2.0.3.0-64bit productionsql> select instance_name, host_name from V $ instance; instance_name host_name ---------------- export ak3210 n10db03p -- add tablespacesql> Create tablespace tbs_tmp datafile '/u02/database/ak3210/oradata/tbs_tmp.dbf 'size 10 m autoextend on; -- add table objects based on the new tablespace SQL> Create Table XY (SEQ varchar2 (20), who varchar2 (20), DT varchar2 (20) tablespace tbs_tmp; -- insert data SQL> insert into xy select 'firstarch ', 'robinson', to_char (sysdate, 'yyyymmdd hh24: MI: ss') from dual; SQL> commit; -- Archive SQL> alter system archive log current for the current log; -- The following is the generated archive log SQL> Ho lsarch_818416637_425157.arc -- verify that the newly inserted record exists in the archive log SQL> Ho strings arch_818416637_109157.arc | grep "firstarch" firstarch --> insert into xy select 'secnodarch ', 'Jackson ', to_char (sysdate, 'yyyymmdd hh: MI: ss') from dual; SQL> commit; SQL> alter system archive log current; SQL> Ho shortarch_818416637_2017158.arcsql> Ho strings arch_818416637_2017158.arc | grep "secnodarch" secnodarch -- Author: Robinson Cheng -- Blog: Login> Ho SCP *. arc 192.168.250.101:/u02/database/ak3210/100% 34 MB 34.2 Mb/s 00: 00arch_818416637_1_158.arc 100% 12kb 12.0kb/s -- archiving of the prod database, the current log sequence is 159sql> archive log list; database Log mode archive modeautomatic archival enabledarchive destination/u02/database/ak3210/archive/oldest online log sequence 157 next log sequence to archive 159 current log sequence 159sql> Col name format a60sql> set linesize 160sql> alter session set nls_date_format = 'yyyymmdd hh24: mi: ss'; --> query archived log SQL> select name, sequence #, status, completion_time from V $ archived_log where status = 'a '; name sequence # s completion_time hour --------------------------/u02/database/ak3210/archive/lifecycle 157 A 20130731 16:34:30/u02/database/ak3210/archive/lifecycle 158 A 20130731 16: 35: 42sql> select * From XY; seq who DT -------------------- ---------------------------------- firstarch Robinson 20130731 16: 34: 15 secnodarch Jackson 20130731 16:35:35

3. Restore the database on the Bak Server

Oracle @ bkdb01p: ~> Export oracle_sid = ak3210oracle @ bkdb01p: ~> RMAN target/CATALOG rman_user/RMAN @ catadb -- connect to target dB and catalog dbrecovery manager on the backup server: Release 10.2.0.3.0-production on Wed Jul 31 16:39:45 2013 copyright (c) 1982,200 5, oracle. all rights reserved. connected to target database (not started) connected to recovery catalog databaserman> startup Mount; ---> Start the database to the Mount state RMAN> restore archivelog all; ---> restore all archived logs starting restore at 20130731 16: 41: 35 allocated channel: channels ora_disk_1: SID = 1090 devtype = diskchannel ora_disk_1: Starting archive log restore to default destinationchannel ora_disk_1: Restoring archive logarchive log thread = 1 sequence = 156 channel ora_disk_1: reading from backup piece/u02/database/ak3210/flash_recovery_area/ak3210/backupset/2013_07_31/o1_mf_annnn_archbk_8zkgnw5t _. bkpchannel ora_disk_1: restored bac Kup piece 1 piece handle =/u02/database/ak3210/flash_recovery_area/ak3210/backupset/2013_07_31/ow.mf_annnn_archbk_8zkgnw5t _. bkp tag = archbkchannel ora_disk_1: restore complete, elapsed time: 00: 00: 02 channel ora_disk_1: Starting archive log restore to default destinationchannel ora_disk_1: restoring archive logarchive log thread = 1 sequence = 155 channel ora_disk_1: reading from backup piece/u02/Database /Ak3210/flash_recovery_area/ak3210/backupset/2013_07_31/o1_mf_annnn_archbk_8zkgnw5l _. bkpchannel ora_disk_1: restored backup piece 1 piece handle =/u02/database/ak3210/flash_recovery_area/ak3210/backupset/2013_07_31/o1_mf_annnn_archbk_8zkgnw5l _. bkp tag = archbkchannel ora_disk_1: restore complete, elapsed time: 00: 00: 08 finished restore at 20130731 16: 41: 46 RMAN> List copy; ---> View the restored log file list of Arch Ived log copieskey thrd seq s low time name ------- ---- ------------------------- ---- 34428 1 155 A 20130731 01:00:50/u02/database/ak3210/archive/clusters 1 156 A 20130731 15:19:54/u02/Database /ak3210/archive/arch_818416637_2017156.arcrman> catalog archivelog '/u02/database/ak3210/archive/arch_818416637_2017157.arc '; ---> register the new archive log to catalogcataloged archive logarchive log filena Me =/u02/database/ak3210/archive/arch_818416637_2017157.arc recid = 148 stamp = 822242629 RMAN> catalog archivelog '/u02/database/ak3210/archive/arch_818416637_1_158.arc '; cataloged archive logarchive log filename =/u02/database/ak3210/archive/arch_818416637_1_158.arc recid = 149 stamp = 822242639 RMAN> List copy; ---> when you view the file again, all archived logs are already in the archive directory list of archived log copieskey thrd seq s low time name ---------------- -- ----------------- ---- 34428 1 155 A 20130731 01:00:50/u02/database/ak3210/archive/logs 1 156 A 20130731 15:19:54/u02/database/ak3210/archive/arch_818416637_1_156.arc34495 1 157 A 20130731 15:19:55 /u02/database/ak3210/archive/restore 1 158 A 20130731 16:34:30/u02/database/ak3210/archive/arch_818416637_2017158.arcrman> run {---> restore the database using, below Error Message 2> set until sequence 159; 3> recover database;} Executing command: set until clausestarting recover at 20130731 16: 45: 47 using channel ora_disk_1starting media recoveryarchive log thread 1 sequence 155 is already on disk as file/u02/database/ak3210/archive/initlog thread 1 sequence 156 is already on disk as file/u02 /database/ak3210/archive/arch_818416637_2017156.arc Archive log thread 1 sequence 157 is already on disk as file/u02/database/ak3210/archive/arch_818416637_2017157.arcarchive log thread 1 sequence 158 is already on disk as file/u02/database/ak3210/ archive/arch_818416637_1_158.arcRMAN-00571: ========================================================== ============================== RMAN-00569: ================ error message stack follows ============================ RMAN-00571: ============================ ========================================================== = RMAN-03002: failure of recover command at 07/31/2013 16: 45: 51rman-06053: unable to perform media recovery because of missing logRMAN-06025: No backup of log thread 1 seq 94 lowscn 2457942 found to restorerman> exitrecovery manager complete. oracle @ bkdb01p: ~> Export oracle_sid = ak3210oracle @ bkdb01p: ~> Sqlplus/As sysdba ---> recover SQL> recover database using backup controlfile; ---> Restore database ORA-00279 with backup-based control files: change 2654259 generated at 07/31/2013 15:19:26 needed for thread 1ora-00289: Suggestion:/u02/database/ak3210/archive/arch_818416637_1_155.arcORA-00280: Change 2654259 for thread 1 is in sequence #155 specify log: {<RET> = suggested | filename | auto | cancel} auto ---> enter auto and apply automatically Log File ORA-00279: Change 2654361 generated at 07/31/2013 15:19:54 needed for thread 1ora-00289: Suggestion:/u02/database/ak3210/archive/arch_818416637_1_156.arcORA-00280: Change 2654361 for thread 1 is in sequence #156ora-00278: log File '/u02/database/ak3210/archive/arch_818416637_415155.arc' nolonger needed for this recoveryORA-00279: Change 2654372 generated at 07/31/2013 15:19:55 needed for thre Ad 1ora-00289: Suggestion:/u02/database/ak3210/archive/arch_818416637_1_157.arc ORA-00280: Change 2654372 for thread 1 is in sequence #157 ---> log apply to 157ora-00278: log File '/u02/database/ak3210/archive/arch_818416637_415156.arc' nolonger needed for this recoveryORA-00283: recovery session canceled due to errors ---> An unknown data file is prompted to be added to the control file ORA-01244: Unnamed datafile (s) added to control file Media recoveryORA-01110: Data File 26: '/u02/database/ak3210/oradata/tbs_tmp.dbf' ora-01112: Media recovery not started ---> error message, the following message is displayed, indicating that SQL> Ho ls/u02/database/ak3210/oradata/tbs_tmp.dbf ---> is not started for media restoration. Check the corresponding data file because the file does not exist on the backup server. ls: /u02/database/ak3210/oradata/tbs_tmp.dbf: no such file or directory ---> use the following command to recreate the data file. Why can this operation be performed? The archive log records the data file SQL> alter database create datafile 26 as '/u02/database/ak3210/oradata/tbs_tmp.dbf'; database altered. SQL> Ho ls/u02/database/ak3210/oradata/tbs_tmp.dbf ---> check that the data file already exists/u02/database/ak3210/oradata/tbs_tmp.dbfsql> recover database using backup controlfile; ---> recover database again ORA-00279: Change 2656873 generated at 07/31/2013 16:33:06 needed for thread 1ora-00289: Suggestion:/u02/Data Base/ak3210/archive/arch_818416637_1_157.arcORA-00280: Change 2656873 for thread 1 is in sequence #157 specify log: {<RET> = suggested | filename | auto | cancel} auto ---> input autoORA-00279: change 2656938 generated at 07/31/2013 16:34:30 needed for thread 1ora-00289: Suggestion:/u02/database/ak3210/archive/arch_818416637_1_158.arcORA-00280: Change 2656938 for thread 1 is in sequence #158ora-00278: Lo G File '/u02/database/ak3210/archive/arch_818416637_415157.arc' nolonger needed for this recoveryORA-00279: Change 2656966 generated at 07/31/2013 16:35:42 needed for thread 1ora-00289: Suggestion: /u02/database/ak3210/archive/arch_818416637_1_159.arcORA-00280: Change 2656966 for thread 1 is in sequence #159ora-00278: Log File '/u02/database/ak3210/archive/arch_818416637_1_158.arc' nolonger needed For this recoveryORA-00308: cannot open archived log '/u02/database/ak3210/archive/arch_818416637_1_159.arc' ---> Find sequence as 159, in fact it does not exist, so the ORA-27037 cannot be found: unable to obtain file statusLinux-x86_64 error: 2: no such file or directoryadditional information: 3sql> recover database using backup controlfile until cancel; ---> recover database ORA-00279 again: change 2656966 generated at 07/31/2013 16:35:42 needed for th Read 1ora-00289: Suggestion:/u02/database/ak3210/archive/arch_818416637_1_159.arcORA-00280: Change 2656966 for thread 1 is in sequence #159 specify log: {<RET> = suggested | filename | auto | cancel} cancel ---> enter cancelmedia recovery canceled. SQL> alter database open resetlogs; ---> open database altered in resetlogs mode. SQL> select * From XY; ---> Verification Result: the database is successfully restored. seq who DT -------------------------------- -------- -------------------- Firstarch Robinson 20130731 16: 34: 15 secnodarch Jackson 20130731 16: 35: 35sql> shutdown immediate; ---> close the database Oracle @ bkdb01p: ~> Export oracle_sid = ak3210oracle @ bkdb01p: ~> RMAN target/CATALOG rman_user/RMAN @ catadb ---> connect again to catalogrman> startup Mount; ---> Start Oracle instance starteddatabase mountednew incarnation of database registered in recovery catalog ---> you can see that the new incarnation is registered with catalog starting full Resync of recovery catalogfull Resync completerman> List incarnation; ---> list the incarnationlist of database incarnationsdb key Inc key dB name dB ID status R of the current database Eset scn reset time ------- -------- ---------------- --- ---------- 357 358 ak3210 1008246269 parent 1 20130618 09: 57: 17357 34690 ak3210 1008246269 current 2656967 20130731 16: 54: 39 RMAN> Reset database to incarnation 358; ---> Reset the incarnationdatabase reset to incarnation 358 RMAN> Resync Catalog; ---> synchronized catalogrman> shutdown abort; the process of deleting tablespaces and data files in the prod segment is simpler than that of adding data files. You can directly execute restore and recover. However, the corresponding physical data file still exists on the OS system and can be deleted manually.

 

References
Backup and recovery solution for small and medium databases RMAN catalog (1)

Backup and recovery solution for small and medium databases RMAN catalog (2)

Method for converting the cloned file location in the RMAN Database

RMAN duplicate)

RMAN-based database cloning on the same machine

User-managed database clone

Cloning from an active database based on RMAN (RMAN duplicate from active dB)

RMAN duplicate from active encountered ORA-17627 ORA-12154

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion

Customize the date and time format displayed by RMAN

Backup and recovery of read-only tablespace

Incomplete recovery of Oracle based on user management

Understanding using backup controlfile

Use RMAN for recovery from different machine backups (WIN platform)

Use RMAN to migrate a file system database to ASM

Oracle backup policy (RMAN) in Linux)

Linux RMAN backup shell script

Use RMAN to migrate the database to a different machine

Run the SQL statement at the RMAN prompt.

Oracle RMAN-based Incomplete recovery (Incomplete recovery by RMAN)

RMAN restores archivelog)