Oracle 19C Data Guard基礎運維-01安裝物理standby

Oracle 19C Data Guard 基礎運維 -01 安裝物理 standby

配置說明：

	主庫	備庫
IP	192.168.31.90	192.168.31.100
DB	Oracle 19.3.0.0.0	Oracle 19.3.0.0.0
OS	RedHat7.5	RedHat7.5
HostName	cjcos01	cjcos02
DBName	cjcdb	cjcdb
DB_UNIQUE_NAME	cjcdb	chendb
SERVICE_NAME	cjcdb	chendb
PDBName	cjcpdb	cjcpdb

原理：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/data-guard-concepts-and-administration.pdf

(1) 主庫：開啟歸檔模式

(2) 主庫：開啟強制寫日誌功能

(3) 主庫：關閉閃回

(4) 主庫：配置靜態監聽 , 配置 tnsnames 檔案

(5) 主庫：增加 standby logfile 檔案

(6) 主庫：修改引數檔案

(7) 主庫 : 拷貝主庫檔案到備庫

(8) 備庫：配置靜態監聽 , 配置 tnsnames.ora 檔案

(9) 備庫：修改引數檔案和口令檔案

(10) 備庫：根據據引數檔案建立相應的目錄

(11) 備庫： startup nomount

(12) 主庫 : 通過 rman duplicate 方式進行備庫恢復

(13) 驗證是否搭建成功  

 

(1) 主庫：開啟歸檔模式

SQL> sqlplus / as sysdba

SQL> alter system set log_archive_dest_1='location=/arch;

SQL> alter system set log_archive_format = "cjcpdb_%t_%s_%r.arc" scope=spfile;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

(2) 主庫：開啟強制寫日誌功能

SQL> select force_logging from v$database;

SQL> alter database force logging;

(3) 主庫：關閉閃回

SQL> select flashback_on from v$database;

SQL> alter database flashback off;

(4) 主庫：配置靜態監聽 , 配置 tnsnames 檔案

[oracle@cjcos01 ~]$ cd $ORACLE_HOME/network/admin

[oracle@cjcos01 admin]$ vim listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = cjcdb)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

      (SID_NAME = cjcdb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

[oracle@cjcos01 admin]$ vim tnsnames.ora

CJCDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = cjcdb)

    )

  )

CHENDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = chendb)

    )

CJCPDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = cjcpdb)

    )

  )

CHENPDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = cjcpdb)

    )

  )

[oracle@cjcos01 admin]$ lsnrctl stop

[oracle@cjcos01 admin]$ lsnrctl start  

(5) 主庫：增加 standby logfile 檔案

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/CJCDB/redo03.log

/u01/app/oracle/oradata/CJCDB/redo02.log

/u01/app/oracle/oradata/CJCDB/redo01.log

SQL> select bytes/1024/1024 from v$log;

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/CJCDB/standby_redo04.log' size 200M;

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/CJCDB/standby_redo05.log' size 200M;

SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/CJCDB/standby_redo06.log' size 200M;

SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/CJCDB/standby_redo07.log' size 200M;

(6) 主庫：修改引數檔案

SQL> create pfile from spfile;

[oracle@cjcos01 admin]$ cd $ORACLE_HOME/dbs

[oracle@cjcos01 dbs]$ cp initcjcdb.ora initcjcdb.ora.bak.1

[oracle@cjcos01 dbs]$ vim initcjcdb.ora

*.DB_NAME=cjcdb  

*.DB_UNIQUE_NAME=cjcdb  

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cjcdb,chendb)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cjcdb'

*.LOG_ARCHIVE_DEST_2='SERVICE=chendb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chendb'

*.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=chendb

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/chenpdb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/'

*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/chenpdb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/'

*.STANDBY_FILE_MANAGEMENT=AUTO

[oracle@cjcos01 dbs]$ cp spfilecjcdb.ora spfilecjcdb.ora.bak.2

SQL> shutdown immediate

SQL> create spfile from pfile;

SQL> startup

SQL> show parameter log_archive_dest_2

SQL> alter pluggable database cjcpdb open;

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED     READ ONLY  NO

 3 CJCPDB     READ WRITE NO

(7) 主庫 : 拷貝主庫檔案到備庫

監聽檔案，TNS 檔案，引數檔案、密碼檔案到備庫並改名

[oracle@cjcos01 ~]$ mkdir /home/oracle/dg

[oracle@cjcos01 ~]$ cd $ORACLE_HOME/dbs

[oracle@cjcos01 dbs]$ cp initcjcdb.ora /home/oracle/dg

[oracle@cjcos01 dbs]$ cp orapwcjcdb /home/oracle/dg

[oracle@cjcos01 dbs]$ cd ../network/admin/

[oracle@cjcos01 admin]$ cp listener.ora /home/oracle/dg

[oracle@cjcos01 admin]$ cp tnsnames.ora /home/oracle/dg

[oracle@cjcos01 ~]$ tar -zcvf dg.tar.gz dg/

[oracle@cjcos01 ~]$ scp dg.tar.gz 192.168.31.100:/home/oracle/

(8) 備庫：配置靜態監聽 , 配置 tnsnames.ora 檔案

[oracle@cjcos02 ~]$ tar -zxvf dg.tar.gz  

[oracle@cjcos02 dg]$ cd $ORACLE_HOME/network/admin

[oracle@cjcos02 admin]$ mv listener.ora listener.ora.bak

[oracle@cjcos02 admin]$ mv tnsnames.ora tnsnames.ora.bak

[oracle@cjcos02 admin]$ cp /home/oracle/dg/listener.ora .

[oracle@cjcos02 admin]$ cp /home/oracle/dg/tnsnames.ora .

[oracle@cjcos02 admin]$ vim listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = chendb)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

      (SID_NAME = chendb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

[oracle@cjcos02 admin]$ lsnrctl start

(9) 備庫：修改引數檔案和口令檔案

[oracle@cjcos02 admin]$ cd $ORACLE_HOME/dbs

[oracle@cjcos02 dbs]$ cp /home/oracle/dg/initcjcdb.ora .

[oracle@cjcos02 dbs]$ cp /home/oracle/dg/orapwcjcdb .

[oracle@cjcos02 dbs]$ mv orapwcjcdb orapwchendb

[oracle@cjcos02 dbs]$ mv initcjcdb.ora initchendb.ora

[oracle@cjcos02 dbs]$ vim initchendb.ora

:%s/cjcdb/AAA/g  

:%s/chendb/cjcdb/g  

:%s/AAA/chendb/g

------

*.DB_NAME=cjcdb

*.DB_UNIQUE_NAME=chendb

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(chendb,cjcdb)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chendb'

*.LOG_ARCHIVE_DEST_2='SERVICE=cjcdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cjcdb'

*.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=cjcdb

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/','/u01/app/oracle/oradata/chendb/chenpdb/'

*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/','/u01/app/oracle/oradata/chendb/chenpdb/'

*.STANDBY_FILE_MANAGEMENT=AUTO

(10) 備庫：根據據引數檔案建立相應的目錄

[root@cjcos02 ~]# mkdir /arch

[root@cjcos02 ~]# chown oracle.oinstall /arch

[oracle@jch ~]$ cd /u01/app/oracle/admin/

[oracle@cjcos02 admin]# mkdir chendb/{adump,dpdump,pfile} -p

[oracle@cjcos02 chendb]# cd /u01/app/oracle/oradata/

[oracle@cjcos02 oradata]# mkdir chendb/{chenpdb,cjcpdb,pdbseed} -p

(11) 備庫： startup nomount

[oracle@cjcos02 ~]$ export ORACLE_SID=chendb

[oracle@cjcos02 ~]$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount

(12) 主庫 : 通過 rman duplicate 方式進行備庫恢復

[oracle@cjcos01 ~]$ export ORACLE_SID=cjcdb

[oracle@cjcos01 ~]$ rman target / auxiliary sys/oracle@chendb

RMAN> duplicate target database for standby from active database;

(13) 驗證是否搭建成功

主庫：

SQL> col dest_name for a25

SQL> select dest_name,status from v$archive_dest_status;

DEST_NAME     STATUS

------------------------- ---------

LOG_ARCHIVE_DEST_1   VALID

LOG_ARCHIVE_DEST_2   VALID

SQL> archive log list;

SQL> alter system switch logfile;

備庫：  

SQL> archive log list;

SQL> select process, pid, status, client_process from v$managed_standby;

PROCESS   PID      STATUS CLIENT_P

--------- ------------------------ ------------ --------

ARCH   11867      CONNECTED ARCH

DGRD   11869      ALLOCATED N/A

DGRD   11871      ALLOCATED N/A

ARCH   11873      CONNECTED ARCH

ARCH   11875      CONNECTED ARCH

ARCH   11877      CONNECTED ARCH

RFS       12558      IDLE     Archival

RFS       12560      IDLE     LGWR

RFS       12565      IDLE     UNKNOWN

9 rows selected.

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL   ROLE      SWITCHOVER_STATUS

-------------------- -------------------- ---------------- --------------------

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

測試主庫 CDB$ROOT 資料同步

備庫：

SQL> alter database open;

SQL> recover managed standby database using current logfile disconnect from session;

-----SQL> recover managed standby database cancel;

主庫：  

SQL> create table test1 as select level as id from dual connect by level<=3;  

備庫：  

SQL> select * from test1;

ID

----------

 1

 2

 3

測試 PDB 資料同步

主庫：

SQL> conn cjc/cjc@cjcpdb

Connected.

SQL> create table tt1 as select level as id from dual connect by level<=3;

Table created.

備庫：

SQL> conn cjc/cjc@chenpdb

Connected.

SQL> select * from tt1;

ID

----------

 1

 2

 3

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2686491/，如需轉載，請註明出處，否則將追究法律責任。