【備份恢復】從備份恢復資料庫
準備工作
[root@edbjr2p1 ~]# su - oracle
[oracle@edbjr2p1 ~]$ export ORACLE_SID=PROD
[oracle@edbjr2p1 ~]$ cd /u01/arch
備份資料庫，備份指令碼如下
backup.cmd
run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
crosscheck backup;
crosscheck archivelog all;
delete force noprompt expired backup;
delete force noprompt expired archivelog all;
allocate channel c1 device type disk connect sys/oracle@PROD;
sql 'alter system archive log current';
backup as compressed backupset database format '/u01/arch/full_%d_%s_%p_%T' plus archivelog delete all input format '/u01/arch/arch_%d_%s_%p_%T';
backup current controlfile format '/u01/arch/ctl_%d_%s_%p_%T';
backup spfile format ‘/u01/arch/spfile_%d_%T.bak’;
release channel c1;
}
backup.sh
rman target / nocatalog cmdfile=/u01/backup.cmd log=/u01/backup-`date +%Y%m%d`.log
一、檢視備份
[oracle@edbjr2p1 arch]$ ls -lrt
total 69508
-rw-r----- 1 oracle oinstall 13312 Jan 9 11:19 arch_PROD_39_1_20150109
-rw-r----- 1 oracle oinstall 62971904 Jan 9 11:20 full_PROD_40_1_20150109
-rw-r----- 1 oracle oinstall 1114112 Jan 9 11:20 full_PROD_41_1_20150109
-rw-r----- 1 oracle oinstall 3072 Jan 9 11:20 arch_PROD_42_1_20150109
-rw-r----- 1 oracle oinstall 6881280 Jan 9 11:20 ctl_PROD_43_1_20150109
-rw-r----- 1 oracle oinstall 98304 Jan 9 11:20 spfile_PROD_44_1_20150109
[oracle@edbjr2p1 arch]$
[oracle@edbjr2p1 arch]$
[oracle@edbjr2p1 arch]$
[oracle@edbjr2p1 arch]$ rm -rf $ORACLE_HOME/dbs/*PROD.ora
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:21:02 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
二、刪除資料庫，嘗試啟動。
[oracle@edbjr2p1 arch]$ rm -rf /u01/app/oracle/oradata/PROD/disk*/*
[oracle@edbjr2p1 arch]$ set -o vi
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:22:23 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@PROD>startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD.ora'
SYS@PROD>exit
Disconnected
三、還原引數檔案
[oracle@edbjr2p1 arch]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 9 11:22:31 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> restore spfile from '/u01/arch/spfile_PROD_44_1_20150109';
Starting restore at 09-JAN-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/09/2015 11:22:46
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
RMAN> restore spfile from '/u01/arch/spfile_PROD_44_1_20150109';
Starting restore at 09-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/arch/spfile_PROD_44_1_20150109
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-JAN-15
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 155189472 bytes
Database Buffers 364904448 bytes
Redo Buffers 2973696 bytes
RMAN> exit
Recovery Manager complete.
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:23:25 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SYS@PROD>show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string
compatible string 10.2.0
max_commit_propagation_delay integer 0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
SYS@PROD>exit
Disconnected from Oracle Database 10g Enterprise Edition Data Mining Scoring Engine options
[oracle@edbjr2p1 arch]$ ls -lrt
total 69508
-rw-r----- 1 oracle oinstall 13312 Jan 9 11:19 arch_PROD_39_1_20150109
-rw-r----- 1 oracle oinstall 62971904 Jan 9 11:20 full_PROD_40_1_20150109
-rw-r----- 1 oracle oinstall 1114112 Jan 9 11:20 full_PROD_41_1_20150109
-rw-r----- 1 oracle oinstall 3072 Jan 9 11:20 arch_PROD_42_1_20150109
-rw-r----- 1 oracle oinstall 6881280 Jan 9 11:20 ctl_PROD_43_1_20150109
-rw-r----- 1 oracle oinstall 98304 Jan 9 11:20 spfile_PROD_44_1_20150109
四、恢復控制檔案
[oracle@edbjr2p1 arch]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 9 11:23:41 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (not mounted)
RMAN> restore controlfile from '/u01/arch/ctl_PROD_43_1_20150109';
Starting restore at 09-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/PROD/disk1/control01.ctl
output filename=/u01/app/oracle/oradata/PROD/disk2/control02.ctl
output filename=/u01/app/oracle/oradata/PROD/disk3/control03.ctl
Finished restore at 09-JAN-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
五、恢復資料庫
RMAN> restore database;
Starting restore at 09-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=320 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/PROD/disk1/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk1/USERS.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk1/DSS.dbf
channel ORA_DISK_1: reading from backup piece /u01/arch/full_PROD_40_1_20150109
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/arch/full_PROD_40_1_20150109 tag=TAG20150109T112000
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09-JAN-15
RMAN> recover database;
Starting recover at 09-JAN-15
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=36
channel ORA_DISK_1: reading from backup piece /u01/arch/arch_PROD_42_1_20150109
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/arch/arch_PROD_42_1_20150109 tag=TAG20150109T112017
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/arch/1_36_865882219.dbf thread=1 sequence=36
unable to find archive log
archive log thread=1 sequence=37
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2015 11:25:24
RMAN-06054: media recovery requesting unknown log: thread 1 seq 37 lowscn 178388
RMAN> alter databaes open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databaes
RMAN-01007: at line 1 column 7 file: standard input
RMAN> alter database open resetlogs;
database opened
RMAN>
Recovery Manager complete.
至此資料庫恢復完成
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:27:01 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SYS@PROD>show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string
compatible string 10.2.0
max_commit_propagation_delay integer 0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 155189472 bytes
Database Buffers 364904448 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string
compatible string 10.2.0
max_commit_propagation_delay integer 0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
SYS@PROD>alter system set compatible='10.2.0.1.0' scope=spfile;
System altered.
SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 155189472 bytes
Database Buffers 364904448 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength integer 1
commit_write string
compatible string 10.2.0.1.0
max_commit_propagation_delay integer 0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
SYS@PROD>
簡單做了個備份恢復的實驗，記錄一下。
2015.1.9
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29319205/viewspace-1407979/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 備份與恢復--從備份的歸檔日誌中恢復資料
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- 【備份恢復】資料恢復指導資料恢復
- 【管理篇備份恢復】備份恢復基礎
- 達夢資料庫備份恢復資料庫
- postgresql備份與恢復資料庫SQL資料庫
- mongo資料庫備份與恢復Go資料庫
- 資料庫的備份與恢復資料庫
- Informix資料庫備份與恢復ORM資料庫
- 備份和恢復postgreSQL資料庫SQL資料庫
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Oracle資料庫備份與恢復之三：OS備份/使用者管理的備份與恢復Oracle資料庫
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- 【備份恢復】RMAN catalog 恢復目錄資料庫資料庫
- 資料庫備份與異機恢復——熱備份方式資料庫
- ORACLE DG從庫 Rman備份恢復Oracle
- 第5章：從開啟的資料庫備份與恢復之從開啟的資料庫備份中完全恢復資料庫
- 【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）Hive模式資料庫
- 資料庫資料的恢復和備份資料庫
- PostgreSql資料庫的備份和恢復SQL資料庫
- Mongo 資料庫備份和恢復命令Go資料庫
- 直接透過備份恢復資料庫資料庫
- mysql的資料庫備份與恢復MySql資料庫
- oracle資料庫的備份與恢復Oracle資料庫
- 備份和恢復SQL Server資料庫SQLServer資料庫
- 資料庫備份與恢復技術資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 備份與恢復--利用備份的控制檔案恢復
- Mysql資料備份與恢復MySql
- rman資料庫全庫備份與恢復資料庫
- RMAN備份恢復——RAC環境資料庫的備份(zt)資料庫
- RMAN備份恢復--RAC環境資料庫的備份（十）資料庫
- RMAN備份恢復——RAC環境資料庫的備份（一）資料庫
- Mysql備份恢復MySql
- Postgresql 備份恢復SQL
- redis備份恢復Redis