从RAC数据库恢复到单实例的数据库,需要DBA做的工作还是有一些的---我是指除了常规恢复操作之外。
环境如下
服务端:192.168.75.11(12)
目标端:192.168.75.130,机器名standby
目标端已安装好数据库软件,并升级至与源端相同的版本,参数配置合理并且磁盘空间充足,源端创建的备份集也已复制至目标端。
别的就不废话了,接下来小跑进入实战演练部分吧,首先检查目标端设置环境变量:
rac环境:http://download.csdn.net/detail/dcwnb1/8576809
rman备份:http://blog.csdn.net/dcwnb1/article/details/44957893
[oracle@standby ~]$ env|grep ORA
ORACLE_SID=standby
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
建立目录:
把备份拷贝到相应位置
[oracle@standby ~]$ mkdir /u01/
app/ backup/
[oracle@standby ~]$ mkdir /u01/backup/
[oracle@standby ~]$ ls /u01/app/oracle/admin/racdb/
adump bdump cdump udump
[oracle@standby ~]$ du -sh /u01/backup/
755M /u01/backup/
进入RMAN命令行模式,并启动到NOMOUNT状态:
[oracle@standby ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 25 02:13:56 2015
Copyright (c) 1982, 2007, Oracle. 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/10.2.0/db_1/db
s/initstandby.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes
RMAN> host ls /u01/app/oracle/product/10.2.0/db_1/dbs/
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "double-quoted-s
tring, ;, single-quoted-string"
RMAN-01008: the bad identifier was: ls
RMAN-01007: at line 1 column 6 file: standard input
这里大家可以看到初始化参数文件,也能启动到NOMOUNT呢?
启动过程中有错误,先忽略,这里startup的目的只是为了给ORACLE分配相应的内存区,以便让他能够执行下面的restore操作
从备份集中恢复spfile并保存成pfile,操作如下:
RMAN> restore spfile to pfile "/tmp/initracdb.ora" from "/u01/backup/03q2nhqe_1_1";
Starting restore at 25-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/backup/03q2nhqe_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 25-MAR-15
备份一下:
cp /tmp/initracdb.ora /tmp/initracdb.old
接下来要做的是个体力活,修改pfile.ora中的初始化参数,主要有两方面的修改:
修改含文件路径的参数,达到符合当前服务器环境的实际情况
修改多实例相关的参数
原文件内容如下:
racdb1.__db_cache_size=176160768
racdb2.__db_cache_size=155189248
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__shared_pool_size=92274688
racdb2.__shared_pool_size=113246208
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*.audit_file_dest='/u01/admin/racdb/adump'
*.background_dump_dest='/u01/admin/racdb/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='+DATA/racdb/controlfile/current.256.862924743','+DATA/racdb/controlfile/current.257.862924745'
*.core_dump_dest='/u01/admin/racdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
racdb1.instance_number=1
racdb2.instance_number=2
*.job_queue_processes=10
racdb1.log_archive_dest_1='location=/u01/oradata/racdb1/archivelog'
racdb2.log_archive_dest_1='location=/u01/oradata/racdb2/archivelog'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_listener='LISTENERS_RACDB'
*.remote_login_passwordfile='exclusive'
*.sga_target=283115520
racdb2.thread=2
racdb1.thread=1
*.undo_management='AUTO'
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/racdb/udump'
[oracle@standby tmp]$ vim initracdb.ora
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.background_dump_dest='/u01/app/oracle/admin/racdb/bdump'
*.compatible='10.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/racdb/control01.dbf','/u01/app/oracle/oradata/racdb/control02.dbf','/u01/app/oracle/oradata/racdb/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.job_queue_processes=10
log_archive_dest_1='location=/u01/app/oracle/oradata/racdb/archivelog'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/racdb/udump'
然后进入sqlplus命令行环境,通过编辑好的pfile创建spfile,并重启oracle到nomount状态:
[oracle@standby tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 25 03:14:24 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/tmp/initracdb.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL>
rac中查看dbid:
SQL> select dbid from v$database;
DBID
----------
860541502
[oracle@standby tmp]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 25 03:16:49 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: racdb (not mounted)
RMAN> set dbid=860541502
executing command: SET DBID
接下来要恢复控制文件了,再次进入RMAN命令行,注意操作前别忘了设置dbid(如何获取dbid就不说了吧),执行操作如下:
RMAN> restore controlfile from '/u01/backup/03q2nhqe_1_1';
Starting restore at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/racdb/control01.dbf
output filename=/u01/app/oracle/oradata/racdb/control02.dbf
output filename=/u01/app/oracle/oradata/racdb/control03.ctl
Finished restore at 25-MAR-15
控制文件成功恢复到指定路径下。
下面就可以进入到mount状态了:
RMAN> alter database mount;
using target database control file instead of recovery catalog
database mounted
rac:
在rac上查看文件:
SQL> set linesize 150
SQL> set page size 150
SQL> col name for a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 +DATA/racdb/datafile/system.262.862924783
2 +DATA/racdb/datafile/undotbs1.263.862924831
3 +DATA/racdb/datafile/sysaux.264.862924853
4 +DATA/racdb/datafile/undotbs2.266.862924893
5 +DATA/racdb/datafile/users.267.862924915
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 +DATA/racdb/tempfile/temp.265.862924867
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/racdb/onlinelog/group_1.258.862924751
+DATA/racdb/onlinelog/group_1.259.862924759
+DATA/racdb/onlinelog/group_2.260.862924765
+DATA/racdb/onlinelog/group_2.261.862924773
+DATA/racdb/onlinelog/group_3.268.862928611
+DATA/racdb/onlinelog/group_3.269.862928621
+DATA/racdb/onlinelog/group_4.270.862928629
+DATA/racdb/onlinelog/group_4.271.862928639
单实例:
注册表库备份后生成的归档文件备份集:
RMAN> catalog start with '/u01/backup';
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/03q2nhqe_1_1
File Name: /u01/backup/04q2nhqu_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/03q2nhqe_1_1
File Name: /u01/backup/04q2nhqu_1_1
RMAN> catalog start with '/u01/backup';
searching for all files that match the pattern /u01/backup
no files found to be unknown to the database
当个文件也可以:catalog backuppiece '/u01/backup/05msf0el_1_1';
RMAN 中的SET命令可以用来为数据文件和临时文件重命名,这里我们就使用set命令对数据文件和临时文件的路径进行重定义,然后再执行恢复操作,如下:
RMAN> RUN
{
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/racdb/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/racdb/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/racdb/sysaux01.dbf';
RESTORE DATABASE;
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/racdb/undotbs02.dbf';
SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/racdb/users01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
11> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/racdb/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/racdb/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/racdb/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/racdb/undotbs02.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/racdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/02q2nhhf_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/02q2nhhf_1_1 tag=TAG20150325T142654
channel ORA_DISK_1: restore complete, elapsed time: 00:03:45
Finished restore at 25-MAR-15
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/undotbs02.dbf
datafile 5 switched to datafile copy
input datafile copy recid=10 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/users01.dbf
对数据库执行recover,由于我们只复制了备份集,而没有复制源库中新归档以及online redo文件,这里只能进行不完全的恢复(创建的备份并非一致性冷备份)
也就是说,报错是必然的,不过没关系,只要能修复到一致性状态就好:
RMAN> recover database;
Starting recover at 25-MAR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=50
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=8
channel ORA_DISK_1: reading from backup piece /u01/backup/04q2nhqu_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/04q2nhqu_1_1 tag=TAG20150325T143158
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/oradata/racdb/archivelog/1_50_862924734.dbf thread=1 sequence=50
archive log filename=/u01/app/oracle/oradata/racdb/archivelog/2_8_862924734.dbf thread=2 sequence=8
unable to find archive log
archive log thread=2 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2015 03:53:35
RMAN-06054: media recovery requesting unknown log: thread 2 seq 9 lowscn 688896
接下来,我们不得不重建控制文件,以修复重做日志文件的路径:
SQL> alter database backup controlfile to trace as '/tmp/cf01.ora';
Database altered.
你也许在想,可以通过"alter database rename file"方式修改重做日志文件路径的啊,事实上"alter database rename file"方式极有可能触发" ORA-00600: internal error code,
arguments: [kgeade_is_0], [], [], [], [], [], [], [] " 错误
经查这是ORACLE的一个BUG,对应BUG为7207932,通常是RAC环境从asm向文件系统迁移时被触发,在10204版本中依然存在,Doc ID: 742289.1对此有详细说明,号称11g版本中对该
问题进行了修复。
CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/racdb/redo01.dbf' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/racdb/redo02.dbf' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/racdb/system01.dbf',
'/u01/app/oracle/oradata/racdb/undotbs01.dbf',
'/u01/app/oracle/oradata/racdb/sysaux01.dbf',
'/u01/app/oracle/oradata/racdb/undotbs02.dbf',
'/u01/app/oracle/oradata/racdb/users01.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled 这个错误查看下面的文档
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 '/u01/app/oracle/oradata/racdb/redo03.dbf' SIZE 50M,
GROUP 4 '/u01/app/oracle/oradata/racdb/redo04.dbf' SIZE 50M;
Database altered.
搞定,下面就可以打开数据库了:
SQL> alter database open resetlogs;
Database altered.
接下来别忘了重建临时表空间的数据文件:
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/racdb/temp01.dbf' size 50M;
Tablespace altered.
清除未使用线程的redo日志组,操作如下:
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 NO CURRENT
2 1 YES UNUSED
3 2 NO CURRENT
4 2 YES UNUSED
SQL> alter database disable thread 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
清除多余的undo文件。
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
rac恢复到单实例的实验介绍完了。
环境如下
服务端:192.168.75.11(12)
目标端:192.168.75.130,机器名standby
目标端已安装好数据库软件,并升级至与源端相同的版本,参数配置合理并且磁盘空间充足,源端创建的备份集也已复制至目标端。
别的就不废话了,接下来小跑进入实战演练部分吧,首先检查目标端设置环境变量:
rac环境:http://download.csdn.net/detail/dcwnb1/8576809
rman备份:http://blog.csdn.net/dcwnb1/article/details/44957893
[oracle@standby ~]$ env|grep ORA
ORACLE_SID=standby
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
建立目录:
把备份拷贝到相应位置
[oracle@standby ~]$ mkdir /u01/
app/ backup/
[oracle@standby ~]$ mkdir /u01/backup/
[oracle@standby ~]$ ls /u01/app/oracle/admin/racdb/
adump bdump cdump udump
[oracle@standby ~]$ du -sh /u01/backup/
755M /u01/backup/
进入RMAN命令行模式,并启动到NOMOUNT状态:
[oracle@standby ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 25 02:13:56 2015
Copyright (c) 1982, 2007, Oracle. 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/10.2.0/db_1/db
s/initstandby.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes
RMAN> host ls /u01/app/oracle/product/10.2.0/db_1/dbs/
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "double-quoted-s
tring, ;, single-quoted-string"
RMAN-01008: the bad identifier was: ls
RMAN-01007: at line 1 column 6 file: standard input
这里大家可以看到初始化参数文件,也能启动到NOMOUNT呢?
启动过程中有错误,先忽略,这里startup的目的只是为了给ORACLE分配相应的内存区,以便让他能够执行下面的restore操作
从备份集中恢复spfile并保存成pfile,操作如下:
RMAN> restore spfile to pfile "/tmp/initracdb.ora" from "/u01/backup/03q2nhqe_1_1";
Starting restore at 25-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/backup/03q2nhqe_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 25-MAR-15
备份一下:
cp /tmp/initracdb.ora /tmp/initracdb.old
接下来要做的是个体力活,修改pfile.ora中的初始化参数,主要有两方面的修改:
修改含文件路径的参数,达到符合当前服务器环境的实际情况
修改多实例相关的参数
原文件内容如下:
racdb1.__db_cache_size=176160768
racdb2.__db_cache_size=155189248
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__shared_pool_size=92274688
racdb2.__shared_pool_size=113246208
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*.audit_file_dest='/u01/admin/racdb/adump'
*.background_dump_dest='/u01/admin/racdb/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='+DATA/racdb/controlfile/current.256.862924743','+DATA/racdb/controlfile/current.257.862924745'
*.core_dump_dest='/u01/admin/racdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain'
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
racdb1.instance_number=1
racdb2.instance_number=2
*.job_queue_processes=10
racdb1.log_archive_dest_1='location=/u01/oradata/racdb1/archivelog'
racdb2.log_archive_dest_1='location=/u01/oradata/racdb2/archivelog'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_listener='LISTENERS_RACDB'
*.remote_login_passwordfile='exclusive'
*.sga_target=283115520
racdb2.thread=2
racdb1.thread=1
*.undo_management='AUTO'
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/racdb/udump'
[oracle@standby tmp]$ vim initracdb.ora
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.background_dump_dest='/u01/app/oracle/admin/racdb/bdump'
*.compatible='10.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/racdb/control01.dbf','/u01/app/oracle/oradata/racdb/control02.dbf','/u01/app/oracle/oradata/racdb/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.job_queue_processes=10
log_archive_dest_1='location=/u01/app/oracle/oradata/racdb/archivelog'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/racdb/udump'
然后进入sqlplus命令行环境,通过编辑好的pfile创建spfile,并重启oracle到nomount状态:
[oracle@standby tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 25 03:14:24 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/tmp/initracdb.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL>
rac中查看dbid:
SQL> select dbid from v$database;
DBID
----------
860541502
[oracle@standby tmp]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 25 03:16:49 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: racdb (not mounted)
RMAN> set dbid=860541502
executing command: SET DBID
接下来要恢复控制文件了,再次进入RMAN命令行,注意操作前别忘了设置dbid(如何获取dbid就不说了吧),执行操作如下:
RMAN> restore controlfile from '/u01/backup/03q2nhqe_1_1';
Starting restore at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/racdb/control01.dbf
output filename=/u01/app/oracle/oradata/racdb/control02.dbf
output filename=/u01/app/oracle/oradata/racdb/control03.ctl
Finished restore at 25-MAR-15
控制文件成功恢复到指定路径下。
下面就可以进入到mount状态了:
RMAN> alter database mount;
using target database control file instead of recovery catalog
database mounted
rac:
在rac上查看文件:
SQL> set linesize 150
SQL> set page size 150
SQL> col name for a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 +DATA/racdb/datafile/system.262.862924783
2 +DATA/racdb/datafile/undotbs1.263.862924831
3 +DATA/racdb/datafile/sysaux.264.862924853
4 +DATA/racdb/datafile/undotbs2.266.862924893
5 +DATA/racdb/datafile/users.267.862924915
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 +DATA/racdb/tempfile/temp.265.862924867
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/racdb/onlinelog/group_1.258.862924751
+DATA/racdb/onlinelog/group_1.259.862924759
+DATA/racdb/onlinelog/group_2.260.862924765
+DATA/racdb/onlinelog/group_2.261.862924773
+DATA/racdb/onlinelog/group_3.268.862928611
+DATA/racdb/onlinelog/group_3.269.862928621
+DATA/racdb/onlinelog/group_4.270.862928629
+DATA/racdb/onlinelog/group_4.271.862928639
单实例:
注册表库备份后生成的归档文件备份集:
RMAN> catalog start with '/u01/backup';
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/03q2nhqe_1_1
File Name: /u01/backup/04q2nhqu_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/03q2nhqe_1_1
File Name: /u01/backup/04q2nhqu_1_1
RMAN> catalog start with '/u01/backup';
searching for all files that match the pattern /u01/backup
no files found to be unknown to the database
当个文件也可以:catalog backuppiece '/u01/backup/05msf0el_1_1';
RMAN 中的SET命令可以用来为数据文件和临时文件重命名,这里我们就使用set命令对数据文件和临时文件的路径进行重定义,然后再执行恢复操作,如下:
RMAN> RUN
{
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/racdb/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/racdb/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/racdb/sysaux01.dbf';
RESTORE DATABASE;
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/racdb/undotbs02.dbf';
SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/racdb/users01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
11> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/racdb/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/racdb/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/racdb/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/racdb/undotbs02.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/racdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/02q2nhhf_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/02q2nhhf_1_1 tag=TAG20150325T142654
channel ORA_DISK_1: restore complete, elapsed time: 00:03:45
Finished restore at 25-MAR-15
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/undotbs02.dbf
datafile 5 switched to datafile copy
input datafile copy recid=10 stamp=875245955 filename=/u01/app/oracle/oradata/racdb/users01.dbf
对数据库执行recover,由于我们只复制了备份集,而没有复制源库中新归档以及online redo文件,这里只能进行不完全的恢复(创建的备份并非一致性冷备份)
也就是说,报错是必然的,不过没关系,只要能修复到一致性状态就好:
RMAN> recover database;
Starting recover at 25-MAR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=50
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=8
channel ORA_DISK_1: reading from backup piece /u01/backup/04q2nhqu_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/04q2nhqu_1_1 tag=TAG20150325T143158
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/oradata/racdb/archivelog/1_50_862924734.dbf thread=1 sequence=50
archive log filename=/u01/app/oracle/oradata/racdb/archivelog/2_8_862924734.dbf thread=2 sequence=8
unable to find archive log
archive log thread=2 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2015 03:53:35
RMAN-06054: media recovery requesting unknown log: thread 2 seq 9 lowscn 688896
接下来,我们不得不重建控制文件,以修复重做日志文件的路径:
SQL> alter database backup controlfile to trace as '/tmp/cf01.ora';
Database altered.
你也许在想,可以通过"alter database rename file"方式修改重做日志文件路径的啊,事实上"alter database rename file"方式极有可能触发" ORA-00600: internal error code,
arguments: [kgeade_is_0], [], [], [], [], [], [], [] " 错误
经查这是ORACLE的一个BUG,对应BUG为7207932,通常是RAC环境从asm向文件系统迁移时被触发,在10204版本中依然存在,Doc ID: 742289.1对此有详细说明,号称11g版本中对该
问题进行了修复。
CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/racdb/redo01.dbf' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/racdb/redo02.dbf' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/racdb/system01.dbf',
'/u01/app/oracle/oradata/racdb/undotbs01.dbf',
'/u01/app/oracle/oradata/racdb/sysaux01.dbf',
'/u01/app/oracle/oradata/racdb/undotbs02.dbf',
'/u01/app/oracle/oradata/racdb/users01.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled 这个错误查看下面的文档
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 '/u01/app/oracle/oradata/racdb/redo03.dbf' SIZE 50M,
GROUP 4 '/u01/app/oracle/oradata/racdb/redo04.dbf' SIZE 50M;
Database altered.
搞定,下面就可以打开数据库了:
SQL> alter database open resetlogs;
Database altered.
接下来别忘了重建临时表空间的数据文件:
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/racdb/temp01.dbf' size 50M;
Tablespace altered.
清除未使用线程的redo日志组,操作如下:
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 NO CURRENT
2 1 YES UNUSED
3 2 NO CURRENT
4 2 YES UNUSED
SQL> alter database disable thread 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
清除多余的undo文件。
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
rac恢复到单实例的实验介绍完了。