Oracle 11g 搭建ADG方式(手动自动)

最新推荐文章于 2025-06-07 22:54:32 发布

存載一片天

最新推荐文章于 2025-06-07 22:54:32 发布

阅读量511

点赞数

CC 4.0 BY-SA版权

分类专栏：笔记文章标签： oracle dba 数据仓库

本文链接：https://blog.youkuaiyun.com/andrewkao882/article/details/120716110

本文详细介绍了如何在Oracle 11g环境中手动和自动设置数据库镜像（ADG）。内容涵盖从创建监听、配置tnsnames.ora和listener.ora，到验证主备库连接，再到修改数据库参数，创建备用重做日志，直至最终的主备库实时同步。文中包含关键命令和配置示例，适合DBA参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1m. 登录主
2m/s. 在主备库同时创建静态监听ltnsnames
   主库的tnsnames.ora 添加备库TNS , 不需要重启 lsnrctl
   备库设定listener.ora（参考主）需重启LSNRCTL，tnsnames.ora 添加主和备库TNS
   主庫：

[oracle@cos6M5]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =  
  (SID_LIST =  
	(SID_DESC =  
	  (GLOBAL_DBNAME = testdb)  
	  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)  
	  (SID_NAME = testdb)  
	)  
  )  
LISTENER =
  (DESCRIPTION_LIST =
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
	)
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@cos6M5]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora 
TESTDB =
(DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = testdb)
	)
)
TESTDB_STD =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = testdb_std)
	)
  )
TESTDB_PRI =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = testdb)
	)
  )

[oracle@cos6M5 ~]$ cat /home/oracle/product/11.2.2/dbhome_1/network/admin/listener.ora
#有 ODBC  連接的
# listener.ora Network Configuration File: /home/oracle/product/11.2.2/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
	(SID_DESC =
	  (GLOBAL_DBNAME = sfcdb)
	  (ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
	  (SID_NAME = sfcdb)
	)
	(SID_DESC =
	  (SID_NAME = dg4msql)
	  (ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
	  (PROGRAM = dg4msql)
	)
	(SID_DESC =
	  (SID_NAME = MSSQL)
	  (ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
	  (PROGRAM = dg4msql)
	)
  )

LISTENER =
  (DESCRIPTION_LIST =
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))     ## 有雙網卡
	)
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	)
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
	)
  )


#啟動監控 LSNRCTL
[oracle@cos6M5]$  lsnrctl start/stop

   備庫類似。

4m/s. 验证主可tnsping 备，反之同。如：

 [oracle@cos6M5 ~]$ tnsping tsmaster_st

5m. 创建主库归档/备份目录

[oracle@cos6M5 ~]$ mkdir -p /data/oradata/TSMASTER/archivelog

6s. 创建备库归档目录, $ORACLE_BASE/admin下的目錄一般要手工建

[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/controlfile 
[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/archivelog 
[oracle@cos6s5 ~]$ mkdir -p /home/oracle/admin/tsmaster/adump
[oracle@cos6s5 ~]$ chown -R oracle:dba /home/oracle/admin/

7m. 修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。用于所有的事务都可以通过归档进行回滚

[oracle@cos6M5 ~]$ sqlplus / as sysdba
set linesize 1000;
set pagesize 1000;
SQL> alter database force logging;
SQL> alter database archivelog;
	Database altered.    ## 一般主机都有开启规档模式，先查看，如果没有开启，再作业

SQL> SELECT name,log_mode,force_logging FROM v$database;   --验证设定成功的状态
	NAME      LOG_MODE     FOR
	--------- ------------ ---
	TSMASTER  ARCHIVELOG   YES
	
M_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
	NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
	--------- -------------------- -------------------- ---------------- 
	SFCDB     READ WRITE           MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY
		
S_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
	NAME      OPEN_MODE            		PROTECTION_MODE      	DATABASE_ROLE    	 SWITCHOVER_STATUS
	--------- -------------------- -------------------- ---------------- --------------------
	SFCDB     READ ONLY WITH APPLY 	MAXIMUM PERFORMANCE   PHYSICAL STANDBY	  NOT ALLOWED

7.1m.查看Log 及logfile

SQL> select * from v$log;
	GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
		 1          1         13   52428800        512          2 NO  CURRENT                1127786 22-SEP-21   2.8147E+14
		 3          1         12   52428800        512          2 YES INACTIVE               1107440 22-SEP-21      1127786 22-SEP-21
		 2          1         11   52428800        512          2 YES INACTIVE               1087159 22-SEP-21      1107440 22-SEP-21

SQL> select group#,type, member from v$logfile;

	GROUP# TYPE    MEMBER
---------- ------- 
		 3 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_3_jm118n37_.log
		 3 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_3_jm118n7t_.log
		 2 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_2_jm118lds_.log
		 2 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_2_jm118lgt_.log
		 1 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_1_jm118jyx_.log
		 1 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_1_jm118k26_.log

6 rows selected.

8m. 创建standby redo log，一般与 online SID log对应并多一个文件

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/oradata/TSMASTER/onlinelog/tsm04.log') size 50M;
	Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/oradata/TSMASTER/onlinelog/tsm05.log') size 50M;
	Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data/oradata/TSMASTER/onlinelog/tsm06.log') size 50M;
	Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data/oradata/TSMASTER/onlinelog/tsm07.log') size 50M;
	Database altered.

SQL> select group#,type, member from v$logfile;
		GROUP# TYPE    MEMBER
	---------- ------- ------------------------------------------------------------------------------------------------------------------------
			 3 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_3_jm118n37_.log
			 3 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_3_jm118n7t_.log
			 2 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_2_jm118lds_.log
			 2 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_2_jm118lgt_.log
			 1 ONLINE  /data/oradata/TSMASTER/onlinelog/o1_mf_1_jm118jyx_.log
			 1 ONLINE  /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_1_jm118k26_.log
			 4 STANDBY /data/oradata/TSMASTER/onlinelog/tsm04.log
			 5 STANDBY /data/oradata/TSMASTER/onlinelog/tsm05.log
			 6 STANDBY /data/oradata/TSMASTER/onlinelog/tsm06.log
			 7 STANDBY /data/oradata/TSMASTER/onlinelog/tsm07.log

	10 rows selected.

例如，以下SQL语句可用于在要从具有两个重做线程的重做源数据库接收重做的数据库上创建备用重做日志：

	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
	SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;

9m. Oracle Pfile參數文件设定：
9m.1a 重启主库的参数变更方案a：非使用状态，可以重启

[oracle@cos6M5]$ sqlplus / as sysdba
SQL> create pfile from spfile;	

SQL> shutdown immediate;

[oracle@cos6M5]$ vim $ORACLE_HOME/dbs/inittestdb.ora 
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=671088640
testdb.__sga_target=989855744
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=234881024
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/TESTDB/controlfile/o1_mf_gq3chycs_.ctl','/u01/app/oracle/fast_recover
y_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl'*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
以下為新增：
DB_UNIQUE_NAME=testdb_pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_pri'
LOG_ARCHIVE_DEST_2='SERVICE=testdb_std  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_std'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=testdb_std
STANDBY_FILE_MANAGEMENT=AUTO

变更后，使用文件值导入	DB 并生效 spfile
[oracle@cos6M5]$ sqlplus / as sysdba
SQL>startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb.ora';
SQL>create spfile from pfile;

9m.1b 主库生产状态的变更方案b：使用状态，DB不重启

--  查看 DB_UNIQUE_NAME，如无设定此参数与db_name 同 此参数如有变理需要重启服务器生效
--  DB_UNIQUE_NAME 参数指本机TNS，要与后面的统一，避免混乱，如统一使用 testdb_pri
--  standby 激活或停ENABLE / DEFER
SQL> show parameter UNIQUE 
SQL> show parameter LOG_ARCHIVE_FORMAT			
	alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope = spfile; 静态参数變更，變更后需重啟DB生效

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_pri'
SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=testdb_std  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_std';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> alter system set FAL_SERVER='testdb_std';
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO	;    ---manual
SQL> alter system switch logfile;

9m.2 将主库的参数文件inittestdb.ora, 秘钥文件orapwtestdb Copy到备库的 $ORACL_HOME/dbs/ 目录下

[oracle@cos6M5]$scp {inittestdb.ora,orapwtestdb} oracle@196.168.1.1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

9s.3 備庫參數：

[oracle@cos6s5]$ vi $ORACLE_HOME/dbs/inittestdb.ora
主备信息指向变更其他不需变更：
		[]# grep Huge /proc/meminfo   #查詢大内存页”的详细内容
				AnonHugePages:         0 kB
				HugePages_Total:       0
				HugePages_Free:        0
				HugePages_Rsvd:        0
				HugePages_Surp:        0
				Hugepagesize:       2048 kB
				 从上面输出可以看到，每个页的大小为 2MB（Hugepagesize），并且系统中目前有 0 个“大内存页”（HugePages_Total）。这里“大内存页”的大小可以从 2MB 增加到 1GB

		# 一般分配缓存做DB的Buff，越大越好，通常 cache+ Share pool <sga_target 
		tjb2bdb.__db_cache_size=8589934592
		tjb2bdb.__shared_pool_size=6006243328

		DB_UNIQUE_NAME=testdb_std
		LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
		LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_std'
		LOG_ARCHIVE_DEST_2='SERVICE=testdb_pri  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_pri'
		FAL_SERVER=testdb_pri
		
		#如果主备目录不一致需添加， *.db_file_name_convert= 主数据库数据文件目录，备用数据库数据文件目录，如
		DB_FILE_NAME_CONVERT='/u01/app/oracle/TESTDB/','/usr/oracle/app/oradata/TESTDB/'	
		LOG_FILE_NAME_CONVERT='/u01/app/oracle/TESTDB/','/usr/oracle/app/oradata/TESTDB/' 
		
		#2G 依据连线最大量来判断 2000 内的连线，一般一个连接占用2KB左右。如果分配给太多，未使用则释放，但没必要
		pga_aggregate_target=2147483648   

		#分配DB使用内存的方案，一般占HOst内存的60%~70% 最少50%；如果有多个实例，需按资料量合理分配占比				
		sga_max_size=16G   
		sga_target=15G       

[oracle@cos6s5]$ cat $ORACLE_HOME/dbs/inittestdb.ora
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=671088640
testdb.__sga_target=989855744
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=234881024  
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/TESTDB/controlfile/o1_mf_gq3chycs_.ctl','/u01/app/oracle/fast_recovery_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=testdb_std  
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'  
LOG_ARCHIVE_DEST_1=  
 'LOCATION=/u01/app/oracle/TESTDB/archivelog 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  
  DB_UNIQUE_NAME=testdb_std'  
LOG_ARCHIVE_DEST_2='SERVICE=testdb_pri LGWR  LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_pri'  
LOG_ARCHIVE_DEST_STATE_1=ENABLE  
LOG_ARCHIVE_DEST_STATE_2=ENABLE  
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE  
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc  
FAL_SERVER=testdb_pri  
STANDBY_FILE_MANAGEMENT=AUTO

用pfile啟動，并生效Pfile
[oracle@cos6s5]$ sql