在 ARCHIVELOG 模式下執行資料庫還原和恢復操作

本實驗是在一個庫中操作備份，然後刪除三大檔案，最後利用rman備份進行恢復資料庫操作

1.檢視歸檔與否：

SYS@ORA11GR2>archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 14

Current log sequence 16

SYS@ORA11GR2>show parameter recover

NAME TYPE VALUE

------------------------------------ -----------

db_recovery_file_dest string

db_recovery_file_dest_size big integer 0

db_unrecoverable_scn_tracking boolean TRUE

recovery_parallelism integer 0

SYS@ORA11GR2>

SYS@ORA11GR2>alter system set db_recovery_file_dest_size=3g;

System altered.

SYS@ORA11GR2>!mkdir -p /u01/app/FRA

SYS@ORA11GR2>alter system set db_recovery_file_dest='/u01/app/FRA';

System altered.

——設定歸檔為enable：

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database archivelog;

Database altered.

SYS@ORA11GR2>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 14

Next log sequence to archive 16

Current log sequence 16

SYS@ORA11GR2>alter database open;

Database altered.

2.將RMAN的自動備份控制檔案功能開啟：

（建議備份時也備份歸檔日誌，以實現完全恢復，否則則需進行不完全恢復）

[oracle@wang ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:44:58 2016

connected to target database: ORA11GR2 (DBID=237843809)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORA11GR2 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORA11GR2.f'; # default

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>

2.備份資料庫：

RMAN> backup database;

Starting backup at 08-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=49 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=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: starting piece 1 at 08-OCT-16

channel ORA_DISK_1: finished piece 1 at 08-OCT-16

piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56

Finished backup at 08-OCT-16

Starting Control File and SPFILE Autobackup at 08-OCT-16

piece handle=/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 08-OCT-16

3.刪除所有控制檔案、資料檔案、日誌檔案，以及引數檔案：

[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls

control01.ctl redo01.log sysaux01.dbf undotbs01.dbf

control02.ctl redo02.log system01.dbf users01.dbf

example01.dbf redo03.log temp01.dbf

[oracle@wang ORA11GR2]$ rm *

[oracle@wang ORA11GR2]$ ls

[oracle@wang ORA11GR2]$ cd $ORACLE_HOME/dbs

[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora

initORA11GR2.ora spfileORA11GR2.ora

[oracle@wang dbs]$ rm spfileORA11GR2.ora initORA11GR2.ora

[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora

ls: spfileORA11GR2.ora: No such file or directory

ls: initORA11GR2.ora: No such file or directory

[oracle@wang dbs]$

4.執行恢復：

1）修改環境變數$ORACLE_SID

[oracle@wang ~]$ echo $ORACLE_SID

ORA11GR2

[oracle@wang ~]$ export ORACLE_SID=SF

[oracle@wang ~]$ echo $ORACLE_SID

SF

2）登入RMAN(可以在沒有引數檔案的情況下登入例項)

[oracle@wang ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:56:11 2016

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSF.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 281019272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5517312 bytes

3）恢復引數檔案：

RMAN> restore spfile to '$ORACLE_HOME/dbs/spfileORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';

Starting restore at 08-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 08-OCT-16

——驗證：

[oracle@wang dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@wang dbs]$ ls spfileORA11GR2.ora

spfileORA11GR2.ora

4）關閉例項及重新以恢復的引數檔案（即ORA11GR2）登入到例項：

[oracle@wang ~]$ echo $ORACLE_SID

SF

[oracle@wang ~]$ export ORACLE_SID=ORA11GR2

[oracle@wang ~]$ echo $ORACLE_SID

ORA11GR2

[oracle@wang ~]$

——驗證可以登入到例項（以ORA11GR2）

SYS@ORA11GR2>startup nomount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter spfile

NAME TYPE VALUE

------------------------------------ -----------

spfile string u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfileORA11GR2.ora

5)繼續以RMAN恢復控制檔案：（重儲）

RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';

Starting restore at 08-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORA11GR2/control01.ctl

output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl

Finished restore at 08-OCT-16

——啟動資料庫到mount下：

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

6）恢復資料庫：（重儲資料庫）

RMAN> restore database;

Starting restore at 08-OCT-16

Starting implicit crosscheck backup at 08-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 08-OCT-16

Starting implicit crosscheck copy at 08-OCT-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-OCT-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA11GR2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp

channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 08-OCT-16

7）恢復資料庫（recover，因為是熱備資料庫且處於歸檔模式）

RMAN> recover database;

Starting recover at 08-OCT-16

using channel ORA_DISK_1

starting media recovery

unable to find archived log

archived log thread=1 sequence=14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/08/2016 15:26:39

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and starting SCN of 1201609

此處我們會發現，在recover的時候，由於備份歸檔日誌，所以，提示只能恢復到SCN 1201609

因為備份整庫時只是備份到當時的點，而且用的是舊版的控制檔案恢復資料庫，所以只能恢復到舊版控制為檔案記錄的最新scn

——解決：

RMAN> run{

2> SET UNTIL SCN 1201609;

3> RESTORE DATABASE;

4> SWITCH DATAFILE ALL;

5> RECOVER DATABASE;

6> }

executing command: SET until clause

Starting restore at 08-OCT-16

using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/oradata/ORA11GR2/system01.dbf

skipping datafile 2; already restored to file /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

skipping datafile 3; already restored to file /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ORA11GR2/users01.dbf

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA11GR2/example01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 08-OCT-16

Starting recover at 08-OCT-16

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 08-OCT-16

——開啟資料庫：

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 10/08/2016 15:32:17

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;（屬於不完全恢復）

database opened

8）驗證資料庫恢復回來了：

SYS@ORA11GR2>select status from v$instance;

STATUS

------------

OPEN

SYS@ORA11GR2>show parameter name

NAME TYPE VALUE

------------------------------------ -----------

cell_offloadgroup_name string

db_file_name_convert string

db_name string ORA11GR2

db_unique_name string ORA11GR2

global_names boolean FALSE

instance_name string ORA11GR2

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string ORA11GR2

SYS@ORA11GR2>

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/

control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

源庫執行資料庫還原和恢復操作完成！！！！！！！！！！！！

【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）

在 ARCHIVELOG 模式下執行資料庫還原和恢復操作

相關文章