RAC环境: db_name= orcl sid= orcl1/orcl2
单实例: db_name= orcl sid= orcl
Rac 网卡设置:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.223 rac1
192.168.10.224 rac2
192.168.10.225 rac1-vip
192.168.10.226 rac2-vip
10.10.10.3 rac1-priv
10.10.10.4 rac2-priv
192.168.10.227 rac-scan
192.168.10.222 rman_backup
注:红色为rac 相关IP , 蓝色为 单实例IP
创建监听,通过netmgr 或者在 $ORALCE_HOME/NETWORK/ADMIN/下配置 listener.ora和tnsname.ora文件:同时rac 和 单实例下都需要配置,tnsping通就行。
[oracle@rac1 admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_SING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rman_backup)(PORT= 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_SING为需要添加的部分
[oracle@rman_backup admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rman_backup)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
通过RAC 的spfile 生成pfile文件修改后 scp 至单机
RAC 生成pfile文件
SQL> create pfile='/u01/app/oracle/initorcl.ora' from spfile;
[oracle@rac1 ~]$ cat /u01/app/oracle/initorcl.ora
orcl1.__db_cache_size=234881024
orcl2.__db_cache_size=268435456
orcl1.__java_pool_size=16777216
orcl2.__java_pool_size=16777216
orcl1.__large_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__pga_aggregate_target=402653184
orcl2.__pga_aggregate_target=402653184
orcl1.__sga_target=754974720
orcl2.__sga_target=754974720
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=436207616
orcl2.__shared_pool_size=436207616
orcl1.__streams_pool_size=33554432
orcl2.__streams_pool_size=0 --这行及以上要删除
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true --删除
*.compatible='11.2.0.0.0'
*.control_files='+DATAFILE/orcl/controlfile/current.260.825093345','+ARCHIVELOG/orcl/controlfile/current.256.825093345' --修改
*.db_block_size=8192
*.db_create_file_dest='+DATAFILE' --修改
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+ARCHIVELOG' --修改
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
orcl2.instance_number=2 --删除
orcl1.instance_number=1 --删除
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_listener='scan.com:1521' --删除
*.remote_login_passwordfile='exclusive'
*.sessions=170
orcl2.thread=2
orcl1.thread=1
orcl2.undo_tablespace='UNDOTBS2' --删除
orcl1.undo_tablespace='UNDOTBS1' --修改为orcl.undo_tablespace='UNDOTBS1'
单机pfile 文件来源于修改后的RAC 文件:
[oracle@rman_backup ~]$ cat /u01/app/oracle/initorcl.ora
orcl.__db_cache_size=452984832
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=687865856
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=184549376
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/orcl/controlfile/current.260.825093345','/u01/app/oracle/oradata/controlfile/current.256.825093345'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/orcl'
*.db_domain=''
*.db_file_name_convert=('+DATAFILE/orcl/datafile','/u01/app/oracle/oradata/orcl')
*.db_file_name_convert=('+DATAFILE/orcl/tempfile','/u01/app/oracle/oradata/orcl/tempfile')
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/archive_log'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert=('+DATAFILE/orcl/onlinelog','/u01/app/oracle/oradata/logfile')
*.log_file_name_convert=('+ARCHIVELOG/orcl/onlinelog','/u01/app/oracle/onlinelog/logfile')
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
创建orapwdSID 密码文件
orapwd file=orapworcl password=oracle entires=10
scp orapworcl oracle@192.168.10.222:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
备份rac 所有数据,一个节点上备份,另一个节点的archivelog 日志备份后,也得拷贝过去
rac1:
run {
allocate channel c1 device type diskconnect 'sys/oracle@orcl1';
allocate channel c2 device type diskconnect 'sys/oracle@orcl2';
backup database format'/u01/app/oracle/db_back/%U_%T.DB';
sql 'alter system archive log current';
backup format '/u01/app/oracle/db_back/%U_%T.ARCHIVE' archiveloog all delete input;
backup current controlfile FORMAT'/u01/app/oracle/db_back/%T.CTL';
backup spfile tag='spfile'format='/u01/app/oracle/db_back/%T.PFILE';
release channel c2;
release channel c1;
}
scp /u01/app/oracle/db_back/* oracle@192.168.10.222:/u01/app/oracle/db_back/
rac 2:
backup archivelog all format'/u01/app/oracle/db_back/%U_%T.archivelog'
scp /u01/app/oracle/db_back/* oracle@192.168.10.222:/u01/app/oracle/db_back/
node 单实例通过pfile 加载到 nomount 状态后,通过pfile 创建spfile 文件。
startup nomount pfile='/u01/app/oracle/initorcl.ora';
create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from pfile='/u01/app/oracle/initorcl.ora';
通过 rman target sys/oracle@ORCL auxiliary / 在单机登陆。
通过 duplicate target database to orcl 恢复数据。 ----这两步有可能会报错,根据报错修改信息。
[oracle@rman_backup db_back]$ rman target sys/oracle@ORCL auxiliary /
RMAN> duplicate target database to orcl;
Starting Duplicate Db at 09-SEP-13
using target database control file insteadof recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 devicetype=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modifiedby RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL'' comment=
''Modified by RMAN duplicate''scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMANduplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 687866376 bytes
Database Buffers 452984832 bytes
Redo Buffers 9371648 bytes
Starting restore at 09-SEP-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 devicetype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
channel ORA_AUX_DISK_1: reading from backuppiece /u01/app/oracle/db_back/20130909.CTL
channel ORA_AUX_DISK_1: piecehandle=/u01/app/oracle/db_back/20130909.CTL tag=TAG20130909T152516
channel ORA_AUX_DISK_1: restored backuppiece 1
channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:03
output filename=/u01/app/oracle/orcl/controlfile/current.260.825093345
output filename=/u01/app/oracle/oradata/controlfile/current.256.825093345
Finished restore at 09-SEP-13
database mounted
Using previous duplicated file/u01/app/oracle/oradata/orcl/system.256.825091903 for datafile 1 withcheckpoint SCN of 2318677
Using previous duplicated file /u01/app/oracle/oradata/orcl/sysaux.257.825091907for datafile 2 with checkpoint SCN of 2318677
Using previous duplicated file/u01/app/oracle/oradata/orcl/undotbs1.258.825091909 for datafile 3 withcheckpoint SCN of 2318657
Using previous duplicated file/u01/app/oracle/oradata/orcl/users.259.825091909 for datafile 4 with checkpointSCN of 2318657
Using previous duplicated file/u01/app/oracle/oradata/orcl/example.264.825093429 for datafile 5 withcheckpoint SCN of 2318677
Using previous duplicated file/u01/app/oracle/oradata/orcl/undotbs2.265.825093865 for datafile 6 withcheckpoint SCN of 2318657
Using previous duplicated file/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105 for datafile 7 withcheckpoint SCN of 2318657
contents of Memory Script:
{
set until scn 2996999;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcl/system.256.825091903";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcl/sysaux.257.825091907";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcl/users.259.825091909";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcl/example.264.825093429";
set newname for datafile 6 to
"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865";
set newname for datafile 7 to
"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";
}
executing Memory Script
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
contents of Memory Script:
{
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/system.256.825091903",
"/u01/app/oracle/oradata/orcl/sysaux.257.825091907",
"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909",
"/u01/app/oracle/oradata/orcl/users.259.825091909",
"/u01/app/oracle/oradata/orcl/example.264.825093429",
"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865",
"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";
switch clone datafile 1 todatafilecopy
"/u01/app/oracle/oradata/orcl/system.256.825091903";
switch clone datafile 2 todatafilecopy
"/u01/app/oracle/oradata/orcl/sysaux.257.825091907";
switch clone datafile 3 todatafilecopy
"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909";
switch clone datafile 4 todatafilecopy
"/u01/app/oracle/oradata/orcl/users.259.825091909";
switch clone datafile 5 todatafilecopy
"/u01/app/oracle/oradata/orcl/example.264.825093429";
switch clone datafile 6 todatafilecopy
"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865";
switch clone datafile 7 todatafilecopy
"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";
}
executing Memory Script
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/system.256.825091903 RECID=10 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/sysaux.257.825091907RECID=11 STAMP=825722444
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs1.258.825091909 RECID=12STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/users.259.825091909RECID=13 STAMP=825722444
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/example.264.825093429 RECID=14STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/undotbs2.265.825093865RECID=15 STAMP=825722444
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105 RECID=16STAMP=825722444
datafile 1 switched to datafile copy
input datafile copy RECID=10STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/system.256.825091903
datafile 2 switched to datafile copy
input datafile copy RECID=11STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/sysaux.257.825091907
datafile 3 switched to datafile copy
input datafile copy RECID=12STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/undotbs1.258.825091909
datafile 4 switched to datafile copy
input datafile copy RECID=13STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/users.259.825091909
datafile 5 switched to datafile copy
input datafile copy RECID=14STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/example.264.825093429
datafile 6 switched to datafile copy
input datafile copy RECID=15STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/undotbs2.265.825093865
datafile 7 switched to datafile copy
input datafile copy RECID=16STAMP=825722444 filename=/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105
contents of Memory Script:
{
set until scn 2996999;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 09-SEP-13
using channel ORA_AUX_DISK_1
starting media recovery
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 687866376 bytes
Database Buffers 452984832 bytes
Redo Buffers 9371648 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Reset to original value by RMAN''scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ==============
RMAN-00571:=====================================================
RMAN-03002: failure of Duplicate Db commandat 09/09/2013 23:20:57
RMAN-05501: aborting duplication of targetdatabase
RMAN-03015: error occurred in stored scriptMemory Script
RMAN-06053: unable to perform mediarecovery because of missing log
RMAN-06025: no backup of archived log forthread 2 with sequence 46 and starting SCN of 2990968 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 45 and starting SCN of 2990878 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 44 and starting SCN of 2904738 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 43 and starting SCN of 2811911 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 42 and starting SCN of 2768543 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 41 and starting SCN of 2698110 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 40 and starting SCN of 2665715 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 39 and starting SCN of 2561227 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 38 and starting SCN of 2468986 found to restore
RMAN-06025: no backup of archived log for thread2 with sequence 37 and starting SCN of 2380959 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 36 and starting SCN of 2318917 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 35 and starting SCN of 2318877 found to restore
RMAN-06025: no backup of archived log forthread 2 with sequence 34 and starting SCN of 2318176 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 72 and starting SCN of 2975716 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 71 and starting SCN of 2897150 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 70 and starting SCN of 2831168 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 69 and starting SCN of 2723427 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 68 and starting SCN of 2612996 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 67 and starting SCN of 2525003 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 66 and starting SCN of 2418165 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 65 and starting SCN of 2318921 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 64 and starting SCN of 2318880 found to restore
RMAN-06025: no backup of archived log forthread 1 with sequence 63 and starting SCN of 2318172 found to restore
提示缺少归档日志
开始不完全恢复
startup mount;
restore database until sequence 62 thread 1;
recover database until sequence 62 thread 1;
alter database open resetlogs;
至此迁移基本完成
单实例后续扫尾工作。
8.1 :清除多余的undo文件
查看UNDO 信息:
SQL> select name from v$tablespace wherename like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
因为我们使用的是UNDOTBS1,在pfile里设置的,所以把UNDOTBS2删除掉。
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 includingcontents and datafiles;
Tablespace dropped.
SQL> select name from v$tablespace wherename like 'UNDO%';
NAME
------------------------------
UNDOTBS1
8.2 :清除未使用线程的redo日志组
默认情况下,RAC 环境下,每个实例都有2个redo。 在单实例下,就没有必要了,删除点线程2的redo 信息。
SQL> select thread#,status,enabled fromv$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 YES UNUSED
2 1 NO CURRENT
3 2 NO CURRENT
4 2 YES UNUSED
SQL> alter database disable thread 2;
Database altered.
SQL> alter database drop logfile group3;
Database altered.
SQL> alter database drop logfile group4;
Database altered.
SQL> selectgroup#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO CURRENT
现在就剩2个了。 一般的单实例是3个online redo,再添加一组。
SQL> alter database add logfile group 3('/u01/app/oracle/oradata/orcl/redo03.log') size 10m;
Database altered.
SQL> selectgroup#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO CURRENT
3 1 YES UNUSED
8.3: 重建临时表空间,并删除原来的数据文件
SQL> create temporary tablespace temp1 tempfile '/U01/app/oracle/oradata/orcl/temp01.dbf' size 100M autoextend on next 100M maxsize unlimited;
SQL> alter database default temporary tablespace temp1;
SQL> drop tablespace temp including contents and datafiles;
SQL> select file#,name from v$tempfile;