一点一滴努力,满仓满谷收成

1 环境
源库：11.2.0.4 rac,rhel6.7
异机恢复服务器:11.2.0.4 单实例,rhel 6.4
如下操作都是异机进行的。

2 环境变量

$cat .bash_profile
export PS1=”[\u@\h \W]\\$ ”

umask 022
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export ORACLE_SID=aux1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT=YYYYMMDDHH24MISS
alias cdoh=”cd $ORACLE_HOME”

ORACLE_SID设置成aux1,这可以是任何名字

3 pfile

$cat initaux1.ora
gxd.__oracle_base=’/oracle/app/oracle’#ORACLE_BASE set from environment
*.audit_file_dest=’/oracle/app/oracle/admin/gxd/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_files=’/yang/gxd/ora_ctrlfile01.dbf’,’/yang/gxd/ora_ctrlfile02.dbf’,’/yang/gxd/ora_ctrlfile03.dbf’
*.db_block_size=8192
*.db_cache_size=2147483648
*.db_domain=”
*.db_name=’gxd’
*.db_unique_name=’aux1′
*.diagnostic_dest=’/oracle/app/oracle’
*.java_pool_size=209715200
*.large_pool_size=209715200
*.log_archive_dest_1=’LOCATION=/arch1′
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=1000
*.remote_login_passwordfile=’EXCLUSIVE’
*.shared_pool_size=912680550
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.resource_manager_plan=”
*.db_files=1000

把实例启动到nomount状态。

4 restore controlfile

$rman target / catalog rman/***@rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Nov 30 12:19:17 2016

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

connected to target database: GXD (not mounted)
connected to recovery catalog database

RMAN> run
2> {
3> allocate channel c1 type disk;
4>
5> set dbid=3096283576;
6> set until time “TO_DATE(‘20161128 20:00:00′,’YYYYMMDD HH24:MI:SS’)”;
7>
8> restore controlfile;
9> sql ‘alter database mount clone database’;
10>
11> release channel c1;
12> }

allocated channel: c1
channel c1: SID=1015 device type=DISK

executing command: SET DBID
database name is “GXD” and DBID is 3096283576

executing command: SET until clause

Starting restore at 20161130121923

channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /backup/backup_rman/controlfile_c-3096283576-20161128-13
channel c1: piece handle=/backup/backup_rman/controlfile_c-3096283576-20161128-13 tag=TAG20161128T190549
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/yang/gxd/ora_ctrlfile01.dbf
output file name=/yang/gxd/ora_ctrlfile02.dbf
output file name=/yang/gxd/ora_ctrlfile03.dbf
Finished restore at 20161130121924

sql statement: alter database mount clone database

released channel: c1

clone选项的作用是把所有的数据文件都置成offline状态

5 restore datafiles
恢复相应数据文件，并置成online状态

$rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Nov 30 15:49:57 2016

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

connected to target database: GXD (DBID=3096283576, not open)

RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> allocate channel c4 type disk;
7>
8> set until time “TO_DATE(‘20161128 20:00:00′,’YYYYMMDD HH24:MI:SS’)”;
9>
10> set newname for datafile 1 to ‘/yang/gxd/system.260.923589565’;
… …
120> set newname for datafile 211 to ‘/yang/gxd/undotbs2.368.928391331’;
121>
122> restore tablespace system,sysaux,undotbs1,undotbs2,TBS_DATACENTER;
123> switch datafile all;
124>
125> sql “alter database datafile 1 online”;
… …
235> sql “alter database datafile 211 online”;
236>
237> release channel c1;
238> release channel c2;
239> release channel c3;
240> release channel c4;
241> }

输出略。。

6 recover
跳过不必要的tablespaces

[oracle@oradb2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Nov 30 15:49:57 2016

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

connected to target database: GXD (DBID=3096283576, not open)

RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> allocate channel c4 type disk;
7>
8> set until time “TO_DATE(‘20161128 20:00:00′,’YYYYMMDD HH24:MI:SS’)”;
9>
10> recover database skip forever tablespace TEMP,USERS,TBS_SDE,TBS_MODELING;
11>
12> release channel c1;
13> release channel c2;
14> release channel c3;
15> release channel c4;
16> }

输出：

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1015 device type=DISK
… …
channel c3: restore complete, elapsed time: 00:02:25
archived log file name=/arch1/arch1/1_10365_923589560.dbf thread=1 sequence=10365
archived log file name=/arch1/arch1/1_10366_923589560.dbf thread=1 sequence=10366
RMAN-08187: WARNING: media recovery until SCN 11153265415 complete
archived log file name=/arch1/arch1/1_10366_923589560.dbf thread=1 sequence=10367
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/30/2016 16:30:56
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/exeution of SQL statement: alter database recover logfile ‘/arch1/arch1/1_10366_923589560.dbf’
ORA-00283: recovery session canceled due to errors
ORA-01112: media recovery not started
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10367 and starting SCN of 11153265415

recover已经完成,结尾输出可以忽略。

7 处理redo,并试图打开库

RMAN> run
2> {
3> allocate channel c1 type disk;
4> sql “alter database rename file ”+DATA01/gxd/redo01.log” to ”/yang/gxd/redo01.log””;
5> sql “alter database rename file ”+DATA01/gxd/redo02.log” to ”/yang/gxd/redo02.log””;
6> sql “alter database rename file ”+DATA01/gxd/redo03.log” to ”/yang/gxd/redo03.log””;
7> sql “alter database rename file ”+DATA01/gxd/redo04.log” to ”/yang/gxd/redo04.log””;
8> sql “alter database rename file ”+DATA01/gxd/stby01.log” to ”/yang/gxd/stby01.log””;
9> sql “alter database rename file ”+DATA01/gxd/redo05.log” to ”/yang/gxd/redo05.log””;
10> sql “alter database rename file ”+DATA02/gxd/redo06.log” to ”/yang/gxd/redo06.log””;
11> sql “alter database rename file ”+DATA02/gxd/redo07.log” to ”/yang/gxd/redo07.log””;
12> sql “alter database rename file ”+DATA02/gxd/redo08.log” to ”/yang/gxd/redo08.log””;
13> sql “alter database rename file ”+DATA02/gxd/redo09.log” to ”/yang/gxd/redo09.log””;
14> sql “alter database rename file ”+DATA02/gxd/redo10.log” to ”/yang/gxd/redo10.log””;
15> release channel c1;
16> }

allocated channel: c1
channel c1: SID=1015 device type=DISK

sql statement: alter database rename file ”+DATA01/gxd/redo01.log” to ”/yang/gxd/redo01.log”
sql statement: alter database rename file ”+DATA01/gxd/redo02.log” to ”/yang/gxd/redo02.log”
sql statement: alter database rename file ”+DATA01/gxd/redo03.log” to ”/yang/gxd/redo03.log”
sql statement: alter database rename file ”+DATA01/gxd/redo04.log” to ”/yang/gxd/redo04.log”
sql statement: alter database rename file ”+DATA01/gxd/stby01.log” to ”/yang/gxd/stby01.log”
sql statement: alter database rename file ”+DATA01/gxd/redo05.log” to ”/yang/gxd/redo05.log”
sql statement: alter database rename file ”+DATA02/gxd/redo06.log” to ”/yang/gxd/redo06.log”
sql statement: alter database rename file ”+DATA02/gxd/redo07.log” to ”/yang/gxd/redo07.log”
sql statement: alter database rename file ”+DATA02/gxd/redo08.log” to ”/yang/gxd/redo08.log”
sql statement: alter database rename file ”+DATA02/gxd/redo09.log” to ”/yang/gxd/redo09.log”
sql statement: alter database rename file ”+DATA02/gxd/redo10.log” to ”/yang/gxd/redo10.log”

released channel: c1

试图打开库报错

RMAN> run
2> {
3> allocate channel c1 type disk;
4> sql “alter database open resetlogs”;
5> release channel c1;
6> }

allocated channel: c1
channel c1: SID=1015 device type=DISK

sql statement: alter database open resetlogs
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 11/30/2016 16:34:57
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/yang/gxd/system.260.923589565’

RMAN>

报错原因是控件文件scn与数据文件不一致。

8 重建控制文件

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 30 16:36:38 2016

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3524329472 bytes
Fixed Size 2258200 bytes
Variable Size 1358957288 bytes
Database Buffers 2147483648 bytes
Redo Buffers 15630336 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE “GXD” RESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 3000
5 MAXINSTANCES 32
6 MAXLOGHISTORY 9344
7 LOGFILE
8 GROUP 1 ‘/yang/gxd/redo01.log’ SIZE 1024M BLOCKSIZE 512,
9 GROUP 2 ‘/yang/gxd/redo02.log’ SIZE 1024M BLOCKSIZE 512,
10 GROUP 5 ‘/yang/gxd/redo05.log’ SIZE 1024M BLOCKSIZE 512,
11 GROUP 7 ‘/yang/gxd/redo07.log’ SIZE 1024M BLOCKSIZE 512,
12 GROUP 9 ‘/yang/gxd/redo09.log’ SIZE 1024M BLOCKSIZE 512
13 DATAFILE
14 ‘/yang/gxd/system.260.923589565’,
15 ‘/yang/gxd/sysaux.261.923589567’,
… …
122 ‘/yang/gxd/tbs_datacenter.270.926684377’,
123 ‘/yang/gxd/undotbs1.380.928391267’,
124 ‘/yang/gxd/undotbs2.368.928391331’
125 CHARACTER SET ZHS16GBK
126 ;

Control file created.

脚本在源库生成。

9 打开库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> show parameter cluster

NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL>

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
2 GROUP 3 ‘/yang/gxd/redo03.log’ SIZE 1024M REUSE,
3 GROUP 4 ‘/yang/gxd/redo04.log’ SIZE 1024M REUSE;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> alter database disable thread 2;

Database altered.

打开成功！

10 其它
1）Oracle 12C rman有recover table选项
2）单表恢复是以表空间为单位，所以表空间不要太大
3)除相应数据表空间,system、sysaux、undo是必须的