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

最新推荐文章于 2024-04-26 16:42:17 发布

VIP文章存載一片天

最新推荐文章于 2024-04-26 16:42:17 发布

阅读量333

点赞数

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

本文链接：https://blog.csdn.net/andrewkao882/article/details/120716110

版权

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} [email protected]

最低0.47元/天解锁文章

存載一片天

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle 11g 搭建ADG方式(手动自动)

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 ...
复制链接

扫一扫