Chinaunix首页 | 论坛 | 博客
  • 博客访问: 484163
  • 博文数量: 158
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 904
  • 用 户 组: 普通用户
  • 注册时间: 2016-10-10 11:17
文章分类

全部博文(158)

文章存档

2018年(74)

2017年(84)

我的朋友

分类: 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

 

     目标库恢复

3.1       设置好ORACLE_SID

[oracle@oradata ~]$ echo $ORACLE_SID

orcl

 

3.2       启动伪实例(哑实例)DUMMY

[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,供恢复数据库使用。

 

3.3       重启实例

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

 

3.4       恢复控制文件

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

 

3.5       还原与恢复数据库

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

 

3.6       打开数据库

以resetlogs打开数据库

RMAN> alter database open resetlogs;

Statement processed

 

     检查目标库

在目标库查看实例状态和pdb状态:

image001.jpg

可以看到,实例orcl已是OPEN,ORCLPDB的状态是READ WRITE,数据库已在目标库恢复完成。

 

     遇到的问题

虽然数据库已经恢复完成,但有几个问题需要解决。

l  此时应用是无法访问数据库的,为什么呢?

因为是异机恢复,目标库上只安装了数据库软件,没有创建监听。

l  当服务器关机、开机时,oracle的实例不能自动关闭、开启,为什么呢?

还是因为是异机恢复,没有设置oracle随服务器启动关闭而启动关闭。

l  在oracle12c中,实例启动了,里边的pdb不能启动到READ WRITE状态,为什么呢?

Pdb是oracle12c的新特性,它默认不随实例启动而启动,如果需要,可以进行设置。

下面记录下笔者处理这些问题的过程:

5.1       配置监听

 一开始,笔者发现监听一直无法动态注册。默认情况下,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,发现不是默认的空:

image002.jpg

 

那么,需要在tnsnames.ora文件中添加对应的信息:

image003.jpg

因为pmon在动态注册监听时要从tnsnames.ora中读取相关信息。

修改好后,监听就可以正常动态注册了。

 

5.2          启动与关闭

当服务器需要关机维护时,需要将数据库正确的关闭,以免数据的丢失。但要是DBA不在,那就比较麻烦,所以,有必要将数据库设置成在系统关闭前自动关闭、在系统启动后自动启动。

实现系统关闭oracle实例自关闭步骤:

1、创建k01orashut文件,将su -c "dbshut" - oracle 命令写入该文件

image004.jpg 

2、修改权限为可执行

#chmod 777 /etc/rc.d/init.d/k01orashut

image005.jpg

 

3、进入/etc/rc3.d/目录,创建关机执行命令连接

#ln -s /etc/rc.d/init.d/k01orashut

image006.jpg

 

实现系统启动oracle实例自启动步骤:

1、修改Oracle系统配置文件/etc/oratab内容,将最后一行中最后一个字符的N改为Y,如果没有,可以手动添加,格式:$ORACLE_SID:$ORACLE_HOME:

image007.jpg

 

2、RedHat修改/etc/rc.d/rc.local文件,添加如下内容即可:

image008.jpg

 

3、切换到oracle用户登录修改$ORACLE_HOME/bin/dbstart,找到ORACLE_HOME_LISTNER=,将值改为ORACLE_HOME的路径,或环境变量($ORACLE_HOME)。

image009.jpg

 

5.3       打开PDB

方法一:

使用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;


阅读(1676) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~