RAC+DG(asm單例項)

rac搭建參考 http://blog.itpub.net/70004783/viewspace-2792218/

DG(asm單例項)參考 http://blog.itpub.net/70004783/viewspace-2792705/

DG是都安裝完資料庫，後進asmcmd裡面將所有的資料檔案，redolog等都刪除，檔案資料夾刪除。

1、rac倆節點加dg節點hosts檔案

[root@rac1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
#public ip ent1
10.8.98.103	rac1
10.8.98.104	rac2
#priv ip ent2
192.168.1.103	rac1-priv
192.168.1.104	rac2-priv
#vip ip
10.8.98.105	rac1-vip
10.8.98.106	rac2-vip
#scan ip
10.8.98.107	cluster-scan
#dg
10.8.98.102 racdg

2、RAC開啟歸檔，主機開啟force logging 模式

SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;

alter system set db_unique_name='prod' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(prod,prodstd)' scope=both sid='*';
alter system set log_archive_dest_1='LOCATION=+ARCHDG01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodstd' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set db_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/' scope=spfile sid='*';
alter system set log_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/' scope=spfile sid='*';;
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set fal_server='tns_standby' scope=both sid='*';
alter system set fal_CLIENT='tns_primary' scope=both sid='*';

3、重新啟動資料庫， 檢查上述引數配置。

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');

4、查詢主庫日誌檔案，並新增standby log

SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
	 1	    1	       2	      50
	 1	    2	       2	      50
	 2	    3	       2	      50
	 2	    4	       2	      50
	 1	    5	       2	      50
	 2	    6	       2	      50

alter database add standby logfile thread 1 group 21 size 50m;
alter database add standby logfile thread 1 group 22 size 50m;
alter database add standby logfile thread 1 group 23 size 50m;
alter database add standby logfile thread 2 group 24 size 50m;
alter database add standby logfile thread 2 group 25 size 50m;
alter database add standby logfile thread 2 group 26 size 50m;

5、rac端更改lisenter.ora,tnsnames.ora（倆節點）lisenter需要重新reload一下

節點1

[grid@rac1:/home/grid]$cat /u01/app/11.2.0/grid/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = prod1))
  )
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
[grid@rac1:/home/grid]$

節點2

[grid@rac2:/home/grid]$cat /u01/app/11.2.0/grid/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = prod2))
  )
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))		# line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON		# line added by Agent

節點1和2

[oracle@rac1:/u01/app/oracle/product/11.2.0/db_1/network/admin]$cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )

6、 透過asmcmd，將dg上面的原來的資料檔案，控制檔案，日誌檔案都清空，，刪除原來系統上的pfile和密碼檔案

7、rac透過spfile建立pfile，傳到dg，修改必要引數

[oracle@rac1:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 23 15:23:12 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create pfile='/home/oracle/initproddg.ora' from spfile;
File created.
SQL>

修改後為

[oracle@racdg:/u01/app/oracle/product/11.2.0/db_1/dbs]$cat initproddg.ora 
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.4.0'
#*.control_files='+DATADG01/prod/controlfile/current.260.1083328405','+DATADG02/prod/controlfile/current.256.1083328405'
*.db_block_size=8192
*.db_create_file_dest='+DATADG01'
*.db_create_online_log_dest_1='+DATADG01'
*.db_create_online_log_dest_2='+DATADG02'
*.db_domain=''
*.db_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/'
*.db_name='prod'
*.db_recovery_file_dest='+ARCHDG01'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='prodstd'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_client='tns_standby'
*.fal_server='tns_primary'
*.log_archive_config='DG_CONFIG=(prod,prodstd)'
*.log_archive_dest_1='LOCATION=+ARCHDG01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodstd'
*.log_archive_dest_2='SERVICE=tns_primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=8
*.log_file_name_convert='+DATADG01/','+DATADG01/','+DATADG02/','+DATADG02/'
*.memory_target=3922722816
*.open_cursors=300
*.processes=1000
#*.remote_listener='cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.standby_file_management='AUTO'
[oracle@racdg:/u01/app/oracle/product/11.2.0/db_1/dbs]$

修改dg端 lisenter.ora,tnsnames.ora

[grid@racdg:/u01/app/11.2.0/grid/network/admin]$lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-SEP-2021 15:31:31
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@racdg:/u01/app/11.2.0/grid/network/admin]$cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = proddg))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
[grid@racdg:/u01/app/11.2.0/grid/network/admin]$

[oracle@racdg:/u01/app/oracle/product/11.2.0/db_1/network/admin]$cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
[oracle@racdg:/u01/app/oracle/product/11.2.0/db_1/network/admin]$

複製密碼檔案到dg端

[oracle@rac1:/u01/app/oracle/product/11.2.0/db_1/dbs]$scp orapwprod1 racdg:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwproddg

8、測試tnsping  ok，rac資料庫都是開啟狀態，備機開啟到nomount狀態。

9. 實施資料庫克隆透過rman duplicate   (在備機操作) 恢復時關閉rac的第二個節點，恢復完開啟即可

參照 http://blog.itpub.net/70004783/viewspace-2788445/

10、測試在rac1 和rac2各建立一個表空間，到dg檢視。