创建ADG(RAC-->单实例)

主库不需要停机
环境的ip信息

[root@racbj01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
###public
10.107.174.164   racbj01
10.107.174.165   racbj02

###vip
10.107.174.167   racbj01vip
10.107.174.168   racbj02vip

###private
10.107.176.164   racbj01priv
10.107.176.165   racbj02priv

###scanip
10.107.174.166   racbjscan

备库安装好Oracle软件

[root@173adg dbs]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.107.174.173   173adg
10.107.174.166   racbjscan

RAC两个节点写好Oracle用户下的tnsnames.ora注意备库hosts文件写自己的ipracscanip的解析,tns文件也可以加一个rac其中一个节点的IP,然后rman连接的时候使用其中的一个节点

pritns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racbjscan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

stdtns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.174.173)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdg)   ##unique_name
    )
  )

准备备库监听文件

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.107.174.173)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=racdg)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=racdb))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)))
lsnrctl start

备库tns文件

pritns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racbjscan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

stdtns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.174.173)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdg)
    )
  )

把主库的密码文件传到备库而且要改名字,rac-dg这个环境要注意密码文件的问题,三个节点的密码文件都要一致,rac1也要传给rac2改名叫orapwracdb2,要不然rac2的alert就会报ORA-16191

[oracle@racbj01 dbs]$ orapwd file=orapwracdb1 password=oracle force=y
[oracle@racbj01 dbs]$ scp orapwracdb1 10.107.174.173:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb
[oracle@racbj01 dbs]$ scp orapwracdb1 racbj02:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb2

主备都执行(测试):

sqlplus sys/oracle@pritns as sysdba
sqlplus sys/oracle@stdtns as sysdba

配置Primary的参数

alter database force logging;
--添加standby logfile方便自动传到standby,一般是比redo多一组
alter database standby logfile
group 4 ('/u01/app/oracle/oradata/orcl/styredo04.log') size 100m,
group 5 ('/u01/app/oracle/oradata/orcl/styredo05.log') size 100m,
group 6 ('/u01/app/oracle/oradata/orcl/styredo06.log') size 100m,
group 7 ('/u01/app/oracle/oradata/orcl/styredo07.log') size 100m;

alter system set log_archive_config='DG_CONFIG=(racdb,racdg)' scope=both sid='*';  ##这里DG_CONFIG填的都是数据库的db_unique_name
alter system set log_archive_dest_2='SERVICE=stdtns LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdg' scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';

##这个和log_file_name_convert是作为备库时的是参数,重启生效,这两参数改了之后有可能到导致rac中一个节点shutdown,startup报错
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance
解决办法参考:https://blog.csdn.net/aaron8219/article/details/10027293

alter system set db_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb/datafile','/u01/app/oracle/tempfile','+DATA/racdb/tempfile' scope=spfile sid='*'; 
alter system set log_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb/onlinelog','/u01/app/oracle/redo','+ARC/racdb/onlinelog' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set fal_server='stdtns' scope=both sid='*';
alter system set fal_client='pritns' scope=both sid='*';

--检查是否生效
set linesize 500 pages 0
col value for a90
col name for a50
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');

主库创建pfile,备库创建pfile中涉及到的目录比如audit_dump

create pfile from spfile

备库

mkdir -p /u01/app/oracle/admin/racdb/adump /u01/app/oracle/oradata /u01/app/oracle/redo /u01/app/oracle/archivelog /u01/app/oracle/tempfile

Standby准备参数文件

db_name='racdb'
db_unique_name='racdg'
--要使用pfile启动,使用spfile启动会报错
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initracdb.ora';
rman target sys/oracle@pritns auxiliary sys/oracle@stdtns
run{
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
allocate channel ch03 type disk;
duplicate target database
for standby nofilenamecheck
from active database
spfile
	SET CLUSTER_DATABASE='FALSE'	##因为主库是RAC,备库是单实例,在读主库的控制文件的也想要复制到RAC结构的备库,所以这里指明备库不是RAC
	set 'db_unique_name' = 'racdg'
	set log_file_name_convert ='+DATA/racdb/onlinelog','/u01/app/oracle/oradata','+ARC/racdb/onlinelog','/u01/app/oracle/redo'##加上另一个路径(有几个不同的路径就写几个映射)
	set db_file_name_convert = '+DATA/racdb/datafile','/u01/app/oracle/oradata','+DATA/racdb/tempfile','/u01/app/oracle/tempfile'   ##加上临时文件路径(有几个不同的路径就写几个映射)
	set control_files='/u01/app/oracle/oradata/control01.ctl'
	set log_archive_dest_1	='location=/u01/app/oracle/archivelog'
	set log_archive_dest_2='service=pritns LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=racdb' ##service写tns中的名字
	set fal_client='stdtns'##tns中的名字
	set fal_server='pritns'##tns中的名字
	;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
	}

Standby进入恢复状态(非实时)

alter database recover managed standby database disconnect from session;

确认standby logfile状态

select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#       USED ARCHIVED  STATUS
---------- ---------- ---------- ---------- --------- ------------------------------
	11	    1	       0	  0 YES       UNASSIGNED
	12	    1	       0	  0 YES       UNASSIGNED
	13	    1	       0	  0 YES       UNASSIGNED
	14	    2	   18521     217088 YES       ACTIVE
	15	    2	       0	  0 NO	      UNASSIGNED
	16	    2	       0	  0 YES       UNASSIGNED

进入只读状态

alter database recover managed standby database cancel;
alter database open;
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY
alter database recover managed standby database disconnect from session;
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY


select process,client_process,thread#,sequence#,status from v$managed_standby;

PROCESS 		    CLIENT_PROCESS		THREAD#  SEQUENCE# STATUS
--------------------------- ------------------------ ---------- ---------- ------------------------------------
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      2      18519 CLOSING
ARCH			    ARCH			      1      49629 CLOSING
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      0 	 0 CONNECTED
RFS			    UNKNOWN			      0 	 0 IDLE
RFS			    ARCH			      0 	 0 IDLE
RFS			    UNKNOWN			      0 	 0 IDLE
RFS			    LGWR			      2      18521 IDLE
RFS			    UNKNOWN			      0 	 0 IDLE
RFS			    UNKNOWN			      0 	 0 IDLE
RFS			    LGWR			      1      49630 IDLE
RFS			    UNKNOWN			      0 	 0 IDLE
MRP0			    N/A 			      2      18521 APPLYING_LOG

17 rows selected.

备库

show parameter db_create_file_dest

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest		     string			       +DATA

设置为空,要不然主库添加数据文件备库会报错,db_create_file_destdb_file_name_convert有冲突

alter system set db_create_file_dest = '' scope=both;

System altered.

参考https://blog.csdn.net/cuji4856/article/details/100157657

遇到的坑:
ORA-19595:这个问题有可能就是因为spfile是在ASM上的原因
解决办法
create pfile from spfile然后create spfile=... from pfile=...
还要记得把备库参数文件中所有带有ASM的都替换掉

上一篇:[adg数据库同步机制]你们公司在选用oralce数据库和MySQL高可用方案的时候,有什么标准和问题?


下一篇:ADG常用查询