当前位置: 首页 > Oracle, oracle 11g > 正文

使用RMAN备份在一台机器上恢复已有DB NAME数据库

案例背景,开发人员需要从生产库同步数据到UAT数据库,由于数据量较大,恢复需要的时间较长,而这期间测试人员还要在UAT环境进行测试,不能耽误测试人员的工作,这样就需要在恢复数据库的同时,UAT数据库还要正常对外提供服务,这样,UAT服务器上就需要面临两套DBID和DBNAME同样的数据库,数据库版本11.2.0.4.0。

如果同一台主机上存在两个DBNAME相同的数据库,那么在后启动的数据库在MOUNT的时候会遇到ORA-01102错误。

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode

告警日志报错信息如下;

ALTER DATABASE   MOUNT
sculkget: failed to lock /opt/oracle/product/11g/dbs/lkIVLDB exclusive
sculkget: lock held by PID: 9257
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 9257
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

这个问题是因为在$ORACLE_HOME/dbs目录下面的lk+DBNAME文件的锁竞争导致的,如果删除这个文件,那么后启动的数据库可以正常启动,之前启动的数据库将不能正常访问。因此,在使用RMAN恢复数据库之前,需要将之前的数据库的DBNAME改掉,如下为使用nid工具修改DBNAME案例:

首先将数据库启动到MOUNT状态。

[oracle@SL010A-ISITDB3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 15 10:30:17 2015

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, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
Database mounted.

使用nid修改数据库的DBNAME。

[oracle@SL010A-ISITDB3 ~]$ nid target=/as sysdba dbname=IVLUAT

DBNEWID: Release 11.2.0.4.0 - Production on Mon Jun 15 10:31:05 2015

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

Password: 
Connected to database XXXX (DBID=1353335250)

Connected to server version 11.2.0

Control Files in database:
    /data/u01/XXXX/control01.ctl
    /opt/oracle/fast_recovery_area/XXXX/control02.ctl

Change database ID and database name XXXX to IVLUAT? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1353335250 to 2742253550
Changing database name from IVLDB to IVLUAT
    Control File /data/u01/IVLDB/control01.ctl - modified
    Control File /opt/oracle/fast_recovery_area/IVLDB/control02.ctl - modified
    Datafile /data/u01/IVLDB/system01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/sysaux01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/undotbs01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/users01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_MC01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV03.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV04.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV05.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV06.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV07.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV08.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV09.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV10.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_WLIF01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_WLIF02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_TS_TK01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/IDX_TS_IN01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/IDX_TS_IN02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG03.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG04.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG05.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG06.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG07.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG08.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG09.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG10.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/system02.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/system03.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/temp01.db - dbid changed, wrote new name
    Control File /data/u01/IVLDB/control01.ctl - dbid changed, wrote new name
    Control File /opt/oracle/fast_recovery_area/IVLDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to IVLUAT.
Modify parameter file and generate a new password file before restarting.
Database ID for database IVLUAT changed to 2742253550.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

修改参数文件和密码文件。

[oracle@SL010A-IVO03 ~]$ cd $ORACLE_HOME/dbs
[oracle@SL010A-IVO03 dbs]$ mv orapwIVLDB orapwIVLUAT
SQL> alter system set db_name='IVLUAT' SCOPE=SPFILE;
System altered.

启动数据库。

SQL> alter system set db_name='IVLUAT' SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

必须以resetlogs的方式打开数据库。

SQL> alter database open resetlogs;

Database altered.

修改数据库的service_names参数,重新注册监听。

SQL> alter system set service_names=IVLDB;

System altered.
SQL> alter system register;

System altered.

此时,应用程序已经可以访问UAT数据库,可以使用RMAN恢复另一套数据库了。

登录UAT数据库,查看数据文件存放目录,新的数据库数据文件不要和原有数据库的数据文件存放到一起。

SQL> select name from v$datafile;

NAME
---------------------------------------------
/data/u01/IVLDB/system01.dbf
/data/u01/IVLDB/sysaux01.dbf
/data/u01/IVLDB/undotbs01.dbf
/data/u01/IVLDB/users01.dbf
/data/u01/IVLDB/datafile/TB_TS_MC01.DBF
/data/u01/IVLDB/datafile/TB_TS_IV01.DBF
/data/u01/IVLDB/datafile/TB_TS_IV02.DBF
/data/u01/IVLDB/datafile/TB_TS_IV03.DBF
/data/u01/IVLDB/datafile/TB_TS_IV04.DBF
/data/u01/IVLDB/datafile/TB_TS_IV05.DBF
/data/u01/IVLDB/datafile/TB_TS_IV06.DBF

创建参数文件,修改后作为新数据库的参数文件。

SQL> create pfile from spfile;

File created.

修改参数文件,主要修改以下内容。

[oracle@SL010A-ISITDB3 dbs]$ vi initIVLDB.ora
*.memory_target=4294967296    --内存足够可以不用修改
*.control_files='/data/u01/uatdb/control01.ctl','/data/u01/uatdb/control02.ctl'

修改参数文件名字为inituatdb.ora,uatdb是新要恢复的数据库的SID。

[oracle@SL010A-ISITDB3 dbs]$ mv initIVLDB.ora inituatdb.ora

设置ORACLE_SID环境变量为uatdb,连接数据库,将数据库启动到nomount状态。

[oracle@SL010A-ISITDB3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 13 09:44:54 2015

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
SQL> show parameter spfile

NAME           TYPE        VALUE
-------------- ----------- ------------------------------
spfile         string      /opt/oracle/product/11g/dbs/spfileuatdb.ora
SQL> show parameter control

NAME               TYPE        VALUE
------------------ ----------- ------------------------------
control_files      string      /data/u01/uatdb/control01.ctl,/data/u01/uatdb/control02.ctl

创建数据文件存放目录,这里统一存放在/data/u01/uatdb目录下面。

[oracle@SL010A-ISITDB3 ivldb]$ cd /data/u01/
[oracle@SL010A-ISITDB3 u01]$ mkdir uatdb

将生产库的备份上传到UAT服务器,由于生产库做的是增量备份,所以需要将周日(6月7日的全备)及6月13日的备份拷贝到UAT服务器。传输过程略。

恢复控制文件。

[oracle@SL010A-ISITDB3 u01]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 13 09:48:08 2015

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

connected to target database: IVLDB (not mounted)

RMAN> restore controlfile from '/data/ivldb/con.bak';

Starting restore at 2015-06-13 09:51:26
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/u01/uatdb/control01.ctl
output file name=/data/u01/uatdb/control02.ctl
Finished restore at 2015-06-13 09:51:27

此时,数据库已经MOUNT状态,此时的数据库service_names参数和原有的UAT数据库的service_names参数冲突,修改为uatdb。

SQL> show parameter name

NAME                                 TYPE                      VALUE
------------------------------------ ----------------------    ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                    IVLDB
db_unique_name                       string                    IVLDB
global_names                         boolean                   FALSE
instance_name                        string                    uatdb
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                    IVLDB
SQL> alter system set service_names='uatdb';

System altered.

查询uatdb数据文件存放位置。

SQL> select name from v$datafile;

NAME
------------------------------------------
+DATA/ivldb/datafile/system.dbf
+DATA/ivldb/datafile/sysaux.dbf
+DATA/ivldb/datafile/ts_inx_ful03.dbf
+DATA/ivldb/datafile/ts_inx_ful04.dbf
+DATA/ivldb/datafile/user01.dbf
+DATA/ivldb/ivldb1_undo.dbf
+DATA/ivldb/ivldb2_undo.dbf
+DATA/ivldb/datafile/ts_bsc01.dbf
+DATA/ivldb/datafile/ts_inx_bsc01.dbf
+DATA/ivldb/datafile/ts_ord01.dbf
+DATA/ivldb/datafile/ts_inx_ord01.dbf
+DATA/ivldb/datafile/ts_ful01.dbf
+DATA/ivldb/datafile/ts_inx_ful01.dbf
+DATA/ivldb/datafile/ts_ful_if01.dbf
+DATA/ivldb/datafile/ts_inx_ful_if01.dbf
+DATA/ivldb/datafile/ts_tmp01.dbf
+DATA/ivldb/datafile/ts_task01.dbf
+DATA/ivldb/datafile/ts_mc01.dbf
+DATA/ivldb/datafile/ts_inx_mc01.dbf
+DATA/ivldb/datafile/ts_mig01.dbf
+DATA/ivldb/datafile/ts_bsc02.dbf
+DATA/ivldb/datafile/ts_inx_bsc02.dbf
+DATA/ivldb/datafile/ts_ful02.dbf
+DATA/ivldb/datafile/ts_ful03.dbf
+DATA/ivldb/datafile/ts_inx_ful02.dbf
+DATA/ivldb/datafile/ts_ord02.dbf
+DATA/ivldb/datafile/ts_inx_ord02.dbf
+DATA/ivldb/datafile/ts_ful04.dbf
+DATA/ivldb/datafile/ts_ful05.dbf
+DATA/ivldb/datafile/ts_ful06.dbf
+DATA/ivldb/datafile/ts_bsc03.dbf
+DATA/ivldb/datafile/ts_inx_ful05.dbf
+DATA/ivldb/datafile/ts_inx_ful06.dbf
+DATA/ivldb/datafile/ts_inx_ful07.dbf

34 rows selected.

由于生产库的数据文件存放在ASM里面,恢复数据库时需要转换数据文件存放位置。

run {
allocate channel d1 type disk; 
allocate channel d2 type disk; 
allocate channel d3 type disk; 
allocate channel d4 type disk; 
set newname for datafile '+DATA/ivldb/datafile/system.dbf' to '/data/u01/uatdb/system.dbf';
set newname for datafile '+DATA/ivldb/datafile/sysaux.dbf' to '/data/u01/uatdb/sysaux.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful03.dbf' to '/data/u01/uatdb/ts_inx_ful03.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful04.dbf' to '/data/u01/uatdb/ts_inx_ful04.dbf';
set newname for datafile '+DATA/ivldb/datafile/user01.dbf' to '/data/u01/uatdb/user01.dbf';
set newname for datafile '+DATA/ivldb/ivldb1_undo.dbf' to '/data/u01/uatdb/ivldb1_undo.dbf';
set newname for datafile '+DATA/ivldb/ivldb2_undo.dbf' to '/data/u01/uatdb/ivldb2_undo.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_bsc01.dbf' to '/data/u01/uatdb/ts_bsc01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_bsc01.dbf' to '/data/u01/uatdb/ts_inx_bsc01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ord01.dbf' to '/data/u01/uatdb/ts_ord01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ord01.dbf' to '/data/u01/uatdb/ts_inx_ord01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful01.dbf' to '/data/u01/uatdb/ts_ful01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful01.dbf' to '/data/u01/uatdb/ts_inx_ful01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful_if01.dbf' to '/data/u01/uatdb/ts_ful_if01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful_if01.dbf' to '/data/u01/uatdb/ts_inx_ful_if01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_tmp01.dbf' to '/data/u01/uatdb/ts_tmp01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_task01.dbf' to '/data/u01/uatdb/ts_task01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_mc01.dbf' to '/data/u01/uatdb/ts_mc01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_mc01.dbf' to '/data/u01/uatdb/ts_inx_mc01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_mig01.dbf' to '/data/u01/uatdb/ts_mig01.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_bsc02.dbf' to '/data/u01/uatdb/ts_bsc02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_bsc02.dbf' to '/data/u01/uatdb/ts_inx_bsc02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful02.dbf' to '/data/u01/uatdb/ts_ful02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful03.dbf' to '/data/u01/uatdb/ts_ful03.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful02.dbf' to '/data/u01/uatdb/ts_inx_ful02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ord02.dbf' to '/data/u01/uatdb/ts_ord02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ord02.dbf' to '/data/u01/uatdb/ts_inx_ord02.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful04.dbf' to '/data/u01/uatdb/ts_ful04.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful05.dbf' to '/data/u01/uatdb/ts_ful05.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_ful06.dbf' to '/data/u01/uatdb/ts_ful06.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_bsc03.dbf' to '/data/u01/uatdb/ts_bsc03.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful05.dbf' to '/data/u01/uatdb/ts_inx_ful05.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful06.dbf' to '/data/u01/uatdb/ts_inx_ful06.dbf';
set newname for datafile '+DATA/ivldb/datafile/ts_inx_ful07.dbf' to '/data/u01/uatdb/ts_inx_ful07.dbf';
restore database force;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

查看redo日志的存放路径。

SQL> select member from v$logfile;

MEMBER
--------------------------------
+REDOLOG1/ivldb/redo01.log
+REDOLOG2/ivldb/redo02.log
+REDOLOG1/ivldb/redo03.log
+REDOLOG2/ivldb/redo04.log
+REDOLOG1/ivldb/redo05.log
+REDOLOG2/ivldb/redo06.log
+REDOLOG1/ivldb/redo19.log
+REDOLOG2/ivldb/redo20.log
+REDOLOG1/ivldb/redo07.log
+REDOLOG2/ivldb/redo08.log
+REDOLOG1/ivldb/redo09.log
+REDOLOG2/ivldb/redo010.log
+REDOLOG1/ivldb/redo011.log
+REDOLOG2/ivldb/redo012.log
+REDOLOG1/ivldb/redo013.log
+REDOLOG2/ivldb/redo014.log
+REDOLOG1/ivldb/redo015.log
+REDOLOG2/ivldb/redo016.log
+REDOLOG1/ivldb/redo017.log
+REDOLOG2/ivldb/redo018.log
+REDOLOG1/ivldb/st_redo01.log
+REDOLOG1/ivldb/st_redo02.log
+REDOLOG1/ivldb/st_redo03.log
+REDOLOG2/ivldb/st_redo04.log
+REDOLOG2/ivldb/st_redo05.log
+REDOLOG2/ivldb/st_redo06.log

26 rows selected.

修改redo日志的存放路径。

SQL> alter database rename file '+REDOLOG1/ivldb/redo01.log' to '/data/u01/uatdb/redo01.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo02.log' to '/data/u01/uatdb/redo02.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo03.log' to '/data/u01/uatdb/redo03.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo04.log' to '/data/u01/uatdb/redo04.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo05.log' to '/data/u01/uatdb/redo05.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo06.log' to '/data/u01/uatdb/redo06.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo19.log' to '/data/u01/uatdb/redo19.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo20.log' to '/data/u01/uatdb/redo20.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo07.log' to '/data/u01/uatdb/redo07.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo08.log' to '/data/u01/uatdb/redo08.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo09.log' to '/data/u01/uatdb/redo09.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo010.log' to '/data/u01/uatdb/redo10.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo011.log' to '/data/u01/uatdb/redo11.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo012.log' to '/data/u01/uatdb/redo12.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo013.log' to '/data/u01/uatdb/redo13.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo014.log' to '/data/u01/uatdb/redo14.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo015.log' to '/data/u01/uatdb/redo15.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo016.log' to '/data/u01/uatdb/redo16.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/redo017.log' to '/data/u01/uatdb/redo17.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/redo018.log' to '/data/u01/uatdb/redo18.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/st_redo01.log' to '/data/u01/uatdb/st_redo01.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/st_redo02.log' to '/data/u01/uatdb/st_redo02.log';

Database altered.
SQL> alter database rename file '+REDOLOG1/ivldb/st_redo03.log' to '/data/u01/uatdb/st_redo03.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/st_redo04.log' to '/data/u01/uatdb/st_redo04.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/st_redo05.log' to '/data/u01/uatdb/st_redo05.log';

Database altered.
SQL> alter database rename file '+REDOLOG2/ivldb/st_redo06.log' to '/data/u01/uatdb/st_redo06.log';

Database altered.

安装开发人员需求,尝试基于时间点的不完全恢复。

RMAN> run{
2> allocate channel t1 device type disk;
3> allocate channel t2 device type disk;
4> allocate channel t3 device type disk;
5> allocate channel t4 device type disk;
6> set until time "to_date('2015-06-12 00:00:00','yyyy-mm-dd hh24:mi:ss')";
7> recover database;
8> release channel t1;
9> release channel t2;
10> release channel t3;
11> release channel t4;
12> }

此时,告警日志会抛出一堆的错误,但是不要紧,恢复已经进行,RMAN并不会中断。

Sat Jun 13 11:35:19 2015
Errors in file /opt/oracle/diag/rdbms/ivldb/uatdb/trace/uatdb_ora_12684.trc:
ORA-19625: error identifying file +DATA/ivldb/datafile/system.dbf
ORA-17503: ksfdopn:2 Failed to open file +DATA/ivldb/datafile/system.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Switch of datafile 1 complete to datafile copy 
  checkpoint is 109650939467
Errors in file /opt/oracle/diag/rdbms/ivldb/uatdb/trace/uatdb_ora_12684.trc:
ORA-19625: error identifying file +DATA/ivldb/datafile/sysaux.dbf
ORA-17503: ksfdopn:2 Failed to open file +DATA/ivldb/datafile/sysaux.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Switch of datafile 2 complete to datafile copy 
  checkpoint is 109650939408
… …
Sat Jun 13 11:35:28 2015
Signalling error 1152 for datafile 16!
Signalling error 1152 for datafile 17!
Signalling error 1152 for datafile 18!
Signalling error 1152 for datafile 19!
Signalling error 1152 for datafile 20!
Signalling error 1152 for datafile 22!
Signalling error 1152 for datafile 23!
Signalling error 1152 for datafile 26!
Signalling error 1152 for datafile 30!
Signalling error 1152 for datafile 31!
Checker run found 19 new persistent data failures

这个错误是由于恢复的时候没有 和restore放到一起导致的,因为restore的时候,制定了set newname转换了数据文件,这个错误不用理会,因为马上就会在告警日志看到以下可爱的信息。

Sat Jun 13 12:09:09 2015
Incremental restore complete of datafile 10 /data/u01/uatdb/ts_ord01.dbf
  checkpoint is 109751515007
  last deallocation scn is 109640847186
Incremental restore complete of datafile 15 /data/u01/uatdb/ts_inx_ful_if01.dbf
  checkpoint is 109751515007
  last deallocation scn is 3
Sat Jun 13 12:27:57 2015
Incremental restore complete of datafile 3 /data/u01/uatdb/ts_inx_ful03.dbf
  checkpoint is 109751515007
  last deallocation scn is 3
Incremental restore complete of datafile 4 /data/u01/uatdb/ts_inx_ful04.dbf
  checkpoint is 109751515007
  last deallocation scn is 3
Incremental restore complete of datafile 12 /data/u01/uatdb/ts_ful01.dbf
  checkpoint is 109751515007
  last deallocation scn is 109644356022
Incremental restore complete of datafile 28 /data/u01/uatdb/ts_ful04.dbf
  checkpoint is 109751515007
  last deallocation scn is 109644356017

恢复完成后,打开数据库遇到ORA-03113错误,这个错误通常是数据文件损坏或不存在导致。

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25534
Session ID: 421 Serial number: 59

登录数据库查看下resetlog是否成功。

[oracle@SL010A-ISITDB3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 15 10:37:41 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
Database mounted.

SQL> archive log list   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

UAT数据库不需要打开归档,关闭归档。

SQL> alter database noarchivelog;

Database altered.

再次尝试打开数据库,遇到ORA-30012错误。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 28079
Session ID: 421 Serial number: 3

这个报错信息就很明显了,因为生产库是RAC环境,UAT是单实例环境,而打开这个UAT数据库的参数文件是从单实例的数据库拷贝过来的,当时修改参数的时候并没有更改undo_tablespace参数。

SQL> alter system set undo_tablespace='IVLDB1_UNDO' scope=spfile;

System altered.

SQL> alter database open;

Database altered.

修改undo_tablespace参数后,数据库可以启动,此时,恢复工作并没有完成,还需要增加临时文件。

SQL> select NAME, FILE#,TS#,STATUS from v$tempfile;

NAME                                                    FILE#        TS# STATUS
-------------------------------------------------- ---------- ---------- --------------
+DATA/ivldb/tempfile/temp01.dbf                             1          3 ONLINE
+DATA/ivldb/ivalue_temp1.dbf                                2          9 ONLINE
+DATA/ivldb/ivalue_temp2.dbf                                3         10 ONLINE
+DATA/ivldb/ivalue_temp3.dbf                                4         11 ONLINE
SQL> select ts#,name from v$tablespace where ts# in(3,9,10,11);

       TS# NAME
---------- --------------------------------------------------
         3 TEMP
         9 IVALUE_TEMP1
        10 IVALUE_TEMP2
        11 IVALUE_TEMP3
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/u01/uatdb/temp01.dbf' SIZE 1g AUTOEXTEND ON;

Tablespace altered.

SQL> ALTER TABLESPACE IVALUE_TEMP1 ADD TEMPFILE '/data/u01/uatdb/ivalue_temp1.dbf' SIZE 1g AUTOEXTEND ON;

Tablespace altered.

SQL>  ALTER TABLESPACE IVALUE_TEMP2 ADD TEMPFILE '/data/u01/uatdb/ivalue_temp2.dbf' SIZE 1g AUTOEXTEND ON;

Tablespace altered.

SQL> ALTER TABLESPACE IVALUE_TEMP3 ADD TEMPFILE '/data/u01/uatdb/ivalue_temp3.dbf' SIZE 1g AUTOEXTEND ON;

Tablespace altered.

SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/ivldb/tempfile/temp01.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE IVALUE_TEMP1 DROP TEMPFILE '+DATA/ivldb/ivalue_temp1.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE IVALUE_TEMP2 DROP TEMPFILE '+DATA/ivldb/ivalue_temp2.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE IVALUE_TEMP3 DROP TEMPFILE '+DATA/ivldb/ivalue_temp3.dbf';

Tablespace altered.

SQL> SELECT NAME FROM V$TEMPFILE;

NAME
--------------------------------------------------
/data/u01/uatdb/temp01.dbf
/data/u01/uatdb/ivalue_temp1.dbf
/data/u01/uatdb/ivalue_temp2.dbf
/data/u01/uatdb/ivalue_temp3.dbf

至此,恢复完成,UAT数据库可正常提供服务。

总结:如果要在一台服务器上恢复一套DB NAME相同的数据库,而且这两个数据库需要同时对外提供访问,那么需要将一套数据库的DB NAME改掉。

本文固定链接: http://www.dbdream.com.cn/2015/06/%e4%bd%bf%e7%94%a8rman%e5%a4%87%e4%bb%bd%e5%9c%a8%e4%b8%80%e5%8f%b0%e6%9c%ba%e5%99%a8%e4%b8%8a%e6%81%a2%e5%a4%8d%e5%b7%b2%e6%9c%89db-name%e6%95%b0%e6%8d%ae%e5%ba%93/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2015年06月24日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 使用RMAN备份在一台机器上恢复已有DB NAME数据库 | 信春哥,系统稳,闭眼上线不回滚!
关键字:

使用RMAN备份在一台机器上恢复已有DB NAME数据库:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter