Oracle 10g RMAN的備份測試
2024-08-29 13:43:50
供稿：網友
情況一，數據庫崩潰，只剩下磁帶上的通過rman 的全備份，有controlfile和spfile都在rman備份中。
通過重新安裝一個同名的SID之后再進行恢復。注重事項：
1 rman的保存格式前后要一樣.
2 archive 的格式也要和原來一樣。Oracle 10g
情況一，數據庫崩潰，只剩下磁帶上的通過rman 的全備份，有controlfile和spfile都在rman備份中。
通過重新安裝一個同名的SID之后再進行恢復。注重事項：
1 rman的保存格式前后要一樣.
2 archive 的格式也要和原來一樣。oracle 10g[oracle@standby oracle]$ rman target /
啟動歸檔
SQL>alter database archivelog;System altered.SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
SQL>重啟數據庫，歸檔就會自動啟動。啟動自動備份控制文件RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are sUCcessfully stored
RMAN> configure channel device type disk format 'F:/backup/%U';new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U';
new RMAN configuration parameters are successfully stored
SQL> select name from v$datafile;NAME
---------------------------------------------------
F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/SYSTEM01.DBF
F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/UNDOTBS01.DBF
F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/SYSAUX01.DBF
F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/USERS01.DBF
確定原控制文件的路徑
SQL> select name from v$controlfile;查看log位置SQL>select name from V$ARCHIVED_LOG；查看log的信息
SQL>select * from v$log_history;插入數據
SQL> select * from ttt;NAME EMAIL
---------- ----------
aa aa@abc.com
bb bb@abc.com
cc cc@abc.comRMAN備份，RMAN>backup database;再插入數據
SQL> select * from ttt;NAME EMAIL
---------- ----------
aa aa@abc.com
bb bb@abc.com
cc cc@abc.com
dd dd@abc.com
用DBCA刪除 數據庫和相關數據文件。
再重新建個相同實例名的SID，再開始恢復。配置RMAN
RMAN> configure channel device type disk format 'F:/backup/%U'; //注重和原來的格式一樣。new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U';
new RMAN configuration parameters are successfully stored
先需要從自動備份中恢復控制文件
RMAN> startup nomount;RMAN> restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;重建本地認證文件
C:/>orapwd file=F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA passWord=123456 entries=2;RMAN>shutdown immediateRMAN> startup mountdatabase is already started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/03/2005 20:00:30
RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416)RMAN>重新設置DBIDRMAN> set DBID=1862908416executing command: SET DBID
你可能需要修改spfile文件,假如文件位置改變。SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile;File created.SQL> startup mount;
ORACLE instance started.RMAN> restore database;
轉入SQLPLUS，sys登陸sqlplus
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 356534 generated at 08/02/2005 19:14:40 needed for thread 1
ORA-00289: suggestion :
F:/ORACLE/PRODUCT.1.0/Flash_RECOVERY_AREA/TEST/ARCHIVELOG05_08_03/O1_MF_1_
2_%U_.ARC
ORA-00280: change 356534 for thread 1 is in sequence #2
Specify log: {<RET>=suggested filename AUTO CANCEL}
cancel //因為log丟失，此步選cancel
Media recovery cancelled.
SQL> alter database open resetlogs;Database altered.SQL> select * from ttt;NAME EMAIL
---------- ----------
aa aa@abc.com
bb bb@abc.com
cc cc@abc.comSQL>數據回來了，但是由于歸檔和redo log丟了，丟失一部分數據。
情況二 數據庫崩潰，但是還剩下歸檔和redo log是好的。
啟動歸檔
SQL>alter database archivelog;System altered.SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
SQL>
重啟數據庫，歸檔就會自動啟動。啟動自動備份控制文件RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored配置RMAN的格式和存儲位置。
RMAN> configure channel device type disk format 'F:/backup/%U';new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U';
new RMAN configuration parameters are successfully stored查看數據文件的位置
SQL> select name from v$datafile;
確定原控制文件的路徑
SQL> select name from v$controlfile;查看log位置SQL>select name from V$ARCHIVED_LOG；查看log的信息
SQL>select * from v$log_history;插入數據
SQL> select * from sss;AA BB CC
---------- ---------- ---
aa bb cc
aa jj mm
vv ss tt完全備份數據庫
RMAN>backup data再插入數據SQL> select * from sss;AA BB CC
---------- ---------- ---
aa bb cc
aa jj mm
vv ss tt
mm mm mm
用DBCA刪除數據庫，刪除相關的數據文件，保留redo log和歸檔日志。再用DBCA重建一個SID一樣的數據庫。RMAN>startup配置RMAN
RMAN> configure channel device type disk format 'F:/backup/%U'; //注重和原來的格式一樣。new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:/backup/%U';
new RMAN configuration parameters are successfully stored
RMAN>startup nomount
RMAN>restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;
再copy幾份和原來一樣的路徑文件名。RMAN>shutdown immediateRMAN> startup mountconnected to target database (not started)
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/03/2005 19:55:46
ORA-01991: invalid password file 'F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA'
重建本地認證文件
C:/>orapwd file=F:/oracle/product.1.0/Db_1/DATABASE/PWDtest.ORA password=123456 entries=2;RMAN>shutdown immediateRMAN> startup mountdatabase is already started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/03/2005 20:00:30
RMAN-06189: current DBID 1863531774 does not match target mounted database (1862908416)RMAN>重新設置DBIDRMAN> set DBID=1862908416executing command: SET DBIDRMAN> startup mountdatabase is already startedRMAN> restore database;
RMAN> recover database;Starting recover at 03-8月 -05
using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 1 is already on disk as file F:/ORACLE/PRODUCT.1.0/FLASH_RECOVERY_AREA/TEST/ARCHIVELOG/
2005_08_03/O1_MF_1_1_1H2D39KL_.ARC
archive log thread 1 sequence 2 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO02.LOG
archive log thread 1 sequence 3 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO03.LOG
archive log thread 1 sequence 4 is already on disk as file F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO01.LOG
archive log filename=F:/ORACLE/PRODUCT.1.0/FLASH_RECOVERY_AREA/TEST/ARCHIVELOG05_08_03/O1_MF_1_1_1H2D39KL_.ARC thr
ead=1 sequence=1
archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO02.LOG thread=1 sequence=2
archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO03.LOG thread=1 sequence=3
archive log filename=F:/ORACLE/PRODUCT.1.0/ORADATA/TEST/REDO01.LOG thread=1 sequence=4
media recovery complete
Finished recover at 03-8月 -05RMAN>
由于控制文件是用備份還原的需要resetlogs打開。
SQL> alter database open resetlogs;
SQL> select * from sss;AA BB CC
---------- ---------- ---
aa bb cc
aa jj mm
vv ss tt
mm mm mm //此行數據回來了,說明沒有丟數據.
