Oracle 11g 异机恢复参考文档

	原库	目标库
操作系统	CentOS 6.4	CentOS 6.4
主机名	sht-sgmhadoopnn-01	sht-sgmhadoopnn-02
IP	172.16.101.55	172.16.101.56
数据库版本	11.2.0.4.0	11.2.0.4.0
$ORACLE_BASE	/u01/app/oracle	/u01/app/oracle
$ORACLE_HOME	/u01/app/oracle/product/11.2.0/db_1	/u01/app/oracle/product/11.2.0/db_1
数据库名	userdata	userdata

本次以原库全新安装后为例说明利用RMAN做异机恢复,原库和目标库路径一致,目标库仅需安装数据库软件即可,安装过程详见我的另一篇博客http://www.cnblogs.com/ilifeilong/p/7041676.html

 

1. 原库开启归档模式

SYS@userdata>archive log list;
Database log mode           　　No Archive Mode
Automatic archival           　Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3

SYS@userdata>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@userdata>startup mount;
ORACLE instance started.

Total System Global Area 1837244416 bytes
Fixed Size            2254224 bytes
Variable Size          637536880 bytes
Database Buffers     1191182336 bytes
Redo Buffers            6270976 bytes
Database mounted.

SYS@userdata>alter database archivelog;

Database altered.

SYS@userdata>alter database open;

Database altered.

SYS@userdata>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

2. 用RMAN对原库进行备份

$ mkdir /home/oracle/backupset
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 10 20:00:02 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: USERDATA (DBID=3894924926)

RMAN> run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
sql 'alter system archive log current';
backup database format '/home/oracle/backupset/data_%U.bak';
backup archivelog all format '/home/oracle/backupset/arch_%U.bak';
backup current controlfile format '/home/oracle/backupset/ctl_%U.bak';
release channel ch1;
release channel ch2;
}

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=479 device type=DISK

allocated channel: ch2
channel ch2: SID=20 device type=DISK

sql statement: alter system archive log current

Starting backup at 2017-08-10 20:05:18
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/userdata/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/userdata/users01.dbf
channel ch1: starting piece 1 at 2017-08-10 20:05:18
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/userdata/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/userdata/undotbs01.dbf
channel ch2: starting piece 1 at 2017-08-10 20:05:18
channel ch2: finished piece 1 at 2017-08-10 20:05:33
piece handle=/home/oracle/backupset/data_02sbj0ru_1_1.bak tag=TAG20170810T200518 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
including current control file in backup set
channel ch2: starting piece 1 at 2017-08-10 20:05:35
channel ch2: finished piece 1 at 2017-08-10 20:05:36
piece handle=/home/oracle/backupset/data_03sbj0se_1_1.bak tag=TAG20170810T200518 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch2: starting piece 1 at 2017-08-10 20:05:36
channel ch1: finished piece 1 at 2017-08-10 20:05:37
piece handle=/home/oracle/backupset/data_01sbj0ru_1_1.bak tag=TAG20170810T200518 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:19
channel ch2: finished piece 1 at 2017-08-10 20:05:37
piece handle=/home/oracle/backupset/data_04sbj0sg_1_1.bak tag=TAG20170810T200518 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-08-10 20:05:37

Starting backup at 2017-08-10 20:05:38
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=951681918
channel ch1: starting piece 1 at 2017-08-10 20:05:39
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=951681939
channel ch2: starting piece 1 at 2017-08-10 20:05:39
channel ch1: finished piece 1 at 2017-08-10 20:05:40
piece handle=/home/oracle/backupset/arch_05sbj0sj_1_1.bak tag=TAG20170810T200539 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 2017-08-10 20:05:40
piece handle=/home/oracle/backupset/arch_06sbj0sj_1_1.bak tag=TAG20170810T200539 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-08-10 20:05:40

Starting backup at 2017-08-10 20:05:41
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 2017-08-10 20:05:42
channel ch1: finished piece 1 at 2017-08-10 20:05:43
piece handle=/home/oracle/backupset/ctl_07sbj0sl_1_1.bak tag=TAG20170810T200541 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-08-10 20:05:43

released channel: ch1

released channel: ch2


SYS@userdata>create pfile='/home/oracle/backupset/pfile.ora' from spfile;

3. 将备份目录拷贝到目标节点相同目录

$ scp -r /home/oracle/backupset oracle@172.16.101.56:/home/oracle/
pfile.ora                                                                                          100% 1018     1.0KB/s   00:00    
arch_05sbj0sj_1_1.bak                                                                              100%   23MB  22.5MB/s   00:00    
data_02sbj0ru_1_1.bak                                                                              100%  379MB  37.9MB/s   00:10    
data_01sbj0ru_1_1.bak                                                                              100%  635MB  48.9MB/s   00:13    
data_04sbj0sg_1_1.bak                                                                              100%   96KB  96.0KB/s   00:00    
arch_06sbj0sj_1_1.bak                                                                              100%   14KB  13.5KB/s   00:00    
ctl_07sbj0sl_1_1.bak                                                                               100%   11MB  10.7MB/s   00:01    
data_03sbj0se_1_1.bak                                                                              100%   11MB  10.7MB/s   00:00 

3. 目标库安装数据库软件

略过,参考 http://www.cnblogs.com/ilifeilong/p/7041676.html

4. 创建密码文件

$ scp $ORACLE_HOME/dbs/orapwuserdata oracle@sht-sgmhadoopnn-02:$ORACLE_HOME/dbs/
orapwuserdata                                                                                      100% 1536     1.5KB/s   00:00  

5. 还原参数文件,启动到nomount状态

$ cat /home/oracle/backupset/pfile.ora 
userdata.__db_cache_size=1409286144
userdata.__java_pool_size=16777216
userdata.__large_pool_size=33554432
userdata.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
userdata.__pga_aggregate_target=620756992
userdata.__sga_target=1845493760
userdata.__shared_io_pool_size=0
userdata.__shared_pool_size=369098752
userdata.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/userdata/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/userdata/control01.ctl','/u01/app/oracle/fast_recovery_area/userdata/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='userdata'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8589934592
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=userdataXDB)'
*.java_jit_enabled=FALSE
*.open_cursors=600
*.pga_aggregate_target=613416960
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1840250880
*.undo_tablespace='UNDOTBS1'

$ mkdir -p /u01/app/oracle/admin/userdata/adump
$ mkdir -p /u01/app/oracle/oradata/userdata
$ mkdir -p /u01/app/oracle/fast_recovery_area/userdata

SYS@userdata>startup nomount pfile='/home/oracle/backupset/pfile.ora';
ORACLE instance started.

Total System Global Area 1837244416 bytes
Fixed Size            2254224 bytes
Variable Size          637536880 bytes
Database Buffers     1191182336 bytes
Redo Buffers            6270976 bytes

SYS@userdata>create spfile from pfile='/home/oracle/backupset/pfile.ora';

File created.

SYS@userdata>shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SYS@userdata>startup nomount;
ORACLE instance started.

Total System Global Area 1837244416 bytes
Fixed Size            2254224 bytes
Variable Size          637536880 bytes
Database Buffers     1191182336 bytes
Redo Buffers            6270976 bytes

6. RMAN还原控制文件,并启动到mount状态

RMAN> restore controlfile from '/home/oracle/backupset/ctl_07sbj0sl_1_1.bak';

Starting restore at 2017-08-11 00:55:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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/userdata/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/userdata/control02.ctl
Finished restore at 2017-08-11 00:55:02

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

7. RMAN还原与恢复数据库

RMAN> recover database;

Starting recover at 2017-08-11 00:58:34
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /home/oracle/backupset/arch_06sbj0sj_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backupset/arch_06sbj0sj_1_1.bak tag=TAG20170810T200539
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/USERDATA/archivelog/2017_08_11/o1_mf_1_4_drs45wx1_.arc thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/USERDATA/archivelog/2017_08_11/o1_mf_1_4_drs45wx1_.arc RECID=3 STAMP=951699516
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/11/2017 00:58:38
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 969878

RMAN> recover database until scn 969878;

Starting recover at 2017-08-11 00:59:49
using channel ORA_DISK_1

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

Finished recover at 2017-08-11 00:59:51

8. 以resetlogs方式打开数据库

SYS@userdata>alter database open resetlogs;

Database altered.

9. 验证数据库数据

SYS@userdata>select instance_name, status from v$instance;

INSTANCE_NAME                     STATUS
------------------------------------------------ ------------------------------------
userdata                     OPEN

SYS@userdata>select dbid, open_mode from v$database;

      DBID OPEN_MODE
---------- ------------------------------------------------------------
3894924926 READ WRITE

SYS@userdata>select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/u01/app/oracle/oradata/userdata/users01.dbf
/u01/app/oracle/oradata/userdata/undotbs01.dbf
/u01/app/oracle/oradata/userdata/sysaux01.dbf
/u01/app/oracle/oradata/userdata/system01.dbf

SYS@userdata>select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/userdata/redo03.log
/u01/app/oracle/oradata/userdata/redo02.log
/u01/app/oracle/oradata/userdata/redo01.log

SYS@userdata>SELECT THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$LOG;

   THREAD#  SEQUENCE# ARCHIVED    STATUS
---------- ---------- --------- ------------------------------------------------
     1        1 NO    CURRENT
     1        0 YES    UNUSED
     1        0 YES    UNUSED

10. 创建监听

$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = sht-sgmhadoopnn-02)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-AUG-2017 01:07:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/sht-sgmhadoopnn-02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sht-sgmhadoopnn-02)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-AUG-2017 01:07:35
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/sht-sgmhadoopnn-02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sht-sgmhadoopnn-02)(PORT=1521)))
The listener supports no services
The command completed successfully

$ sqlplus system/888888

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 11 01:08:28 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM@userdata>