oracle11g_dataguard搭建

千家信息网最后更新 2025年01月20日oracle11g_dataguard搭建
先确认primary库处于归档模式
SQL> archive log list; 强制归档
SQL> alter database force logging;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/John/app/oracle/oradata/carthage/redo04.log','/John/app/oracle/oradata/carthage/redo044.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/John/app/oracle/oradata/carthage/redo05.log','/John/app/oracle/oradata/carthage/redo055.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/John/app/oracle/oradata/carthage/redo06.log','/John/app/oracle/oradata/carthage/redo066.log') SIZE 200M;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint; ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3; # cd /John/app/oracle/oradata/carthage/
# rm -rf redo01.log
# rm -rf redo02.log
# rm -rf redo03.log ALTER DATABASE ADD LOGFILE GROUP 1 ('/John/app/oracle/oradata/carthage/redo01.log','/John/app/oracle/oradata/carthage/redo011.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/John/app/oracle/oradata/carthage/redo02.log','/John/app/oracle/oradata/carthage/redo022.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/John/app/oracle/oradata/carthage/redo03.log','/John/app/oracle/oradata/carthage/redo033.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 7 ('/John/app/oracle/oradata/carthage/standby_redo01.log','/John/app/oracle/oradata/carthage/standby_redo011.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 8 ('/John/app/oracle/oradata/carthage/standby_redo02.log','/John/app/oracle/oradata/carthage/standby_redo022.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 9 ('/John/app/oracle/oradata/carthage/standby_redo03.log','/John/app/oracle/oradata/carthage/standby_redo033.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 10 ('/John/app/oracle/oradata/carthage/standby_redo04.log','/John/app/oracle/oradata/carthage/standby_redo044.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 11 ('/John/app/oracle/oradata/carthage/standby_redo05.log','/John/app/oracle/oradata/carthage/standby_redo055.log') SIZE 200M;
ALTER DATABASE ADD standby LOGFILE GROUP 12 ('/John/app/oracle/oradata/carthage/standby_redo06.log','/John/app/oracle/oradata/carthage/standby_redo066.log') SIZE 200M; $> mkdir -p /John/app/oracle/oradata/carthage/archive_dest
$> mkdir -p /John/app/oracle/oradata/carthage/standby_archive alter system set log_archive_dest_1='location=/John/app/oracle/oradata/carthage/archive_dest' scope=spfile;
alter system set standby_archive_dest='/John/app/oracle/oradata/carthage/standby_archive' scope=spfile;
alter system set standby_file_management='AUTO'; ---- 一致性关闭数据库起来后执行以下命令: create pfile from spfile;

2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initcarthage.ora $ORACLE_HOME/dbs/initcarthage.ora.bak
vi $ORACLE_HOME/dbs/initcarthage.ora
*.db_unique_name='carthage'
*.diagnostic_dest='/John/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='carthage'
*.fal_server='carthage_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/John/app/oracle/oradata/carthage','/John/app/oracle/oradata/carthage'
*.log_file_name_convert='/John/app/oracle/oradata/carthage','/John/app/oracle/oradata/carthage'
*.log_archive_config='DG_CONFIG=(carthage,carthage_dg)'
*.log_archive_dest_2='SERVICE=carthage_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=carthage_dg'
*.log_archive_dest_state_2='ENABLE'

listener.ora # listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools. SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/John/app/oracle/product/11.2.0/dbhome_2)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = carthage)
(ORACLE_HOME = /John/app/oracle/product/11.2.0/dbhome_2)
)
) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
) ADR_BASE_LISTENER = /John/app/oracle

tnsnames.ora # tnsnames.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools. hammer =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
) carthage =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.130)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
) carthage_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.131)(PORT = 10010))
)
(CONNECT_DATA =
(SERVICE_NAME = carthage)
)
) EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2.6 监听服务重启
lsnrctl stop
lsnrctl start
startup pfile=/John/app/oracle/product/11.2.0/dbhome_2/dbs/initcarthage.ora
-- 在备库上安装数据库软件
scp tnsnames.ora listener.ora 10.25.10.131:/John/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp initcarthage.ora orapwcarthage 10.25.10.131:/John/app/oracle/product/11.2.0/dbhome_1/dbs/ cat listener.ora # listener.ora Network Configuration File: /John/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools. SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/John/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = carthage)
(ORACLE_HOME = /John/app/oracle/product/11.2.0/dbhome_1)
)
) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.10.131)(PORT = 10010))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
) ADR_BASE_LISTENER = /John/app/oracle
cat initcarthage.ora
carthage.__db_cache_size=63350767616
carthage.__java_pool_size=536870912
carthage.__large_pool_size=536870912
carthage.__oracle_base='/John/app/oracle'#ORACLE_BASE set from environment
carthage.__pga_aggregate_target=53687091200
carthage.__sga_target=80530636800
carthage.__shared_io_pool_size=0
carthage.__shared_pool_size=15032385536
carthage.__streams_pool_size=0
*.audit_file_dest='/John/app/oracle/admin/carthage/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/John/app/oracle/oradata/carthage/control01.ctl','/John/app/oracle/oradata/carthage/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='carthage'
*.db_unique_name='carthage_dg'
*.db_file_name_convert='/John/app/oracle/oradata/carthage','/John/app/oracle/oradata/carthage'
*.log_file_name_convert='/John/app/oracle/oradata/carthage','/John/app/oracle/oradata/carthage'
*.log_archive_config='DG_CONFIG=(carthage,carthage_dg)'
*.fal_client='carthage_dg'
*.fal_server='carthage'
*.db_recovery_file_dest='/John/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/John/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=carthageXDB)'
*.log_archive_dest_1='location=/John/app/oracle/oradata/carthage/archive_dest'
*.log_archive_dest_2='SERVICE=carthage lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=carthage'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=134055198720
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.standby_archive_dest='/John/app/oracle/oradata/carthage/standby_archive'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
mkdir -p /John/app/oracle/admin/carthage/adump
mkdir -p /John/app/oracle/flash_recovery_area
mkdir -p /John/app/oracle/oradata/carthage/standby_archive
mkdir -p /John/app/oracle/oradata/carthage/archive_dest
lsnrctl start
lsnrctl status
---------------------创建catalog库--------可选(catalog必须为异地库)----- CREATE TABLESPACE catalog_tbs DATAFILE '/John/app/oracle/oradata/carthage/catalog_tbs.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M; CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE catalog_tbs; GRANT RECOVERY_CATALOG_OWNER TO rman;
$ rman catalog rman/rman
RMAN> CREATE CATALOG;
RMAN> REGISTER DATABASE; ----------------------------------------
rman target sys/oracle@carthage auxiliary sys/oracle@carthage_dg nocatalog
duplicate target database for standby from active database nofilenamecheck; recover managed standby database using current logfile disconnect from session; select process,client_process,sequence#,status from v$managed_standby;
select database_role,protection_mode,protection_level,open_mode from v$database;
查看DG的日志信息
select * from v$dataguard_status
alter database set standby database to maximize availability; ----切换为最大可用 alter database set standby database to maximize protection; ----切换为最大保护 (切换为最大保护模式报错，需要将standby端启动到mount状态切换)

----------------------故障切换 手工------------
--primary 做如下操作
alter database commit to switchover to physical standby;
shutdown immediate
startup alter database recover managed standby database disconnect from session;
select database_role,switchover_status from v$database; --standby 端做如下操作
alter database commit to switchover to primary;
shutdown immediate
startup
alter system switch logfile;
select database_role,switchover_status from v$database; ----------ADG做(fail over)切换测试-------------
--standby 端检查状态
select open_mode from v$database;

--我们通过shutdown abort方式人工模拟primary奔溃，直接关闭: --primary端
shutdown abort ; --在standby端执行如下操作 : startup mount;
alter system flush redo to 'carthage'; select thread#, low_sequence#, high_sequence# from v$archive_gap; --如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在standby端，要进行关闭apply和结束应用动作
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
select open_mode, switchover_status from v$database;