分类： Oracle
2017-12-28 13:49:54
【摘要】
昨晚，笔者做了个oracle 12c的整库迁移，用的是rman工具，因为目标库只安装了数据库软件，所以在备份恢复完成后还遇到了一些问题，经过逐一解决，数据库服务现已恢复正常，在此记录相关过程。
【正文】
操作系统版本：Red Hat Enterprise Linux Server release 6.5 (Santiago)
Database版本：Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
注意：数据库的安装过程在此省略，源库与目标库的目录结构一致，包括rman备份集存放路径。
在源库用rman进行压缩全备，包括
[root@oatest ~]# su - oracle
[oracle@oatest ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Dec 22 20:43:25 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1489393547)
RMAN> backup as compressed backupset full database
2> format '/u01/backup/full_bk1_%u%p%s.dfb'
3> include current controlfile
4> plus archivelog format '/u01/backup/arch_bk1_%u%p%s.dfb'
5> delete all input;
Starting backup at 22-DEC-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=335 RECID=1 STAMP=963434609
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/arch_bk1_01smpm3h11.dfb tag=TAG20171222T204329 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2017_12_22/o1_mf_1_335_f3szhk75_.arc RECID=1 STAMP=963434609
Finished backup at 22-DEC-17
Starting backup at 22-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
略……
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/orclpdb/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
略……
input datafile file number=00012 name=/u01/app/oracle/oradata/orcl/orclpdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/full_bk1_02smpm3j12.dfb tag=TAG20171222T204331 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:51:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/full_bk1_03smpp4113.dfb tag=TAG20171222T204331 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/full_bk1_04smpp6m14.dfb tag=TAG20171222T204331 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/full_bk1_05smpp7p15.dfb tag=TAG20171222T204331 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-DEC-17
Starting backup at 22-DEC-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=336 RECID=2 STAMP=963437819
channel ORA_DISK_1: starting piece 1 at 22-DEC-17
channel ORA_DISK_1: finished piece 1 at 22-DEC-17
piece handle=/u01/backup/arch_bk1_06smpp7r16.dfb tag=TAG20171222T213659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2017_12_22/o1_mf_1_336_f3t2mvgj_.arc RECID=2 STAMP=963437819
Finished backup at 22-DEC-17
Starting Control File and SPFILE Autobackup at 22-DEC-17
piece handle=/u01/app/oracle/recovery_area/ORCL/autobackup/2017_12_22/o1_mf_s_963437820_f3t2mwz5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-DEC-17
备份完成后，将备份集和SPFILE的备份（o1_mf_s_963437820_f3t2mwz5_.bkp）以oracle用户身份（scp）传送到目标库：
[root@oatest backup]# scp *.dfb 10.17.0.99:/u01/backup/
root@10.17.0.99's password:
arch_bk1_01smpm3h11.dfb 100% 4545KB 4.4MB/s 00:00
arch_bk1_06smpp7r16.dfb 100% 3038KB 3.0MB/s 00:00
full_bk1_02smpm3j12.dfb 100% 17GB 68.9MB/s 04:06
full_bk1_03smpp4113.dfb 100% 526MB 75.2MB/s 00:07
full_bk1_04smpp6m14.dfb 100% 163MB 54.5MB/s 00:03
full_bk1_05smpp7p15.dfb 100% 1088KB 1.1MB/s 00:00
[oracle@oradata backup]$ ll -lrth
total 18G
-rw-r----- 1 oracle oinstall 4.5M Dec 22 21:38 arch_bk1_01smpm3h11.dfb
-rw-r----- 1 oracle oinstall 3.0M Dec 22 21:38 arch_bk1_06smpp7r16.dfb
-rw-r----- 1 oracle oinstall 17G Dec 22 21:42 full_bk1_02smpm3j12.dfb
-rw-r----- 1 oracle oinstall 527M Dec 22 21:42 full_bk1_03smpp4113.dfb
-rw-r----- 1 oracle oinstall 164M Dec 22 21:42 full_bk1_04smpp6m14.dfb
-rw-r----- 1 oracle oinstall 1.1M Dec 22 21:42 full_bk1_05smpp7p15.dfb
-rw-r----- 1 oracle oinstall 18M Dec 22 22:07 o1_mf_s_963437820_f3t2mwz5_.bkp
[oracle@oradata ~]$ echo $ORACLE_SID
orcl
[oracle@oradata ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Dec 22 21:49:39 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 1073745224 bytes
Database Buffers 1006632960 bytes
Redo Buffers 58482688 bytes
RMAN>
这时，oracle会启动一个默认的实例：DUMMY，供恢复数据库使用。
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 10133438464 bytes
Fixed Size 12342160 bytes
Variable Size 2348813424 bytes
Database Buffers 7717519360 bytes
Redo Buffers 54763520 bytes
RMAN> restore controlfile from '/u01/backup/o1_mf_s_963437820_f3t2mwz5_.bkp';
Starting restore at 22-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 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/orcl/control01.ctl
output file name=/u01/app/oracle/recovery_area/orcl/control02.ctl
Finished restore at 22-DEC-17
RMAN> run{
2> restore database;
3> recover database;
4> }
Starting restore at 22-DEC-17
Starting implicit crosscheck backup at 22-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=379 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 22-DEC-17
Starting implicit crosscheck copy at 22-DEC-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-DEC-17
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/orclpdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/orclpdb/users01.dbf
略……
channel ORA_DISK_1: reading from backup piece /u01/backup/full_bk1_02smpm3j12.dfb
channel ORA_DISK_1: piece handle=/u01/backup/full_bk1_02smpm3j12.dfb tag=TAG20171222T204331
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 01:09:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_bk1_03smpp4113.dfb
channel ORA_DISK_1: piece handle=/u01/backup/full_bk1_03smpp4113.dfb tag=TAG20171222T204331
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_bk1_04smpp6m14.dfb
channel ORA_DISK_1: piece handle=/u01/backup/full_bk1_04smpp6m14.dfb tag=TAG20171222T204331
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 23-DEC-17
Starting recover at 23-DEC-17
using channel ORA_DISK_1
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=336
channel ORA_DISK_1: reading from backup piece /u01/backup/arch_bk1_06smpp7r16.dfb
channel ORA_DISK_1: piece handle=/u01/backup/arch_bk1_06smpp7r16.dfb tag=TAG20171222T213659
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/recovery_area/ORCL/archivelog/2017_12_23/o1_mf_1_336_f3tfn2lj_.arc thread=1 sequence=336
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/recovery_area/ORCL/archivelog/2017_12_23/o1_mf_1_336_f3tfn2lj_.arc RECID=3 STAMP=963449091
unable to find archived log
archived log thread=1 sequence=337
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2017 00:44:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 337 and starting SCN of 512323045349
这里提示介质恢复需要的归档日志文件不存在。
[oracle@oradata backup]$ oerr RMAN 06054
6054, 1, "media recovery requesting unknown archived log for thread %(1)s with sequence %(2)s and starting SCN of %(3)s"
// *Cause: Media recovery is requesting a log whose existence is not
// recorded in the recovery catalog or target database control file.
// *Action: If a copy of the log is available, then add it to the recovery
// catalog and/or control file via a CATALOG command and then
// retry the RECOVER command. If not, then a point-in-time recovery
// up to the missing log is the only alternative and database can
// be opened using ALTER DATABASE OPEN RESETLOGS command.
因为是异机恢复，所以可以按照提示将数据库恢复到对应的scn进行不完全恢复，本案例是512323045349。
RMAN> recover database until scn 512323045349;
Starting recover at 23-DEC-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 23-DEC-17
以resetlogs打开数据库
RMAN> alter database open resetlogs;
Statement processed
在目标库查看实例状态和pdb状态：
可以看到，实例orcl已是OPEN，ORCLPDB的状态是READ WRITE，数据库已在目标库恢复完成。
虽然数据库已经恢复完成，但有几个问题需要解决。
l 此时应用是无法访问数据库的，为什么呢？
因为是异机恢复，目标库上只安装了数据库软件，没有创建监听。
l 当服务器关机、开机时，oracle的实例不能自动关闭、开启，为什么呢？
还是因为是异机恢复，没有设置oracle随服务器启动关闭而启动关闭。
l 在oracle12c中，实例启动了，里边的pdb不能启动到READ WRITE状态，为什么呢？
Pdb是oracle12c的新特性，它默认不随实例启动而启动，如果需要，可以进行设置。
下面记录下笔者处理这些问题的过程：
一开始，笔者发现监听一直无法动态注册。默认情况下，在instance启动的时候，PMON进程根据init.ora中的instance_name,service_names两个参数将实例和服务动态注册到listener中。
这里要注意的是：动态注册默认只注册到默认的监听器上(名称是LISTENER、端口是1521、协议是TCP)，因为pmon只会动态注册port等于1521的监听，否则pmon不能动态注册listener，如果需要向非默认监听注册，则需要配置local_listener参数。并将监听的信息添加到tnsnames.ora文件中(或者配置local_listener参数的值为LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1522)))。
注意，是tnsnames.ora 文件，因为pmon在动态注册监听时要从tnsnames.ora中读取相关信息。
检查local_listener，发现不是默认的空：
那么，需要在tnsnames.ora文件中添加对应的信息：
因为pmon在动态注册监听时要从tnsnames.ora中读取相关信息。
修改好后，监听就可以正常动态注册了。
当服务器需要关机维护时，需要将数据库正确的关闭，以免数据的丢失。但要是DBA不在，那就比较麻烦，所以，有必要将数据库设置成在系统关闭前自动关闭、在系统启动后自动启动。
实现系统关闭oracle实例自关闭步骤：
1、创建k01orashut文件，将su -c "dbshut" - oracle 命令写入该文件
2、修改权限为可执行
#chmod 777 /etc/rc.d/init.d/k01orashut
3、进入/etc/rc3.d/目录，创建关机执行命令连接
#ln -s /etc/rc.d/init.d/k01orashut
实现系统启动oracle实例自启动步骤：
1、修改Oracle系统配置文件/etc/oratab内容,将最后一行中最后一个字符的N改为Y，如果没有，可以手动添加，格式：$ORACLE_SID:$ORACLE_HOME:
2、RedHat修改/etc/rc.d/rc.local文件，添加如下内容即可:
3、切换到oracle用户登录修改$ORACLE_HOME/bin/dbstart,找到ORACLE_HOME_LISTNER=，将值改为ORACLE_HOME的路径，或环境变量（$ORACLE_HOME）。
方法一：
使用sys用户创建如下触发器：
CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
方法二：
在cdb中执行：
alter pluggable database all save state;
