RAC搭建DG-生產端控制檔案和聯機日誌檔案傳至DG端-實現DG端資料庫open
本次實驗是在DG的基礎下實現的
DG搭建之最佳實踐(基本引數配置,且本次配置不考慮切換)
生產端：
alter system set log_archive_dest_1='LOCATION=+data/xuh/archivelog' scope=both;
alter system set log_archive_dest_2='SERVICE=xuhdg arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xuh' scope=both;
alter system set log_archive_dest_state_1=ENABLE scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter system set db_file_name_convert='+data/xuh/datafile','/oracle/app/xuh','+data/xuh/tempfile','/oracle/app/xuh' scope=spfile;
alter system set log_file_name_convert='+data/xuh','/oracle/app/xuh' scope=both;
DG端：
*.audit_file_dest='/oracle/app/admin/xuh/adump'
*.background_dump_dest='/oracle/app/admin/xuh/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/app/xuh/control01.ctl'
*.core_dump_dest='/oracle/app/admin/xuh/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='+data/xuh/datafile','/oracle/app/xuh','+data/xuh/tempfile','/oracle/app/xuh'
*.db_name='xuh'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xuhXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oracle/arch'
*.log_archive_dest_2='SERVICE=xuh arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xuh'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+data/xuh','/oracle/app/xuh'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=281018368
*.undo_management='AUTO'
undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/admin/xuh/udump'
搭建結束後，生產端alter system archive log current；對比生產端和DG端max(sequence#),驗證select max(sequence#),thread# from v$archived_log group by thread#;
在確保生產端和DG端歸檔日誌完全同步後，進行如下操作
由於生產端是10gRAC，資料庫檔案均放在ASM磁碟組中，不能將聯機日誌檔案直接scp或者ftp到DG端。因此，新建online redo log，並且新建的聯機日誌放在檔案系統上，刪除舊聯機日誌檔案
SQL> select a.group#,a.thread#,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER
---------- ---------- ------------------------------
1 1 +DATA/xuh/redo01.log
2 1 +DATA/xuh/redo02.log
3 2 +DATA/xuh/redo03.log
4 2 +DATA/xuh/redo04.log
SQL> alter database add logfile thread 1 group 5 ('/oracle/app/xuh/redo05.log') size 50M;
Database altered.
SQL> alter database add logfile thread 1 group 6 ('/oracle/app/xuh/redo06.log') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 7 ('/oracle/app/xuh/redo07.log') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 8 ('/oracle/app/xuh/redo08.log') size 50M;
Database altered.
SQL> select a.group#,a.thread#,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER
---------- ---------- ------------------------------
1 1 +DATA/xuh/redo01.log
2 1 +DATA/xuh/redo02.log
3 2 +DATA/xuh/redo03.log
4 2 +DATA/xuh/redo04.log
5 1 /oracle/app/xuh/redo05.log
6 1 /oracle/app/xuh/redo06.log
7 2 /oracle/app/xuh/redo07.log
8 2 /oracle/app/xuh/redo08.log
8 rows selected.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log CURRENT
3 2 +DATA/xuh/redo03.log CURRENT
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log UNUSED
6 1 /oracle/app/xuh/redo06.log UNUSED
7 2 /oracle/app/xuh/redo07.log UNUSED
8 2 /oracle/app/xuh/redo08.log UNUSED
8 rows selected.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log UNUSED
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log UNUSED
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log CURRENT
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log CURRENT
3 2 +DATA/xuh/redo03.log CURRENT
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
2 1 +DATA/xuh/redo02.log INACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log CURRENT
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> alter database drop logfile '+DATA/xuh/redo02.log';
Database altered.
SQL> alter database drop logfile '+DATA/xuh/redo03.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
6 rows selected.
SQL> alter database drop logfile '+DATA/xuh/redo04.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL> alter database drop logfile '+DATA/xuh/redo01.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL>
日誌檔案切換到檔案系統之後，將控制檔案建立到檔案系統上（原控制檔案在ASM磁碟組上）
SQL> select name,status from v$controlfile;
NAME STATUS
------------------------------ -------
+DATA/xuh/control01.ctl
+DATA/xuh/control02.ctl
+DATA/xuh/control03.ctl
SQL> alter system set control_files='/oracle/app/xuh/control01.ctl','/oracle/app/xuh/control02.ctl','/oracle/app/xuh/control03.ctl' scope=spfile;
System altered.
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 28 00:40:29 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 117443744 bytes
Database Buffers 159383552 bytes
Redo Buffers 2170880 bytes
RMAN> restore controlfile from '+DATA/xuh/control01.ctl';
Starting restore at 28-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=xuh1 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/oracle/app/xuh/control01.ctl
output filename=/oracle/app/xuh/control02.ctl
output filename=/oracle/app/xuh/control03.ctl
Finished restore at 28-JUL-15
RMAN> exit
Recovery Manager complete.
到這裡，控制檔案以及聯機日誌檔案都在檔案系統上，可以用scp傳到DG端指定位置
[oracle@standby xuh]$ ls
control01.ctl redo05.log redo07.log sysaux.479.886185005 undotbs1.480.886185001 users.476.886185017
control01.ctl.bak redo06.log redo08.log system.481.886184991 undotbs2.477.886185015
[oracle@standby xuh]$ pwd
/oracle/app/xuh
[oracle@standby xuh]$
這邊需要注意，scp過來的控制檔案中的logfile已經修改到檔案系統上，現在只需將datafile的路徑重新rename下，從ASM磁碟組指向新的檔案系統位置
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 01:02:26 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 88083616 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> alter database rename file '+DATA/xuh/datafile/system.481.886184991' to '/oracle/app/xuh/system.481.886184991';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/undotbs1.480.886185001' to '/oracle/app/xuh/undotbs1.480.886185001';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/sysaux.479.886185005' to '/oracle/app/xuh/sysaux.479.886185005';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/undotbs2.477.886185015' to '/oracle/app/xuh/undotbs2.477.886185015';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/users.476.886185017' to '/oracle/app/xuh/users.476.886185017';
Database altered.
SQL> recover database;
ORA-00279: change 502365 generated at 07/27/2015 21:25:55 needed for thread 1
ORA-00289: suggestion : /oracle/arch/1_30_886184975.dbf
ORA-00280: change 502365 for thread 1 is in sequence #30
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502365 generated at 07/27/2015 18:54:40 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_1_886184975.dbf
ORA-00280: change 502365 for thread 2 is in sequence #1
.
.
.
這邊省略歸檔日誌的應用
.
.
.
ORA-00279: change 538963 generated at 07/28/2015 00:21:10 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_25_886184975.dbf
ORA-00280: change 538963 for thread 2 is in sequence #25
ORA-00278: log file '/oracle/arch/2_24_886184975.dbf' no longer needed for this
recovery
ORA-00279: change 538969 generated at 07/28/2015 00:21:22 needed for thread 1
ORA-00289: suggestion : /oracle/arch/1_59_886184975.dbf
ORA-00280: change 538969 for thread 1 is in sequence #59
ORA-00278: log file '/oracle/arch/1_58_886184975.dbf' no longer needed for this
recovery
ORA-00279: change 538972 generated at 07/28/2015 00:21:22 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_26_886184975.dbf
ORA-00280: change 538972 for thread 2 is in sequence #26
ORA-00278: log file '/oracle/arch/2_25_886184975.dbf' no longer needed for this
recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG
到此，已經能正常實現DG端資料庫正常open了。本方法不用switchover或者failover，同樣能使得資料庫正常open，零資料丟失！
DG搭建之最佳實踐(基本引數配置,且本次配置不考慮切換)
生產端：
alter system set log_archive_dest_1='LOCATION=+data/xuh/archivelog' scope=both;
alter system set log_archive_dest_2='SERVICE=xuhdg arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xuh' scope=both;
alter system set log_archive_dest_state_1=ENABLE scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter system set db_file_name_convert='+data/xuh/datafile','/oracle/app/xuh','+data/xuh/tempfile','/oracle/app/xuh' scope=spfile;
alter system set log_file_name_convert='+data/xuh','/oracle/app/xuh' scope=both;
DG端：
*.audit_file_dest='/oracle/app/admin/xuh/adump'
*.background_dump_dest='/oracle/app/admin/xuh/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/app/xuh/control01.ctl'
*.core_dump_dest='/oracle/app/admin/xuh/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='+data/xuh/datafile','/oracle/app/xuh','+data/xuh/tempfile','/oracle/app/xuh'
*.db_name='xuh'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xuhXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oracle/arch'
*.log_archive_dest_2='SERVICE=xuh arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xuh'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+data/xuh','/oracle/app/xuh'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=281018368
*.undo_management='AUTO'
undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/admin/xuh/udump'
搭建結束後，生產端alter system archive log current；對比生產端和DG端max(sequence#),驗證select max(sequence#),thread# from v$archived_log group by thread#;
在確保生產端和DG端歸檔日誌完全同步後，進行如下操作
由於生產端是10gRAC，資料庫檔案均放在ASM磁碟組中，不能將聯機日誌檔案直接scp或者ftp到DG端。因此，新建online redo log，並且新建的聯機日誌放在檔案系統上，刪除舊聯機日誌檔案
SQL> select a.group#,a.thread#,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER
---------- ---------- ------------------------------
1 1 +DATA/xuh/redo01.log
2 1 +DATA/xuh/redo02.log
3 2 +DATA/xuh/redo03.log
4 2 +DATA/xuh/redo04.log
SQL> alter database add logfile thread 1 group 5 ('/oracle/app/xuh/redo05.log') size 50M;
Database altered.
SQL> alter database add logfile thread 1 group 6 ('/oracle/app/xuh/redo06.log') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 7 ('/oracle/app/xuh/redo07.log') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 8 ('/oracle/app/xuh/redo08.log') size 50M;
Database altered.
SQL> select a.group#,a.thread#,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER
---------- ---------- ------------------------------
1 1 +DATA/xuh/redo01.log
2 1 +DATA/xuh/redo02.log
3 2 +DATA/xuh/redo03.log
4 2 +DATA/xuh/redo04.log
5 1 /oracle/app/xuh/redo05.log
6 1 /oracle/app/xuh/redo06.log
7 2 /oracle/app/xuh/redo07.log
8 2 /oracle/app/xuh/redo08.log
8 rows selected.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log CURRENT
3 2 +DATA/xuh/redo03.log CURRENT
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log UNUSED
6 1 /oracle/app/xuh/redo06.log UNUSED
7 2 /oracle/app/xuh/redo07.log UNUSED
8 2 /oracle/app/xuh/redo08.log UNUSED
8 rows selected.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log UNUSED
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log UNUSED
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log CURRENT
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log CURRENT
3 2 +DATA/xuh/redo03.log CURRENT
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
2 1 +DATA/xuh/redo02.log ACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
8 rows selected.
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
2 1 +DATA/xuh/redo02.log INACTIVE
3 2 +DATA/xuh/redo03.log INACTIVE
4 2 +DATA/xuh/redo04.log CURRENT
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log INACTIVE
8 rows selected.
SQL> alter database drop logfile '+DATA/xuh/redo02.log';
Database altered.
SQL> alter database drop logfile '+DATA/xuh/redo03.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
4 2 +DATA/xuh/redo04.log INACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
6 rows selected.
SQL> alter database drop logfile '+DATA/xuh/redo04.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log CURRENT
5 1 /oracle/app/xuh/redo05.log ACTIVE
6 1 /oracle/app/xuh/redo06.log ACTIVE
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log ACTIVE
5 1 /oracle/app/xuh/redo05.log CURRENT
6 1 /oracle/app/xuh/redo06.log INACTIVE
7 2 /oracle/app/xuh/redo07.log INACTIVE
8 2 /oracle/app/xuh/redo08.log CURRENT
SQL> /
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
1 1 +DATA/xuh/redo01.log INACTIVE
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL> alter database drop logfile '+DATA/xuh/redo01.log';
Database altered.
SQL> select a.group#,a.thread#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER STATUS
---------- ---------- ------------------------------ ----------------
5 1 /oracle/app/xuh/redo05.log INACTIVE
6 1 /oracle/app/xuh/redo06.log CURRENT
7 2 /oracle/app/xuh/redo07.log CURRENT
8 2 /oracle/app/xuh/redo08.log INACTIVE
SQL>
日誌檔案切換到檔案系統之後，將控制檔案建立到檔案系統上（原控制檔案在ASM磁碟組上）
SQL> select name,status from v$controlfile;
NAME STATUS
------------------------------ -------
+DATA/xuh/control01.ctl
+DATA/xuh/control02.ctl
+DATA/xuh/control03.ctl
SQL> alter system set control_files='/oracle/app/xuh/control01.ctl','/oracle/app/xuh/control02.ctl','/oracle/app/xuh/control03.ctl' scope=spfile;
System altered.
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 28 00:40:29 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 117443744 bytes
Database Buffers 159383552 bytes
Redo Buffers 2170880 bytes
RMAN> restore controlfile from '+DATA/xuh/control01.ctl';
Starting restore at 28-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=xuh1 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/oracle/app/xuh/control01.ctl
output filename=/oracle/app/xuh/control02.ctl
output filename=/oracle/app/xuh/control03.ctl
Finished restore at 28-JUL-15
RMAN> exit
Recovery Manager complete.
到這裡，控制檔案以及聯機日誌檔案都在檔案系統上，可以用scp傳到DG端指定位置
[oracle@standby xuh]$ ls
control01.ctl redo05.log redo07.log sysaux.479.886185005 undotbs1.480.886185001 users.476.886185017
control01.ctl.bak redo06.log redo08.log system.481.886184991 undotbs2.477.886185015
[oracle@standby xuh]$ pwd
/oracle/app/xuh
[oracle@standby xuh]$
這邊需要注意，scp過來的控制檔案中的logfile已經修改到檔案系統上，現在只需將datafile的路徑重新rename下，從ASM磁碟組指向新的檔案系統位置
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 01:02:26 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 88083616 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> alter database rename file '+DATA/xuh/datafile/system.481.886184991' to '/oracle/app/xuh/system.481.886184991';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/undotbs1.480.886185001' to '/oracle/app/xuh/undotbs1.480.886185001';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/sysaux.479.886185005' to '/oracle/app/xuh/sysaux.479.886185005';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/undotbs2.477.886185015' to '/oracle/app/xuh/undotbs2.477.886185015';
Database altered.
SQL> alter database rename file '+DATA/xuh/datafile/users.476.886185017' to '/oracle/app/xuh/users.476.886185017';
Database altered.
SQL> recover database;
ORA-00279: change 502365 generated at 07/27/2015 21:25:55 needed for thread 1
ORA-00289: suggestion : /oracle/arch/1_30_886184975.dbf
ORA-00280: change 502365 for thread 1 is in sequence #30
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502365 generated at 07/27/2015 18:54:40 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_1_886184975.dbf
ORA-00280: change 502365 for thread 2 is in sequence #1
.
.
.
這邊省略歸檔日誌的應用
.
.
.
ORA-00279: change 538963 generated at 07/28/2015 00:21:10 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_25_886184975.dbf
ORA-00280: change 538963 for thread 2 is in sequence #25
ORA-00278: log file '/oracle/arch/2_24_886184975.dbf' no longer needed for this
recovery
ORA-00279: change 538969 generated at 07/28/2015 00:21:22 needed for thread 1
ORA-00289: suggestion : /oracle/arch/1_59_886184975.dbf
ORA-00280: change 538969 for thread 1 is in sequence #59
ORA-00278: log file '/oracle/arch/1_58_886184975.dbf' no longer needed for this
recovery
ORA-00279: change 538972 generated at 07/28/2015 00:21:22 needed for thread 2
ORA-00289: suggestion : /oracle/arch/2_26_886184975.dbf
ORA-00280: change 538972 for thread 2 is in sequence #26
ORA-00278: log file '/oracle/arch/2_25_886184975.dbf' no longer needed for this
recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG
到此，已經能正常實現DG端資料庫正常open了。本方法不用switchover或者failover，同樣能使得資料庫正常open，零資料丟失！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30310891/viewspace-1753385/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Django實現web端tailf日誌檔案DjangoWebAI
- oracle dg庫資料檔案空間不足Oracle
- [20180718]拷貝資料檔案從dg庫.txt
- DG備庫手動管理 新增資料檔案
- [20190410]dg建立臨時表檔案資料檔案.txt
- Oracle DG備庫手動管理新增資料檔案Oracle
- DG歸檔日誌缺失恢復
- oracle dg庫資料檔案建立失敗ORA-01111Oracle
- 遠端備份資料庫和檔案的方法資料庫
- oracle dg 歸檔日誌恢復情況Oracle
- Oracle RAC+DG搭建Oracle
- oracle DG 日誌傳輸小結Oracle
- dg主庫建立檔案備庫未同步解決方法
- spring cloud feign實現遠端呼叫服務傳輸檔案SpringCloud
- Vue實現多檔案上傳功能(前端 + 後端程式碼)Vue前端後端
- 檔案下載之斷點續傳（客戶端與服務端的實現）斷點客戶端服務端
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle
- 【邏輯DG滾動升級一】ORACLE11204 邏輯DG滾動升級至12C---生產端前期準備Oracle
- 怎麼遠端傳輸大檔案？
- 後端配置檔案後端
- 分析Oracle資料庫日誌檔案（三）EPOracle資料庫
- 分析Oracle資料庫日誌檔案（二）DOOracle資料庫
- 分析Oracle資料庫日誌檔案（一）HBOracle資料庫
- Qt實現基於多執行緒的檔案傳輸（服務端，客戶端）QT執行緒服務端客戶端
- go開發屬於自己的日誌庫-檔案日誌庫實現Go
- 在vs code透過git提交檔案至遠端倉庫(github)Github
- C語言log日誌管理-支援檔案與終端輸出C語言
- 改造xxl-job的客戶端日誌檔案生成體系客戶端
- rsync udr——遠端大檔案傳輸加速
- 利用程序池給客戶端傳檔案客戶端
- 11G RAC+DG搭建
- go 開發屬於自己的日誌庫-檔案日誌庫原型實現Go原型
- 遠端檔案包含shell
- mysql學習8：第四章:資料庫檔案--日誌檔案MySql資料庫
- 【邏輯DG滾動升級二】ORACLE11204 邏輯DG滾動升級至12C---DG端前期準備Oracle
- SpringBoot+Vue.js實現前後端分離的檔案上傳Spring BootVue.js後端
- 遠端登入和複製檔案
- git上傳到遠端有部分檔案未上傳Git
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle