情况:
1)从一个有集群有dg的实例—>恢复到单实例
2)存储路径相同
3)数据量5T左右
1、配置/etc/hosts文件
.xx.xx sznwk01 //带库备份服务器ip
.xx.xx szcssjk01 //恢复库ip
.xx.xx szcssjk01_nwk //恢复库备份ip
.xx.xx sznsapora01_nwk //源库的备份ip
2、在恢复机运行如下命令查找networker的备份记录
$ nsrinfo -s sznwk01 -n oracle sznsapora01 |grep ^c|more
c---, date= Mon Apr :: AM CST
cntrl_228610_1_20180423, date= Mon Apr :: AM CST
以下为需要恢复的controlfile:
c---
//这里我直接选择了最新的归档,没有认真筛选,直接造成recover database 恢复很久
3、恢复环境部署
从 源端 复制$ORACLE_HOME/dbs/pfile参数文件到 恢复机 上
vi /oracle/pfile.ora 修改里面的参数路径(仅参考)
*.audit_file_dest='/oracle/app/oradba/admin/PMSSC/adump'//路径要建好
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.compatible='11.2.0.4.0'
*.control_files='+DG_DATA01/pmssc/controlfile/current.256.880387951' //这个我忘记更改了
*.db_block_size=
*.db_create_file_dest='+DG_DATA01'
*.db_domain=''
*.db_name='PMSSC'
*.diagnostic_dest='/oracle/app'
*.dispatchers=''
*.local_listener=''
*.open_cursors=
*.pga_aggregate_target=
*.processes=
*.resource_limit=TRUE
*.sessions=
*.sga_max_size=
*.sga_target=
*.undo_retention=
*.undo_tablespace='UNDOTBS1'
*.db_files=
4、开启库startup nomount pfile=‘路径’; 5、以下在恢复机上运行
$ rman target /
Recovery Manager: Release .. - Production on Mon Apr ::
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
connected to target database: PMSSC (not mounted) --->nomount状态
RMAN>run {
allocate channel t1 type 'sbt_tape';
send 'NSR_ENV=(NSR_SERVER=sznwk01,NSR_CLIENT=sznsapora01_nwk)';
restore controlfile from 'c-2208694315-20180423-04';
release channel t1;
}
RMAN> alter database mount;
RMAN> report schema;
RMAN> run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=sznwk01,NSR_CLIENT=sznsapora01_nwk)';
RESTORE DATABASE;
switch datafile all;
switch tempfile all;
release channel t1;
release channel t2;
}
可以通过这个命令查还有几个数据文件在恢复
SQL> select name,bytes/// from v$datafile where bytes=;
no rows selected
//数据量大,从第一天9点半恢复到第二天的晚上八点30分
SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
--- --------- ----------------------------------------------------------------- --- ------------------ ------------------- ----------
NO ONLINE E+ -- ::
NO ONLINE E+ -- ::
NO ONLINE E+ -- ::
6、创建一个spfile
SQL> create spfile from pfile='/oracle/pfile0926.ora';
File created.
SQL> shutdown immediate
ORA-: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
ORA-: error in identifying control file, check alert log for more info
发现控制文件有问题
回去找/oracle/pfile.ora
*.control_files='+DG_DATA01/pmssc/controlfile/current.256.880387951'
这个控制文件是源库恢复过来的那个,没有进行更改
ASMCMD> cd +DG_DATA01/pmssc/controlfile/
ASMCMD> ls
current
*.control_files='+DG_DATA01/pmssc/controlfile/current.268.974195339'
重新打开试试
[oradba@szcssjk01:/oracle]$ sqlplus / as sysdba
SQL*Plus: Release Production on Tue Apr ::
Copyright (c) , , Oracle. All rights reserved.
Connected to:
Oracle Database g Enterprise Edition Release - bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate
ORA-: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
ORA-: error in identifying control file, check alert log for more info
已经是spfile打开了的,所以刚刚改pfile没起作用,直接改control_files
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oradba/app/oradba/produc
t//dbhome_1/dbs/spfilePM
SSC.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer
control_files string +DG_DATA01/pmssc/controlfile/c
urrent
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set control_files='+DG_DATA01/pmssc/controlfile/current.268.974195339';
alter system set control_files='+DG_DATA01/pmssc/controlfile/current.268.974195339'
*
ERROR at line :
ORA-: specified initialization parameter cannot be modified
SQL> alter system set control_files='+DG_DATA01/pmssc/controlfile/current.268.974195339' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted.
7、配置归档路径
SQL> alter system set log_archive_dest_1='location=/data01/archivelog';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data01/archivelog
Oldest online log sequence
Next log sequence to archive
Current log sequence
8、选择恢复时间点的思路
查询备份的时间点
SQL> set linesize pages
SQL> COL STATUS FORMAT a9
SQL> COL hours FORMAT
SQL> col in_size for a15
SQL> col out_sec for a15
SQL> col in_sec for a15
SQL> col out_size for a15
SQL> SELECT SESSION_KEY, INPUT_TYPE, STATUS,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,
TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time,
ELAPSED_SECONDS/ hours
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
SESSION_KEY INPUT_TYPE STATUS IN_SIZE OUT_SIZE COMPRESSION_RATIO IN_SEC OUT_SEC START_TIME END_TIME HOURS
----------- ------------- --------- --------------- --------------- ----------------- --------------- --------------- ---------------- ---------------- --------
CONTROLFILE COMPLETED 38M 75M 02M 05M -- : -- :
DB INCR COMPLETED 85T 89G 06M 44M -- : -- :
ARCHIVELOG COMPLETED 98G 99G 37M 37M -- : -- :
ARCHIVELOG COMPLETED 34G 34G 60M 61M -- : -- :
DB INCR COMPLETED 79T 13T 90M 55M -- : -- :
ARCHIVELOG COMPLETED 34G 34G 42M 43M -- : -- :
ARCHIVELOG COMPLETED 01G 02G 74M 74M -- : -- :
DB INCR COMPLETED 84T 20G 35M 60M -- : -- :
ARCHIVELOG COMPLETED 14G 14G 10M 10M -- : -- :
ARCHIVELOG COMPLETED 88G 88G 40M 40M -- : -- :
DB INCR COMPLETED 85T 31G 71M 70M -- : -- :
ARCHIVELOG COMPLETED 46G 47G 05M 05M -- : -- :
ARCHIVELOG COMPLETED 17G 18G 78M 78M -- : -- :
DB INCR COMPLETED 79T 14T 47M 63M -- : -- :
ARCHIVELOG COMPLETED 05G 05G 20M 20M -- : -- :
ARCHIVELOG COMPLETED 89G 89G 82M 83M -- : -- :
DB INCR COMPLETED 85T 06G 14M 90M -- : -- :
ARCHIVELOG COMPLETED 70G 71G 03M 04M -- : -- :
ARCHIVELOG COMPLETED 91G 92G 31M 32M -- : -- :
DB INCR COMPLETED 85T 41G 16M 20M -- : -- :
ARCHIVELOG COMPLETED 17G 17G 90M 90M -- : -- :
ARCHIVELOG COMPLETED 42G 42G 37M 38M -- : -- :
DB INCR COMPLETED 84T 16G 76M 87M -- : -- :
ARCHIVELOG COMPLETED 22G 22G 40M 40M -- : -- :
ARCHIVELOG COMPLETED 47G 47G 38M 38M -- : -- :
DB INCR COMPLETED 79T 15T 55M 09M -- : -- :
ARCHIVELOG COMPLETED 31G 31G 96M 96M -- : -- :
ARCHIVELOG COMPLETED 06G 06G 15M 15M -- : -- :
DB INCR COMPLETED 83T 32G 72M 09M -- : -- :
ARCHIVELOG COMPLETED 97G 97G 11M 11M -- : -- :
ARCHIVELOG COMPLETED 37G 38G 97M 97M -- : -- :
DB INCR COMPLETED 85T 52G 14M 98M -- : -- :
ARCHIVELOG COMPLETED 16G 16G 41M 41M -- : -- :
ARCHIVELOG COMPLETED 33G 33G 87M 87M -- : -- :
DB INCR COMPLETED 79T 15T 70M 90M -- : -- :
ARCHIVELOG COMPLETED 96G 96G 02M 02M -- : -- :
ARCHIVELOG COMPLETED 73G 73G 54M 55M -- : -- :
DB INCR COMPLETED 84T 45G 59M 10M -- : -- :
ARCHIVELOG COMPLETED 35G 35G 58M 58M -- : -- :
ARCHIVELOG COMPLETED 06G 07G 00M 01M -- : -- :
DB INCR COMPLETED 79T 16T 62M 15M -- : -- :
DB INCR COMPLETED 84T 34G 24M 48M -- : -- :
ARCHIVELOG COMPLETED 29G 29G 77M 77M -- : -- :
ARCHIVELOG COMPLETED 87G 87G 23M 23M -- : -- :
DB INCR COMPLETED 89T 36G 23M 42M -- : -- :
ARCHIVELOG COMPLETED 91G 92G 48M 49M -- : -- :
ARCHIVELOG COMPLETED 11G 11G 50M 50M -- : -- :
DB INCR COMPLETED 79T 17T 90M 48M -- : -- :
ARCHIVELOG COMPLETED 43G 43G 64M 64M -- : -- :
ARCHIVELOG COMPLETED 85G 86G 98M 99M -- : -- :
DB INCR COMPLETED 84T 38G 37M 48M -- : -- :
ARCHIVELOG COMPLETED 53G 53G 83M 83M -- : -- :
ARCHIVELOG COMPLETED 54G 54G 39M 40M -- : -- :
DB INCR COMPLETED 83T 63G 49M 30M -- : -- :
ARCHIVELOG COMPLETED 70G 70G 67M 67M -- : -- :
ARCHIVELOG COMPLETED 22G 22G 87M 87M -- : -- :
DB INCR COMPLETED 82T 17T 95M 61M -- : -- :
ARCHIVELOG COMPLETED 34G 34G 59M 59M -- : -- :
ARCHIVELOG COMPLETED 94G 94G 02M 03M -- : -- :
DB INCR COMPLETED 88T 33G 64M 27M -- : -- :
ARCHIVELOG COMPLETED 10G 10G 63M 63M -- : -- :
ARCHIVELOG COMPLETED 41G 41G 17M 17M -- : -- :
DB INCR COMPLETED 87T 41G 53M 87M -- : -- :
ARCHIVELOG COMPLETED 90G 90G 03M 03M -- : -- :
ARCHIVELOG COMPLETED 65G 65G 22M 22M -- : -- :
DB INCR COMPLETED 93T 37G 08M 19M -- : -- :
ARCHIVELOG COMPLETED 18G 19G 15M 16M -- : -- :
ARCHIVELOG COMPLETED 22G 22G 46M 46M -- : -- :
DB INCR COMPLETED 84T 20T 05M 67M -- : -- :
ARCHIVELOG COMPLETED 53G 53G 92M 93M -- : -- :
ARCHIVELOG COMPLETED 20G 20G 00M 00M -- : -- :
DB INCR COMPLETED 31T 29G 05M 88M -- : -- :
ARCHIVELOG COMPLETED 54G 54G 81M 81M -- : -- :
ARCHIVELOG COMPLETED 92G 92G 30M 30M -- : -- :
DB INCR COMPLETED 12T 53G 79M 66M -- : -- :
ARCHIVELOG COMPLETED 07G 07G 75M 75M -- : -- :
rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
YES INACTIVE -- :: -- ::
YES INACTIVE -- :: -- ::
YES INACTIVE -- :: -- ::
NO CURRENT -- ::
YES INACTIVE -- :: -- ::
YES INACTIVE -- :: -- ::
YES INACTIVE -- :: -- ::
NO CURRENT -- ::
YES INACTIVE -- :: -- ::
YES INACTIVE -- :: -- ::
NO CURRENT -- ::
YES INACTIVE -- :: -- ::
rows selected.
恢复的时间点选的是 -- :: --->当时思路是选THREAD#=1(节点1)的
NO CURRENT -- ::
所以要在-- ::之后一点
同时又要在归档备份的时间之前
arch_228608_1_20180423, date= Mon Apr :: AM CST
所以要在Mon Apr ::之前一点
$ nsrinfo -s sznwk01 -n oracle sznsapora01_nwk |grep ^ar|more
arch_228608_1_20180423, date= Mon Apr :: AM CST
arch_228607_1_20180423, date= Mon Apr :: AM CST
arch_228606_1_20180423, date= Mon Apr :: AM CST
arch_228605_1_20180423, date= Mon Apr :: AM CST
arch_228604_1_20180423, date= Mon Apr :: AM CST
9、进行恢复database
[oradba@szcssjk01:/home/oradba]$ rman target /
Recovery Manager: Release .. - Production on Tue Apr ::
Copyright (c) , , Oracle and/or its affiliates. All rights reserved.
connected to target database: PMSSC (DBID=, not open)
RMAN> run {
2> allocate channel t1 type 'SBT_TAPE';
3> allocate channel t2 type 'SBT_TAPE';
4> send 'NSR_ENV=(NSR_SERVER=sznwk01,NSR_CLIENT=sznsapora01_nwk)';
5> recover database until time "to_date('04/23/18 08:11:40','MM/DD/YY HH24:MI:SS')";
6> release channel t1;
7> release channel t2;
8> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID= device type=SBT_TAPE
channel t1: NMDA Oracle v8.
allocated channel: t2
channel t2: SID= device type=SBT_TAPE
channel t2: NMDA Oracle v8.
sent command to channel: t1
sent command to channel: t2
Starting recover at -APR-
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_data_24.dbf
channel t1: reading from backup piece db_227014_1_20180418
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/other_data_18.dbf
channel t2: reading from backup piece db_227016_1_20180418
channel t2: piece handle=db_227016_1_20180418 tag=TAG20180418T011145
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/yd_indx_02.dbf
channel t2: reading from backup piece db_227015_1_20180418
channel t1: piece handle=db_227014_1_20180418 tag=TAG20180418T011145
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/undotbs3.
channel t1: reading from backup piece db_227017_1_20180418
channel t1: piece handle=db_227017_1_20180418 tag=TAG20180418T011145
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_data_24.dbf
channel t1: reading from backup piece db_227262_1_20180419
channel t1: piece handle=db_227262_1_20180419 tag=TAG20180419T011151
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_03.dbf
channel t1: reading from backup piece db_227020_1_20180418
channel t1: piece handle=db_227020_1_20180418 tag=TAG20180418T011145
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/other_data_18.dbf
channel t1: reading from backup piece db_227264_1_20180419
channel t1: piece handle=db_227264_1_20180419 tag=TAG20180419T011151
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_04.dbf
channel t1: reading from backup piece db_227021_1_20180418
channel t1: piece handle=db_227021_1_20180418 tag=TAG20180418T011145
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/undotbs3.
channel t1: reading from backup piece db_227265_1_20180419
channel t2: piece handle=db_227015_1_20180418 tag=TAG20180418T011145
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/yd_indx_02.dbf
channel t2: reading from backup piece db_227263_1_20180419
channel t2: piece handle=db_227263_1_20180419 tag=TAG20180419T011151
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_04.dbf
channel t2: reading from backup piece db_227269_1_20180419
channel t1: piece handle=db_227265_1_20180419 tag=TAG20180419T011151
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/yd_indx_02.dbf
channel t1: reading from backup piece db_227514_1_20180420
channel t2: piece handle=db_227269_1_20180419 tag=TAG20180419T011151
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_03.dbf
channel t2: reading from backup piece db_227268_1_20180419
channel t2: piece handle=db_227268_1_20180419 tag=TAG20180419T011151
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_data_24.dbf
channel t2: reading from backup piece db_227513_1_20180420
channel t2: piece handle=db_227513_1_20180420 tag=TAG20180420T011208
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/other_data_18.dbf
channel t2: reading from backup piece db_227515_1_20180420
channel t1: piece handle=db_227514_1_20180420 tag=TAG20180420T011208
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/undotbs3.
channel t1: reading from backup piece db_227516_1_20180420
channel t2: piece handle=db_227515_1_20180420 tag=TAG20180420T011208
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_04.dbf
channel t2: reading from backup piece db_227520_1_20180420
channel t1: piece handle=db_227516_1_20180420 tag=TAG20180420T011208
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/part_index_03.dbf
channel t1: reading from backup piece db_227519_1_20180420
channel t1: piece handle=db_227519_1_20180420 tag=TAG20180420T011208
channel t1: restored backup piece
channel t1: restore complete, elapsed time: ::
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/undotbs1.
channel t1: reading from backup piece db_227958_1_20180422
channel t2: piece handle=db_227520_1_20180420 tag=TAG20180420T011208
channel t2: restored backup piece
channel t2: restore complete, elapsed time: ::
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile : +DG_DATA01/pmssc/datafile/yd_data_03.dbf
channel t2: reading from backup piece db_227960_1_20180422
^C
user interrupt received
released channel: t1
released channel: t2
RMAN-: ===========================================================
RMAN-: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-: ===========================================================
RMAN-: failure of recover command at // ::
RMAN-: job cancelled at user request
RMAN>
//觉得通道开少了,就开了个,重新跑
RMAN> run {
2> allocate channel t1 type 'SBT_TAPE';
3> allocate channel t2 type 'SBT_TAPE';
4> allocate channel t3 type 'SBT_TAPE';
5> allocate channel t4 type 'SBT_TAPE';
6> allocate channel t5 type 'SBT_TAPE';
7> allocate channel t6 type 'SBT_TAPE';
8> allocate channel t7 type 'SBT_TAPE';
9> allocate channel t8 type 'SBT_TAPE';
10> send 'NSR_ENV=(NSR_SERVER=sznwk01,NSR_CLIENT=sznsapora01_nwk)';
11> recover database until time "to_date('04/23/18 08:11:40','MM/DD/YY HH24:MI:SS')";
12> release channel t1;
13> release channel t2;
14> release channel t3;
15> release channel t4;
16> release channel t5;
17> release channel t6;
18> release channel t7;
19> release channel t8;
20> }
从晚上八点九恢复到凌晨三点多
为什么恢复这么久,原因是
SESSION_KEY INPUT_TYPE STATUS IN_SIZE OUT_SIZE COMPRESSION_RATIO IN_SEC OUT_SEC START_TIME END_TIME HOURS
----------- ------------- --------- --------------- --------------- ----------------- --------------- --------------- ---------------- ---------------- --------
41212 ARCHIVELOG COMPLETED 227.22G 227.22G 1 171.46M 171.46M 2018-04-20 19:41 2018-04-20 20:04 .377
41219 DB INCR COMPLETED 4.84T 4.20T 1.15303243 259.05M 224.67M 2018-04-21 01:12 2018-04-21 06:38 5.442
(这里看出做了一个全备备份)
41225 ARCHIVELOG COMPLETED 340.53G 340.53G 1 117.92M 117.93M 2018-04-21 07:41 2018-04-21 08:31 .821
41232 ARCHIVELOG COMPLETED 511.20G 511.20G 1 101.00M 101.00M 2018-04-21 19:42 2018-04-21 21:08 1.440
41239 DB INCR COMPLETED 5.31T 564.29G 9.63882607 606.05M 62.88M 2018-04-22 01:12 2018-04-22 03:45 2.553
(这里看出做了一个增量备份)
41248 ARCHIVELOG COMPLETED 469.54G 469.54G 1 119.81M 119.81M 2018-04-22 07:42 2018-04-22 08:48 1.115
41255 ARCHIVELOG COMPLETED 458.92G 458.92G 1 205.30M 205.30M 2018-04-22 19:42 2018-04-22 20:20 .636
41262 DB INCR COMPLETED 5.12T 351.53G 14.9225776 591.79M 39.66M 2018-04-23 01:12 2018-04-23 03:43 2.521
(这里看出做了一个增量备份)
41271 ARCHIVELOG COMPLETED 178.07G 178.07G 1 86.75M 86.75M 2018-04-23 07:42 2018-04-23 08:17 .584
恢复的控制文件是 2018-04-23 08:17:07
恢复的时间点选的是 2018-04-23 08:11:40
恢复的时候是无条件选择全备的,所以它自动选择从2018-04-21 06:38开始,因为我们选择了2018-04-23 08:11:40,中间隔了两个是增量备份,如果是从21号开始恢复的话,一直恢复下去,恢复完全备还要轮两个增量备份的归档。
同步观察恢复日志情况
select * from v$Log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
YES INACTIVE -- :: -- ::
YES UNUSED -- :: -- ::
YES UNUSED -- :: -- ::
NO CURRENT -- ::
YES INACTIVE -- :: -- ::
YES UNUSED -- :: -- ::
YES UNUSED -- :: -- ::
NO CURRENT -- ::
YES CLEARING -- :: -- ::
YES INACTIVE -- :: -- ::
NO CURRENT -- ::
YES UNUSED -- :: -- ::
rows selected.
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
YES UNUSED
NO CURRENT -- ::
YES UNUSED
YES UNUSED
YES UNUSED
YES UNUSED
YES INACTIVE -- :: -- ::
YES UNUSED
YES UNUSED
YES UNUSED
YES INACTIVE -- :: -- ::
YES UNUSED
rows selected.
//直到SEQUENCE#=0,说明日志恢复完成
10、处理一下恢复的日志
HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node (Doc ID 415579.1)10) Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- --------- --------
OPEN PUBLIC
CLOSED PUBLIC
CLOSED PRIVATE
SQL> select group# from v$log where THREAD# = or THREAD#=;
GROUP#
----------
rows selected.
SQL> alter database disable thread ;
Database altered.
SQL> alter database disable thread ;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- --------- ------- -------------------------------------------------------------------------------- ---
ONLINE +DG_DATA01/pmssc/onlinelog/redo_01.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_11.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_02.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_04.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_14.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_05.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_15.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_07.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_17.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_08.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_18.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_09.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_19.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_06.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_20.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_30.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_21.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_31.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_12.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_43.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_33.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_16.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_22.log NO
ONLINE +DG_DATA01/pmssc/onlinelog/redo_32.log NO
rows selected.
SQL> select group# from v$log where THREAD# = or THREAD#=;
GROUP#
----------
rows selected.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> alter database drop logfile group ;
Database altered.
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- --------- --------
OPEN PUBLIC
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
NO CURRENT -- ::
YES UNUSED
YES UNUSED
YES UNUSED
12、更改一下参数
SQL> show parameter paralle
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer
parallel_min_percent integer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer
parallel_servers_target integer
parallel_threads_per_cpu integer
recovery_parallelism integer
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer
parallel_threads_per_cpu integer
resource_manager_cpu_allocation integer
SQL> alter system set parallel_servers_target=;
System altered.
SQL> alter system set parallel_max_servers=;
System altered.
12、建立监听
netca