情况:
1)从一个有集群有dg的实例—>恢复到单实例
2)存储路径相同
3)数据量5T左右
1、配置/etc/hosts文件
172.16.xx.xx sznwk01 //带库备份服务器ip
172.31.xx.xx szcssjk01 //恢复库ip
172.16.xx.xx szcssjk01_nwk //恢复库备份ip
172.16.xx.xx sznsapora01_nwk //源库的备份ip
2、在恢复机运行如下命令查找networker的备份记录
$ nsrinfo -s sznwk01 -n oracle sznsapora01 |grep ^c|more
c-2208694315-20180423-04, date=1524442627 Mon 23 Apr 2018 08:17:07 AM CST
cntrl_228610_1_20180423, date=1524442612 Mon 23 Apr 2018 08:16:52 AM CST
以下为需要恢复的controlfile:
c-2208694315-20180423-04
//这里我直接选择了最新的归档,没有认真筛选,直接造成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=8192
*.db_create_file_dest='+DG_DATA01'
*.db_domain=''
*.db_name='PMSSC'
*.diagnostic_dest='/oracle/app'
*.dispatchers=''
*.local_listener=''
*.open_cursors=60000
*.pga_aggregate_target=100687091200
*.processes=3500
*.resource_limit=TRUE
*.sessions=2205
*.sga_max_size=349755813888
*.sga_target=349755813888
*.undo_retention=7200
*.undo_tablespace='UNDOTBS1'
*.db_files=1024
4、开启库startup nomount pfile=‘路径’;
5、以下在恢复机上运行
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 23 09:48:25 2018
Copyright (c) 1982, 2011, 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/1024/1024/1024 from v$datafile where bytes=0;
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 1.5942E+13 2018-04-21 03:30:18 1
NO ONLINE 1.5942E+13 2018-04-21 05:57:33 1
NO ONLINE 1.5942E+13 2018-04-21 06:33:26 1
6、创建一个spfile
SQL> create spfile from pfile='/oracle/pfile0926.ora';
File created.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3.4848E+11 bytes
Fixed Size 2275976 bytes
Variable Size 2.1475E+10 bytes
Database Buffers 3.2642E+11 bytes
Redo Buffers 582516736 bytes
ORA-00205: 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.268.974195339
*.control_files='+DG_DATA01/pmssc/controlfile/current.268.974195339'
重新打开试试
[oradba@szcssjk01:/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 24 20:53:32 2018
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3.4848E+11 bytes
Fixed Size 2275976 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 3.2534E+11 bytes
Redo Buffers 582516736 bytes
ORA-00205: 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/11.2.0/dbhome_1/dbs/spfilePM
SSC.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_DATA01/pmssc/controlfile/c
urrent.256.880387951
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 1:
ORA-02095: 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-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3.4848E+11 bytes
Fixed Size 2275976 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 3.2534E+11 bytes
Redo Buffers 582516736 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 51791
Next log sequence to archive 51794
Current log sequence 51794
8、选择恢复时间点的思路
查询备份的时间点
SQL> set linesize 200 pages 999
SQL> COL STATUS FORMAT a9
SQL> COL hours FORMAT 999.999
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,
2 INPUT_BYTES_DISPLAY in_size,
3 OUTPUT_BYTES_DISPLAY out_size,
4 COMPRESSION_RATIO,
5 INPUT_BYTES_PER_SEC_DISPLAY in_sec,
6 OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
7 TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,
8 TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time,
9 ELAPSED_SECONDS/3600 hours
10 FROM V$RMAN_BACKUP_JOB_DETAILS
11 ORDER BY SESSION_KEY;
SESSION_KEY INPUT_TYPE STATUS IN_SIZE OUT_SIZE COMPRESSION_RATIO IN_SEC OUT_SEC START_TIME END_TIME HOURS
----------- ------------- --------- --------------- --------------- ----------------- --------------- --------------- ---------------- ---------------- --------
40718 CONTROLFILE COMPLETED 112.38M 112.75M 1 7.02M 7.05M 2018-03-29 19:50 2018-03-29 19:51 .004
40725 DB INCR COMPLETED 4.85T 86.89G 57.1878489 883.06M 15.44M 2018-03-30 01:12 2018-03-30 02:48 1.601
40734 ARCHIVELOG COMPLETED 58.98G 58.99G 1 119.37M 119.37M 2018-03-30 07:42 2018-03-30 07:50 .141
40741 ARCHIVELOG COMPLETED 67.34G 67.34G 1 168.60M 168.61M 2018-03-30 19:42 2018-03-30 19:48 .114
40748 DB INCR COMPLETED 4.79T 4.13T 1.16011165 277.90M 239.55M 2018-03-31 01:12 2018-03-31 06:13 5.023
40754 ARCHIVELOG COMPLETED 62.34G 62.34G 1 95.42M 95.43M 2018-03-31 07:42 2018-03-31 07:53 .186
40761 ARCHIVELOG COMPLETED 56.01G 56.02G 1 85.74M 85.74M 2018-03-31 19:41 2018-03-31 19:52 .186
40768 DB INCR COMPLETED 4.84T 57.20G 86.5796288 917.35M 10.60M 2018-04-01 01:11 2018-04-01 02:43 1.536
40777 ARCHIVELOG COMPLETED 44.14G 44.14G 1 86.10M 86.10M 2018-04-01 07:41 2018-04-01 07:49 .146
40784 ARCHIVELOG COMPLETED 58.88G 58.88G 1 105.40M 105.40M 2018-04-01 19:41 2018-04-01 19:50 .159
40791 DB INCR COMPLETED 4.85T 79.31G 62.6368241 795.71M 12.70M 2018-04-02 01:11 2018-04-02 02:58 1.776
40800 ARCHIVELOG COMPLETED 60.46G 60.47G 1 91.05M 91.05M 2018-04-02 07:41 2018-04-02 07:52 .189
40807 ARCHIVELOG COMPLETED 73.17G 73.18G 1 145.78M 145.78M 2018-04-02 19:41 2018-04-02 19:49 .143
40814 DB INCR COMPLETED 4.79T 4.14T 1.15849745 298.47M 257.63M 2018-04-03 01:11 2018-04-03 05:52 4.677
40820 ARCHIVELOG COMPLETED 68.05G 68.05G 1 88.20M 88.20M 2018-04-03 07:41 2018-04-03 07:54 .219
40827 ARCHIVELOG COMPLETED 68.89G 68.89G 1 104.82M 104.83M 2018-04-03 19:41 2018-04-03 19:52 .187
40834 DB INCR COMPLETED 4.85T 75.06G 66.130855 853.14M 12.90M 2018-04-04 01:11 2018-04-04 02:50 1.655
40843 ARCHIVELOG COMPLETED 55.70G 55.71G 1 108.03M 108.04M 2018-04-04 07:41 2018-04-04 07:50 .147
40850 ARCHIVELOG COMPLETED 65.91G 65.92G 1 138.31M 138.32M 2018-04-04 19:41 2018-04-04 19:49 .136
40857 DB INCR COMPLETED 4.85T 79.41G 62.5435295 888.16M 14.20M 2018-04-05 01:11 2018-04-05 02:47 1.591
40866 ARCHIVELOG COMPLETED 53.17G 53.17G 1 103.90M 103.90M 2018-04-05 07:41 2018-04-05 07:50 .146
40873 ARCHIVELOG COMPLETED 55.42G 55.42G 1 123.37M 123.38M 2018-04-05 19:41 2018-04-05 19:49 .128
40880 DB INCR COMPLETED 4.84T 52.16G 94.9336324 936.76M 9.87M 2018-04-06 01:11 2018-04-06 02:42 1.504
40889 ARCHIVELOG COMPLETED 34.22G 34.22G 1 100.40M 100.40M 2018-04-06 07:41 2018-04-06 07:47 .097
40896 ARCHIVELOG COMPLETED 40.47G 40.47G 1 104.38M 104.38M 2018-04-06 19:41 2018-04-06 19:48 .110
40903 DB INCR COMPLETED 4.79T 4.15T 1.15607045 314.55M 272.09M 2018-04-07 01:11 2018-04-07 05:38 4.438
40909 ARCHIVELOG COMPLETED 47.31G 47.31G 1 103.96M 103.96M 2018-04-07 07:41 2018-04-07 07:49 .129
40916 ARCHIVELOG COMPLETED 41.06G 41.06G 1 88.15M 88.15M 2018-04-07 19:41 2018-04-07 19:49 .133
40923 DB INCR COMPLETED 4.83T 49.32G 100.295459 911.72M 9.09M 2018-04-08 01:12 2018-04-08 02:44 1.543
40932 ARCHIVELOG COMPLETED 40.97G 40.97G 1 97.11M 97.11M 2018-04-08 07:41 2018-04-08 07:49 .120
40939 ARCHIVELOG COMPLETED 53.37G 53.38G 1 109.97M 109.97M 2018-04-08 19:41 2018-04-08 19:50 .138
40946 DB INCR COMPLETED 4.85T 80.52G 61.7002547 924.14M 14.98M 2018-04-09 01:12 2018-04-09 02:43 1.529
40955 ARCHIVELOG COMPLETED 61.16G 61.16G 1 99.41M 99.41M 2018-04-09 07:41 2018-04-09 07:52 .175
40962 ARCHIVELOG COMPLETED 102.33G 102.33G 1 117.87M 117.87M 2018-04-09 19:41 2018-04-09 19:56 .247
40969 DB INCR COMPLETED 4.79T 4.15T 1.15405469 260.70M 225.90M 2018-04-10 01:12 2018-04-10 06:33 5.355
40975 ARCHIVELOG COMPLETED 99.96G 99.96G 1 90.02M 90.02M 2018-04-10 07:41 2018-04-10 08:00 .316
40982 ARCHIVELOG COMPLETED 94.73G 94.73G 1 107.54M 107.55M 2018-04-10 19:42 2018-04-10 19:57 .251
40989 DB INCR COMPLETED 4.84T 67.45G 73.5491351 963.59M 13.10M 2018-04-11 01:12 2018-04-11 02:40 1.464
40998 ARCHIVELOG COMPLETED 53.35G 53.35G 1 125.58M 125.58M 2018-04-11 07:42 2018-04-11 07:49 .121
41005 ARCHIVELOG COMPLETED 63.06G 63.07G 1 123.00M 123.01M 2018-04-11 19:42 2018-04-11 19:50 .146
41012 DB INCR COMPLETED 4.79T 4.16T 1.1527753 267.62M 232.15M 2018-04-12 01:12 2018-04-12 06:25 5.216
41013 DB INCR COMPLETED 4.84T 65.34G 75.8610614 795.24M 10.48M 2018-04-12 01:12 2018-04-12 02:58 1.773
41027 ARCHIVELOG COMPLETED 49.29G 49.29G 1 127.77M 127.77M 2018-04-12 07:42 2018-04-12 07:48 .110
41034 ARCHIVELOG COMPLETED 60.87G 60.87G 1 71.23M 71.23M 2018-04-12 19:42 2018-04-12 19:56 .243
41041 DB INCR COMPLETED 4.89T 141.36G 35.4095174 829.23M 23.42M 2018-04-13 01:12 2018-04-13 02:55 1.717
41050 ARCHIVELOG COMPLETED 100.91G 100.92G 1 126.48M 126.49M 2018-04-13 07:42 2018-04-13 07:55 .227
41057 ARCHIVELOG COMPLETED 110.11G 110.11G 1 137.50M 137.50M 2018-04-13 19:42 2018-04-13 19:55 .228
41064 DB INCR COMPLETED 4.79T 4.17T 1.14997603 194.90M 169.48M 2018-04-14 01:12 2018-04-14 08:22 7.163
41067 ARCHIVELOG COMPLETED 107.43G 107.43G 1 49.64M 49.64M 2018-04-14 07:42 2018-04-14 08:19 .616
41077 ARCHIVELOG COMPLETED 55.85G 55.86G 1 84.98M 84.99M 2018-04-14 19:42 2018-04-14 19:53 .187
41084 DB INCR COMPLETED 4.84T 56.38G 87.8757205 921.37M 10.48M 2018-04-15 01:12 2018-04-15 02:44 1.529
41093 ARCHIVELOG COMPLETED 46.53G 46.53G 1 118.83M 118.83M 2018-04-15 07:42 2018-04-15 07:49 .111
41100 ARCHIVELOG COMPLETED 49.54G 49.54G 1 80.39M 80.40M 2018-04-15 19:41 2018-04-15 19:51 .175
41107 DB INCR COMPLETED 4.83T 52.63G 94.0258834 968.49M 10.30M 2018-04-16 01:11 2018-04-16 02:38 1.453
41116 ARCHIVELOG COMPLETED 40.70G 40.70G 1 156.67M 156.67M 2018-04-16 07:41 2018-04-16 07:45 .074
41123 ARCHIVELOG COMPLETED 50.22G 50.22G 1 86.87M 86.87M 2018-04-16 19:41 2018-04-16 19:51 .164
41130 DB INCR COMPLETED 4.82T 4.17T 1.15597652 298.95M 258.61M 2018-04-17 01:11 2018-04-17 05:53 4.699
41136 ARCHIVELOG COMPLETED 56.34G 56.34G 1 84.59M 84.59M 2018-04-17 07:41 2018-04-17 07:52 .189
41143 ARCHIVELOG COMPLETED 64.94G 64.94G 1 86.02M 86.03M 2018-04-17 19:41 2018-04-17 19:54 .215
41150 DB INCR COMPLETED 4.88T 75.33G 66.2914623 879.64M 13.27M 2018-04-18 01:11 2018-04-18 02:48 1.615
41159 ARCHIVELOG COMPLETED 56.10G 56.10G 1 112.63M 112.63M 2018-04-18 07:41 2018-04-18 07:50 .142
41166 ARCHIVELOG COMPLETED 64.41G 64.41G 1 95.17M 95.17M 2018-04-18 19:41 2018-04-18 19:53 .193
41173 DB INCR COMPLETED 4.87T 64.41G 77.4134329 841.53M 10.87M 2018-04-19 01:11 2018-04-19 02:52 1.685
41182 ARCHIVELOG COMPLETED 48.90G 48.90G 1 149.03M 149.03M 2018-04-19 07:41 2018-04-19 07:47 .093
41189 ARCHIVELOG COMPLETED 113.65G 113.65G 1 97.22M 97.22M 2018-04-19 19:41 2018-04-19 20:01 .333
41196 DB INCR COMPLETED 4.93T 151.37G 33.3702443 807.08M 24.19M 2018-04-20 01:12 2018-04-20 02:58 1.780
41205 ARCHIVELOG COMPLETED 112.18G 112.19G 1 254.15M 254.16M 2018-04-20 07:41 2018-04-20 07:49 .126
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
76 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
10 1 51792 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:55:27 1.5943E+13 2018-04-23 05:02:32
1 1 51791 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:53:39 1.5943E+13 2018-04-23 04:55:27
4 1 51793 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 05:02:32 1.5943E+13 2018-04-23 07:42:04
7 1 51794 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:04 2.8147E+14
11 2 43837 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 07:16:49 1.5943E+13 2018-04-23 07:42:06
5 2 43836 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 05:16:50 1.5943E+13 2018-04-23 07:16:49
2 2 43835 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:59:16 1.5943E+13 2018-04-23 05:16:50
8 2 43838 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:06 2.8147E+14
9 3 21865 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:55:30 1.5943E+13 2018-04-23 04:58:31
12 3 21866 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:58:31 1.5943E+13 2018-04-23 06:01:28
3 3 21868 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:04 2.8147E+14
6 3 21867 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 06:01:28 1.5943E+13 2018-04-23 07:42:04
12 rows selected.
恢复的时间点选的是 2018-04-23 08:11:40 --->当时思路是选THREAD#=1(节点1)的
7 1 51794 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:04 2.8147E+14
所以要在2018-04-23 07:42:04之后一点
同时又要在归档备份的时间之前
arch_228608_1_20180423, date=1524442366 Mon 23 Apr 2018 08:12:46 AM CST
所以要在Mon 23 Apr 2018 08:12:46之前一点
$ nsrinfo -s sznwk01 -n oracle sznsapora01_nwk |grep ^ar|more
arch_228608_1_20180423, date=1524442366 Mon 23 Apr 2018 08:12:46 AM CST
arch_228607_1_20180423, date=1524442300 Mon 23 Apr 2018 08:11:40 AM CST
arch_228606_1_20180423, date=1524442125 Mon 23 Apr 2018 08:08:45 AM CST
arch_228605_1_20180423, date=1524441990 Mon 23 Apr 2018 08:06:30 AM CST
arch_228604_1_20180423, date=1524441834 Mon 23 Apr 2018 08:03:54 AM CST
9、进行恢复database
[oradba@szcssjk01:/home/oradba]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 24 20:57:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PMSSC (DBID=2208694315, 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=2201 device type=SBT_TAPE
channel t1: NMDA Oracle v8.2.1
allocated channel: t2
channel t2: SID=2256 device type=SBT_TAPE
channel t2: NMDA Oracle v8.2.1
sent command to channel: t1
sent command to channel: t2
Starting recover at 24-APR-18
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00219: +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 00081: +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 1
channel t2: restore complete, elapsed time: 00:00:35
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00022: +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 1
channel t1: restore complete, elapsed time: 00:01:00
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00127: +DG_DATA01/pmssc/datafile/undotbs3.474.974317845
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 1
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00219: +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 1
channel t1: restore complete, elapsed time: 00:01:05
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00051: +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 1
channel t1: restore complete, elapsed time: 00:01:55
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00081: +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 1
channel t1: restore complete, elapsed time: 00:00:25
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00052: +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 1
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00127: +DG_DATA01/pmssc/datafile/undotbs3.474.974317845
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 1
channel t2: restore complete, elapsed time: 00:06:21
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00022: +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 1
channel t2: restore complete, elapsed time: 00:03:55
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00052: +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 1
channel t1: restore complete, elapsed time: 00:05:00
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00022: +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 1
channel t2: restore complete, elapsed time: 00:01:05
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00051: +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 1
channel t2: restore complete, elapsed time: 00:01:45
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00219: +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 1
channel t2: restore complete, elapsed time: 00:01:15
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00081: +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 1
channel t1: restore complete, elapsed time: 00:05:16
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00127: +DG_DATA01/pmssc/datafile/undotbs3.474.974317845
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 1
channel t2: restore complete, elapsed time: 00:02:15
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00052: +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 1
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00051: +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 1
channel t1: restore complete, elapsed time: 00:01:55
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DG_DATA01/pmssc/datafile/undotbs1.261.974195431
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 1
channel t2: restore complete, elapsed time: 00:03:10
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +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-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/24/2018 21:20:11
RMAN-03099: job cancelled at user request
RMAN>
//觉得通道开少了,就开了8个,重新跑
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
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
10 1 51792 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:55:27 1.5943E+13 2018-04-23 05:02:32
1 1 0 1073741824 512 2 YES UNUSED 1.5943E+13 2018-04-23 04:53:39 1.5943E+13 2018-04-23 04:55:27
4 1 0 1073741824 512 2 YES UNUSED 1.5943E+13 2018-04-23 05:02:32 1.5943E+13 2018-04-23 07:42:04
7 1 0 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:04 2.8147E+14
11 2 43837 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 07:16:49 1.5943E+13 2018-04-23 07:42:06
5 2 0 1073741824 512 2 YES UNUSED 1.5943E+13 2018-04-23 05:16:50 1.5943E+13 2018-04-23 07:16:49
2 2 0 1073741824 512 2 YES UNUSED 1.5943E+13 2018-04-23 04:59:16 1.5943E+13 2018-04-23 05:16:50
8 2 0 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:06 2.8147E+14
9 3 0 1073741824 512 2 YES CLEARING 1.5943E+13 2018-04-23 04:55:30 1.5943E+13 2018-04-23 04:58:31
12 3 21866 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-23 04:58:31 1.5943E+13 2018-04-23 06:01:28
3 3 0 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-23 07:42:04 2.8147E+14
6 3 0 1073741824 512 2 YES UNUSED 1.5943E+13 2018-04-23 06:01:28 1.5943E+13 2018-04-23 07:42:04
12 rows selected.
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
10 1 0 1073741824 512 2 YES UNUSED 0 0
1 1 1 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-25 07:22:55 2.8147E+14
4 1 0 1073741824 512 2 YES UNUSED 0 0
7 1 0 1073741824 512 2 YES UNUSED 0 0
11 2 0 1073741824 512 2 YES UNUSED 0 0
5 2 0 1073741824 512 2 YES UNUSED 0 0
2 2 1 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-25 07:22:55 1.5943E+13 2018-04-25 07:38:32
8 2 0 1073741824 512 2 YES UNUSED 0 0
9 3 0 1073741824 512 2 YES UNUSED 0 0
12 3 0 1073741824 512 2 YES UNUSED 0 0
3 3 1 1073741824 512 2 YES INACTIVE 1.5943E+13 2018-04-25 07:22:55 1.5943E+13 2018-04-25 07:38:33
6 3 0 1073741824 512 2 YES UNUSED 0 0
12 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
---------- --------- --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
3 CLOSED PRIVATE
SQL> select group# from v$log where THREAD# =2 or THREAD#=3;
GROUP#
----------
2
3
5
6
8
9
11
12
8 rows selected.
SQL> alter database disable thread 2;
Database altered.
SQL> alter database disable thread 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- --------- ------- -------------------------------------------------------------------------------- ---
1 ONLINE +DG_DATA01/pmssc/onlinelog/redo_01.log NO
1 ONLINE +DG_DATA01/pmssc/onlinelog/redo_11.log NO
2 ONLINE +DG_DATA01/pmssc/onlinelog/redo_02.log NO
4 ONLINE +DG_DATA01/pmssc/onlinelog/redo_04.log NO
4 ONLINE +DG_DATA01/pmssc/onlinelog/redo_14.log NO
5 ONLINE +DG_DATA01/pmssc/onlinelog/redo_05.log NO
5 ONLINE +DG_DATA01/pmssc/onlinelog/redo_15.log NO
7 ONLINE +DG_DATA01/pmssc/onlinelog/redo_07.log NO
7 ONLINE +DG_DATA01/pmssc/onlinelog/redo_17.log NO
8 ONLINE +DG_DATA01/pmssc/onlinelog/redo_08.log NO
8 ONLINE +DG_DATA01/pmssc/onlinelog/redo_18.log NO
9 ONLINE +DG_DATA01/pmssc/onlinelog/redo_09.log NO
9 ONLINE +DG_DATA01/pmssc/onlinelog/redo_19.log NO
6 ONLINE +DG_DATA01/pmssc/onlinelog/redo_06.log NO
10 ONLINE +DG_DATA01/pmssc/onlinelog/redo_20.log NO
10 ONLINE +DG_DATA01/pmssc/onlinelog/redo_30.log NO
11 ONLINE +DG_DATA01/pmssc/onlinelog/redo_21.log NO
11 ONLINE +DG_DATA01/pmssc/onlinelog/redo_31.log NO
2 ONLINE +DG_DATA01/pmssc/onlinelog/redo_12.log NO
3 ONLINE +DG_DATA01/pmssc/onlinelog/redo_43.log NO
3 ONLINE +DG_DATA01/pmssc/onlinelog/redo_33.log NO
6 ONLINE +DG_DATA01/pmssc/onlinelog/redo_16.log NO
12 ONLINE +DG_DATA01/pmssc/onlinelog/redo_22.log NO
12 ONLINE +DG_DATA01/pmssc/onlinelog/redo_32.log NO
24 rows selected.
SQL> select group# from v$log where THREAD# =2 or THREAD#=3;
GROUP#
----------
2
3
5
6
8
9
11
12
8 rows selected.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- --------- --------
1 OPEN PUBLIC
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------- ------------- ------------------- ------------ -------------------
1 1 1 1073741824 512 2 NO CURRENT 1.5943E+13 2018-04-25 07:22:55 2.8147E+14
4 1 0 1073741824 512 2 YES UNUSED 0 0
7 1 0 1073741824 512 2 YES UNUSED 0 0
10 1 0 1073741824 512 2 YES UNUSED 0 0
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 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 3470
parallel_min_percent integer 0
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 1536
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 96
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 96
SQL> alter system set parallel_servers_target=30;
System altered.
SQL> alter system set parallel_max_servers=30;
System altered.
12、建立监听
netca