题目部分
在Oracle中,将单实例备份集恢复为rac数据库的步骤有哪些?
♣
答案部分
将单实例备份集恢复为rac数据库的过程基本上就是先将备份集恢复为单实例的数据库,然后再将数据库转换为RAC库。
数据库的备份可以使用如下的脚本:
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/rman_back/full_%n_%T_%t_%s_%p.bak';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '/home/oracle/rman_back/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
}
将单实例备份集恢复为rac数据库常用的命令如下所示:
ORACLE_SID=lhrdb
ORACLE_SID=lhrrac21
mkdir +DATA/lhrrac2/
startup nomount
restore spfile to '+DATA/lhrrac2/spfilelhrrac2.ora' from '/home/oracle/rman_back/full_LHRDBxxx_20180711_981220001_4_1.bak';
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initlhrrac21.ora
SPFILE='+DATA/lhrrac2/spfilelhrrac2.ora'
echo "SPFILE='+DATA/lhrrac2/spfilelhrrac2.ora'" > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initlhrrac22.ora
create pfile='/tmp/a.txt' from spfile;
*.audit_file_dest='/u01/app/oracle/admin/lhrdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+FRA'
*.db_block_size=8192
*.db_domain=''
*.db_name='lhrdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrdbXDB)'
*.memory_target=415236096
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
create spfile='+DATA/lhrrac2/spfilelhrrac2.ora' from pfile='/tmp/b.txt';
startup nomount force;
restore controlfile from '/home/oracle/rman_back/ctl_LHRDB_20180711_7_1.bak';
alter database mount;
set line 9999 pagesize 9999
col FILE_NAME format a60
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
set pagesize 200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a;
set pagesize 200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "+DATA";' from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "+DATA";' from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''+DATA'''' ";' FROM v$logfile a;
set newname for datafile 1 to "+DATA";
set newname for datafile 2 to "+DATA";
set newname for datafile 3 to "+DATA";
set newname for datafile 4 to "+DATA";
set newname for datafile 5 to "+DATA";
set newname for tempfile 1 to "+DATA";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo03.log'' to ''+DATA'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo02.log'' to ''+DATA'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo01.log'' to ''+DATA'' ";
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
set newname for datafile 1 to "+DATA";
set newname for datafile 2 to "+DATA";
set newname for datafile 3 to "+DATA";
set newname for datafile 4 to "+DATA";
set newname for datafile 5 to "+DATA";
set newname for tempfile 1 to "+DATA";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo03.log'' to ''+DATA'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo02.log'' to ''+DATA'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/lhrdb/redo01.log'' to ''+DATA'' ";
restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
release channel c1;
}
list backupset of archivelog all;
RUN{
set until sequence 6;
recover database;
}
alter database open resetlogs;
以上步骤执行完后依然是一个单实例的数据库,因此需要将数据库转换为RAC库。需要修改集群参数,redo和undo的相关内容:
select * from v$option where parameter = 'Real Application Clusters';
--集群参数
alter system set cluster_database=true scope=spfile;
alter system set cluster_database_instances=2 scope=spfile;
alter system set instance_number=1 scope=spfile sid='lhrrac21';
alter system set instance_number=2 scope=spfile sid='lhrrac22';
--redo
select THREAD# ,STATUS from v$thread;
alter system set thread=1 scope=spfile sid='lhrrac21';
alter system set thread=2 scope=spfile sid='lhrrac22';
alter database add logfile thread 2 group 4 '+FRA' size 50M;
alter database add logfile thread 2 group 5 '+FRA' size 50M;
alter database add logfile thread 2 group 6 '+FRA' size 50M;
col instance format a20
select thread#,instance,status,enabled from v$thread;
alter database enable thread 2 ;
--undo
create undo tablespace undotbs2 datafile '+DATA' SIZE 50m;
alter system set undo_tablespace='undotbs1' scope=spfile sid='lhrrac21';
alter system set undo_tablespace='undotbs2' scope=spfile sid='lhrrac22';
show spparameter undo
srvctl remove db -d lhrdb -f
srvctl add database -d lhrdb -o $ORACLE_HOME -p +DATA/lhrrac2/spfilelhrrac2.ora
srvctl config database -d lhrdb
srvctl add instance -d lhrdb -i lhrrac21 -n raclhr-11gR2-N1
srvctl add instance -d lhrdb -i lhrrac22 -n raclhr-11gR2-N2
srvctl config database -d lhrdb
srvctl start db -d lhrdb
---重建集群相关的视图
@$ORACLE_HOME/rdbms/admin/catclust.sql
& 说明:
有关将单实例备份集恢复为rac数据库的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-1682250/。
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗