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;