11G RAC 异机恢复至单实例测试

最新推荐文章于 2024-07-19 00:12:38 发布

cssmya086682

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量108

点赞数

文章标签：数据库

1 背景描述：
将RAC 异机恢复至单实例，以检验数据的可用性。

1.1 RACDB数据库基本配置信息
主机节点1: scdb1 操作系统：AIX6100
节点1: scdb2 操作系统：AIX6100
数据库版本 Oracle11.2.0.1 RAC
OS用户名/组 uid=501(oracle) gid=500(oinstall) groups=502(dba),503(oper),504(asmdba)
数据库名 RACDB
实例名 RACDB1
RACDB2
数据库字符集 ZHS16GBK
数据文件存储方式 ASM

1.2 恢复目标机的情况：
主机 csdb 操作系统：AIX6100
数据库版本 Oracle11.2.0.1 单机
OS用户名/组 uid=501(oracle) gid=500(oinstall) groups=502(dba),503(oper),504(asmdba)
数据库名 RACDB
实例名 RACDB
数据库字符集 ZHS16GBK
数据文件存储方式 ASM

2 恢复的相关信息：
2.1 确认备份的脚本是否正确备份
[scdb1:root]more bck_all

connect target sys/******@RACDB
connect catalog rman_RACDB/******@rman;
run
{
allocate channel t1 type 'sbt_tape'
parms 'ENV=(NSR_SERVER=xxxx)';
backup full database
filesperset 4
format 'full_%d_%U'
(database include current controlfile);
sql 'alter system archive log current';
backup archivelog all
format 'arch_%t_%s_%p'
skip inaccessible
delete input;
release channel t1;
}

3 恢复的详细步骤
开始恢复：

3.1 在rac库上创建pfile
SQL> create pfile='/tmp/initRACDB.ora' from spfile;
将pfile通过FTP传传到csdb上

3.2 在目标机上修改initRACDB.ora ，修改成如下：
RACDB1.__db_cache_size=754974720
RACDB1.__java_pool_size=33554432
RACDB1.__large_pool_size=16777216
RACDB1.__oracle_base='/software/oracle'#ORACLE_BASE set from environment
RACDB1.__pga_aggregate_target=3942645760
RACDB1.__sga_target=2936012800
RACDB1.__shared_io_pool_size=0
RACDB1.__shared_pool_size=2046820352
RACDB1.__streams_pool_size=33554432
*.audit_file_dest='/software/oracle/admin/RACDB/adump'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+RACDBDG/RACDB/controlfile/current.256.716919673'
#*.control_files='/software/backup/controlfil01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+RACDBDG'
*.db_domain=''
*.db_files=2048
*.db_name='RACDB'
*.db_recovery_file_dest='+RACDBDG'
*.db_recovery_file_dest_size=85899345920
*.diagnostic_dest='/software/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'
RACDB1.instance_number=1
RACDB1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.105)(PORT=1521))))'
*.memory_target=6871318528
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=300
*.processes=1500
#*.remote_listener='RACDB-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1655
RACDB1.thread=1
RACDB1.undo_tablespace='UNDOTBS1'’

3.3 在目标机建立相应目录
在csdb上的ASM，配置好RACDBDG，并在上面建立相应的目录，然后在/software/oracle/admin/RACDB/ 下也建立bdump等相应目录。用orapwd产生pwd密码文件。
建diskgroup
Grid> sqlplus / as sysasm
Create diskgroup RACDBDG external redundancy disk ‘/dev/ /dev/rhdiskpower1’,’/dev/ rhdiskpower12’;

Exit
Asmcmd
Cd +RACDBDG
Cd RACDB
Mkdir controlfile
Mkdir datafile
Mkdir onlinelog

3.4 在目标机上用RMAN恢复
[csdb:oracle]rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: RACDB (not mounted)
RMAN> connect catalog rman_RACDB/rman_RACDB@rman
connected to recovery catalog database
RMAN> run {
2> allocate channel t1 type 'sbt_tape'
3> parms 'ENV=(NSR_SERVER=xxxx,NSR_CLIENT=xxx)';
4> restore controlfile;
5> }

allocated channel: t1
channel t1: SID=574 device type=SBT_TAPE
channel t1: NMO v5.0.0.0
channel t1: starting datafile backup set restore
channel t1: restoring control file
channel t1: reading from backup piece mklnrbhi_1_1
channel t1: piece handle=mklnrbhi_1_1 tag=TAG20100914T013937
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:46
output file name=+RACDBDG/RACDB/controlfile/current.256.729681003
Finished restore at
released channel: t1

RMAN> run {
2> allocate channel t1 type 'sbt_tape'
3> parms 'ENV=(NSR_SERVER=xxxx)';
4> sql 'alter database mount';
5> set until sequence 659 thread 1;
6> set newname for datafile 10 to '+RACDBDG/RACDB/datafile/ts_xx1';
7> set newname for datafile 13 to '+RACDBDG/RACDB/datafile/ts_xx2';
8> set newname for datafile 15 to '+RACDBDG/RACDB/datafile/ts_xx3';
9> set newname for datafile 22 to '+RACDBDG/RACDB/datafile/ts_xx4';
10> set newname for datafile 14 to '+RACDBDG/RACDB/datafile/ts_ixx5';
11> set newname for datafile 17 to '+RACDBDG/RACDB/datafile/ts_xx6';
12> set newname for datafile 23 to '+RACDBDG/RACDB/datafile/ts_xx7;
13> set newname for datafile 24 to '+RACDBDG/RACDB/datafile/ts_xx8';
14> set newname for datafile 6 to '+RACDBDG/RACDB/datafile/ts_xx9';
15> set newname for datafile 8 to '+RACDBDG/RACDB/datafile/ts_xx10';
16> set newname for datafile 16 to '+RACDBDG/RACDB/datafile/ts_xx11';
17> set newname for datafile 19 to '+RACDBDG/RACDB/datafile/ts_xx12';
18> set newname for datafile 7 to '+RACDBDG/RACDB/datafile/ts_xx13';
19> set newname for datafile 9 to '+RACDBDG/RACDB/datafile/ts_xx14';
20> set newname for datafile 18 to '+RACDBDG/RACDB/datafile/ts_xx15';
21> set newname for datafile 20 to '+RACDBDG/RACDB/datafile/ts_xx16';
22> set newname for datafile 3 to '+RACDBDG/RACDB/datafile/undotbs1';
23> set newname for datafile 5 to '+RACDBDG/RACDB/datafile/users';
24> set newname for datafile 11 to '+RACDBDG/RACDB/datafile/ts_xx18';
25> set newname for datafile 21 to '+RACDBDG/RACDB/datafile/ts_xx19';
26> set newname for datafile 1 to '+RACDBDG/RACDB/datafile/system';
27> set newname for datafile 2 to '+RACDBDG/RACDB/datafile/sysaux ;
28> set newname for datafile 4 to '+RACDBDG/RACDB/datafile/undotbs2';
29> set newname for datafile 12 to '+RACDBDG/RACDB/datafile/ts_xx20';
30> restore database;
31> switch datafile all;
32> }

allocated channel: t1
channel t1: SID=574 device type=SBT_TAPE
channel t1: NMO v5.0.0.0

sql statement: alter database mount
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at
Starting implicit crosscheck backup at
Finished implicit crosscheck backup at

Starting implicit crosscheck copy at
Finished implicit crosscheck copy at

searching for all files in the recovery area
cataloging files...
no files cataloged
中间过程略
.....
released channel: t1

3.5 恢复归档到指定的目录：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 654
Next log sequence to archive 658
Current log sequence 658
SQL> alter system set log_archive_dest_1='location=/software/oracle/arch';
System altered.
SQL>
恢复节点2的归档日志
run{
allocate channel t1 type 'sbt_tape'
parms 'ENV=(NSR_SERVER=xxx,NSR_CLIENT=scdb2)';
set archivelog destination to '/software/oracle/arch';
restore archivelog from sequence 1023 thread 2 until sequence 1028 thread 2;
}
恢复节点1的归档日志
run{
allocate channel t1 type 'sbt_tape'
parms 'ENV=(NSR_SERVER=xxx,NSR_CLIENT=scdb2)';
set archivelog destination to '/software/oracle/arch';
restore archivelog from sequence 756 thread 1 until sequence 760 thread 1; }

3.6 将redolog改名：
[csdb:oracle]sqlplus / as sysdba

alter database rename file '+DGSYSTEM/RACDB/onlinelog/group_1.258.724171631' to '+RACDBDG/RACDB/onlinelog/group_1.258.724171631';
alter database rename file '+dgarch/RACDB/onlinelog/group_2.1043.724171657' to '+RACDBDG/RACDB/onlinelog/group_2.1043.724171657';
alter database rename file '+dgarch/RACDB/onlinelog/group_1.431.724171637' to '+RACDBDG/RACDB/onlinelog/group_1.431.724171637';
......
alter database rename file '+DGSYSTEM/RACDB/onlinelog/group_2.257.724171651' to '+RACDBDG/RACDB/onlinelog/group_2.257.724171651';
alter database rename file '+dgarch/RACDB/onlinelog/group_10.547.724170047' to '+RACDBDG/RACDB/onlinelog/group_10.547.724170047';

3.7 打开数据库：
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 11321006301221 generated at 08/22/201x 02:29:54 needed for
thread 1
ORA-00289: suggestion : /software/oracle/arch/1_660_716919669.dbf
ORA-00280: change 11321006301221 for thread 1 is in sequence #660
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>

3.8 删除多余的线程。
SQL> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC

SQL> select group# from v$log where thread#=2; --查出可以删除的日志文件．
SQL>ALTER DATABASE DISABLE THREAD 2; --disable将被删除日志文件的thread.
SQL> alter database drop logfile group 3; 删除日志组．
alter database drop logfile group 4;
alter database drop logfile group 8;
alter database drop logfile group 9；
alter database drop logfile group 10

3.9 删除不用的UNDO_TABLESPACE．
SQL> show parameter undo; --看正在使用的，在前面已经移除了UNDO_TABLESPACES2．
SQL> select tablespace_name from dba_tablespaces where contents='UNDO'; －－查ＵＮＤＯ表空间．
SQL>drop tablespace UNDOTABS2 including contents and datafiles; －－删除ＵＮＤＯ表空间．
临时表空间处理．
SQL> alter tablespace temp add tempfile '+RACDBDG/RACDB/tempfile
/temp01.dbf' SIZE 200M;

3.10 创建spfile,重新启动DB
SQL> Create spfile from memory;
File created.
SQL> startup force
ORACLE instance started.
Total System Global Area 6847938560 bytes
Fixed Size 2219808 bytes
Variable Size 6090129632 bytes
Database Buffers 738197504 bytes
Redo Buffers 17391616 bytes
Database mounted.
Database opened.

至此恢复测试完成.