oracle dataguard
ORACLE10G DATAGUARD配置筆記
環境：
OS:RHEL5+ORACLE10G10.2.0.1
IP:172.17.61.160(primary) 172.17.61.161(standby)
ORACLE_SID:orcl
ORACLE_HOME:/u01/app
一、配置standby database為MAXIMIZE PERFORMANCE模式
二、轉換模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION
三、主庫和備庫的switchover.
一、配置standby database為MAXIMIZE PERFORMANCE模式
--主庫操作
1.設置主庫為force logging
SQL> alter database force logging;
2.設置主庫為歸檔模式：
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
建議再追加設定歸檔位置
# mkdir -p /u01/app/archlog
alter system set log_archive_dest_1 = 'Location=/u01/app/archlog' scope=both
3.檢查主機是否有口令文件,如沒有需建立
orapwd file='/u01/app/dbs/orapworcl' password=sys entries=5
orapworcl文件名不能錯了
4.為主數據庫添加"備用聯機日誌文件"
SQL> alter database add standby logfile group 4
('/u01/oradata/orcl/redo04.log') size 50m;
SQL> alter database add standby logfile group 5
('/u01/oradata/orcl/redo05.log') size 50m;
SQL> alter database add standby logfile group 6
('/u01/oradata/orcl/redo06.log') size 50m;
SQL> alter database add standby logfile group 7
('/u01/oradata/orcl/redo07.log') size 50m;
5.修改主庫參數文件 !!
SQL> create pfile='/u01/app/admin/orcl/pfile/initprim.ora' from spfile;
orcl.__db_cache_size=100663296
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=54525952
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/orcl/adump'
*.background_dump_dest='/oracle/admin/orcl /bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/ orcl/control03.ctl'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/oracle /flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_format='%T%S%r.ARC'
*.log_archive_max_processes=3
*. open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/orcl /udump'
#add below parameter for standy database
*.DB_UNIQUE_NAME='10gpri'
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
*.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'
*.log_archive_dest_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='10gstandby'
*.FAL_CLIENT ='10gpri'
6.主庫用PFILE建立SPFILE
[oracle@Rod-DG01 pfile]$ sqlplus '/ as sysdba'
SQL> create spfile from pfile='/u01/app/admin/orcl/pfile/initprim.ora';
7.建立備用庫的控製文件
SQL> alter database create standby controlfile as '/home/oracle/standby_ctl01.ctl';
8.配置主數據庫listener及tnsnames
[oracle@Rod-DG01 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG01)(PORT = 1521))
)
)
#加1522端 ??口供以後做switchover
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app)
(SID_NAME = orcl)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG01)(PORT = 1522))
)
)
[oracle@Rod-DG01 admin]$ cat tnsnames.ora
#1521和1522端 ??口都能連上主機和備機，這樣在做switchover時就不需要改這裡的設置了
10gpri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = ( PROTOCOL = TCP)(HOST = 172.17.61.161)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1522))
)
(CONNECT_DATA =
(SID = orcl)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.161)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
9.複製文件到備機(於備機操作)
scp -p oracle@172.17.61.160:/u01/oradata/orcl/*.dbf . 複製datafile
scp -p oracle@172.17.61.160:/u01/oradata/orcl/*.log . redelog文件也需要復製到備機
scp -p oracle@172.17.61.160:/home/oracle/standby_ctl01.ctl . redelog備機專用控制檔
複製&建立三分控制檔
[oracle@Rod-DG02 ~]cd /u01/oradata/orcl
[oracle@Rod-DG02 orcl]$mv standby_ctl01.ctl control01.ctl
[oracle@Rod-DG02 orcl]$cp control01.ctl control02.ctl
[oracle@Rod-DG02 orcl]$cp control01.ctl control03.ctl
10.複製並修改備機的參數文件
[oracle@Rod-DG02 ~]cd /u01/app/admin/orcl/pfile
[oracle@Rod-DG02 pfile]$scp -p oracle@172.17.61.160:/u01/app/admin/orcl/pfile/initprim.ora initstandby.ora
修改為如下：
orcl.__db_cache_size=100663296
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=54525952
orcl.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/orcl/adump'
*.background_dump_dest='/oracle /admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/ oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest ='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_format='%T%S%r.ARC'
*.log_archive_max_processes =3
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle /admin/orcl/udump'
#須修改項目如下
*.DB_UNIQUE_NAME='10gstandby'
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
*.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'
*.log_archive_dest_2 ='SERVICE=10gpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='10gpri'
*.FAL_CLIENT='10gstandby'
11.生成備用庫密碼文件
orapwd file='/u01/app/dbs/orapworcl' password=sys entries=5
12.修改備機的listener及tnsnames
[oracle@Rod-DG02 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = :
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG02)(PORT = 1521))
)
)
#加一個1522的端口供以後做switchover
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app)
(SID_NAME = orcl)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG02)(PORT = 1522))
)
)
[oracle@Rod-DG02 admin]$ cat tnsnames.ora
10gpri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1522))
)
(CONNECT_DATA =
(SID = orcl)
)
)
13.測試主備之間網絡連通
[oracle@Rod-DG01 admin]$ lsnrctl start
[oracle@Rod-DG01 admin]$ tnsping 10gstandby
[oracle@Rod-DG02 admin]$ lsnrctl start
[oracle@Rod-DG02 admin]$ tnsping 10gpri
14.打開備庫
SQL> startup nomount pfile='/u01/app/admin/orcl/pfile/initstandby.ora';
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> create spfile from pfile='/u01/app/admin/orcl/pfile/initstandby.ora';
SQL> alter database recover managed standby database disconnect from session;
15.打開主庫
SQL> startup
16.測試是否OK
主庫：
SQL> alter system switch logfile;
主備庫執行：
SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
FIRST_TIME NEXT_TIME APP SEQUENCE#
------------------- ------------------- --- ---- ------
2007-10-16 11:39:33 2007-10-16 14:16:39 YES 5
2007-10-16 14:16:39 2007-10-16 14:21:06 YES 6
確認SEQUENCE#同步即可!
二、轉換模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION
1.在備機上：
SQL> shutdown immediate;
SQL> startup mount;
2.在主機上
SQL> shutdown immediate;
SQL> startup mount；
SQL> alter system set log_archive_dest_2='SERVICE=10gstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby';
SQL>alter database set standby database to maximize protection;
SQL>alter database open;
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PROTECTION
3.在備機上
SQL> recover managed standby database disconnect from session;
4.測試：
在主機上執行SQL> alter system switch logfile;
在備機上查看v$standby_log視圖
SQL> select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# USED ARC STATUS
---------- ---------- ---------- ---------- - -- ----------
4 1 36 59392 YES ACTIVE
5 1 0 512 NO UNASSIGNED
6 0 0 512 YES UNASSIGNED
7 0 0 512 YES UNASSIGNED
MAXIMIZE PROTECTION和MAXIMIZE AVAILABILITY模式下，備機不能先關閉，會出現如下錯誤
SQL> shutdown immediate;
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
正確的開關機順序是：
關機：先關主機，後關備機
開機：先開備機，後開主機
三、主庫和備庫的switchover.
注意：Swithover時只能先從Primary切到Standby，再從Standby切到Primary.
1.準備原主庫是否有standby redo log，上面1.4已建好了。
2.準備主庫和備庫的參數文件，最好就是將兩個數據庫的參數文件互換，在兩台機器上同時保留主庫和備庫的參數文件。
[oracle@Rod-DG02 /]$ cd /u01/app/admin/orcl/pfile
[oracle@Rod-DG02 pfile]scp -p oracle@172.17.61.160:/oracle/admin/orcl/pfile/initprim.ora initprimary.ora
[oracle@Rod-DG02 pfile]scp -p initstandby.ora oracle@172.17.61.160:/oracle/admin/orcl/pfile/
3.從primary切換到standby
connect / as sysdba
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown
SQL> startup mount pfile=/oracle/admin/orcl/pfile/initstandby.ora;
SQL> recover managed standby database disconnect;
4.啟動新備庫端的Listener (port=1522)
[oracle@Rod-DG01 admin]$ lsnrctl stop
[oracle@Rod-DG01 admin]$ lsnrctl start LISTENER1
5.從standby切換到primary
connect / as sysdba
SQL> alter database commit to switchover to primary;
SQL> shutdown
SQL> startup pfile=/oracle/admin/orcl/pfile/initprimary.ora
startup pfile=/home/oracle/switchover/initprim.ora
6.啟動新主庫端的Listener (port=1522)
[oracle@Rod-DG02 admin]$ lsnrctl stop
[oracle@Rod-DG02 admin]$ lsnrctl start LISTENER1
7.OK
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27146449/viewspace-772910/ ，如需转载，请注明出处，否则将追究法律责任。
