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.

Small and Medium databases rman catalog Backup Recovery Solution Series:

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

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

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
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production

SQL> select instance_name, host_name from v $ instance;

INSTANCE_NAME HOST_NAME
-------------------------------------------------------------
Ak3210 N10db03p

-- Add tablespace for prod
SQL> 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 the current log
SQL> alter system archive log current;

-- The archive logs generated are as follows:
SQL> ho ls
Arch_818416637_2017157.arc

-- Verify that the inserted record exists in the archived log
SQL> ho strings arch_818416637_2017157.arc | grep "FirstArch"
FirstArch

-- Insert new data again
SQL> 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 ls
Arch_818416637_2017157.arc arch_818416637_2017158.arc

SQL> ho strings arch_818416637_2017158.arc | grep "SecnodArch"
SecnodArch

-- Author: Robinson Cheng
-- Blog: http://www.bkjia.com

-- Copy the archived log file to the backup server
SQL> ho scp *. arc 192.168.250.101:/u02/database/Ak3210/archive
Arch_818416637_2017157.arc 100% 34 MB 34.2 MB/s
Arch_818416637_2017158.arc 100% 12KB 12.0KB/s

-- Archive the Prod database. The current Log sequence is 159
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination/u02/database/Ak3210/archive/
Oldest online log sequence 157
Next log sequence to archive 159
Current log sequence 159
SQL> col name format a60
SQL> set linesize 160
SQL> alter session set nls_date_format = 'yyyymmdd hh24: mi: ss'; --> query archived logs
SQL> select name, sequence #, status, COMPLETION_TIME from v $ archived_log where status = 'a ';

Name sequence # S COMPLETION_TIME
----------------------------------------------------------------------------------------
/U02/database/Ak3210/archive/arch_818416637_2017157.arc 157 A 20130731 16:34:30
/U02/database/Ak3210/archive/arch_818416637_2017158.arc 158 A 20130731 16:35:42

SQL> select * from xy;

SEQ WHO DT
------------------------------------------------------------
FirstArch Robinson 20130731 16:34:15
SecnodArch Jackson 20130731 16:35:35

1 2 Next Page