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 |
原理：
(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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 19C Data Guard基礎運維-02 Switchovers(物理)Oracle運維
- Oracle 19C Data Guard基礎運維-03 Failovers(物理)Oracle運維AI
- Oracle 19C Data Guard基礎運維-06 PROTECTION MODEOracle運維
- Oracle 19C Data Guard基礎運維-08 DML重定向Oracle運維
- Oracle 19C Data Guard基礎運維-04 Failovers疑問？Oracle運維AI
- Oracle 19C Data Guard基礎運維-05Failovers (GAP)Oracle運維AI
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- Oracle 19C Data Guard基礎運維-07 failover後閃回恢復dg架構Oracle運維AI架構
- 2 Oracle Data Guard 安裝Oracle
- Oracle 19C OGG基礎運維-01環境準備Oracle運維
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- 【DG】Data Guard搭建（physical standby）
- 【mos 1265700.1】Oracle Patch Assurance - Data Guard Standby-First Patch ApplyOracleAPP
- oracle 11g data guard維護Oracle
- G008-ORACLE-DG ORACLE 19C Active Data Guard DML RedirectionOracle
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- Oracle 19C OGG基礎運維-05DDL操作同步Oracle運維
- Oracle 19C OGG基礎運維-06增加複製表Oracle運維
- Oracle 19C OGG基礎運維-08Error code [942]Oracle運維Error
- 【ASK_ORACLE】Oracle Data Guard（二）物理備庫的概念和優勢Oracle
- Oracle 19C OGG基礎運維-02資料初始化Oracle運維
- Oracle 19C OGG基礎運維-07減少複製表Oracle運維
- Oracle 19C OGG基礎運維-09OGG-15121錯誤Oracle運維
- Jumpserver基礎運維-01介紹Server運維
- Oracle Data Guard Broker元件Oracle元件
- Oracle Data Guard簡介Oracle
- Postgresql日常運維-安裝(Linux)01SQL運維Linux
- Oracle 19C OGG基礎運維-04DML同步常見問題Oracle運維
- 1 關於 Oracle Data GuardOracle
- 1 Oracle Data Guard Broker 概念Oracle
- Oracle Data Guard和Broker概述Oracle
- 01-linu核心基礎-02運維基礎重要概念運維
- 4.1.6 Oracle Restart 與 Oracle Data Guard 整合OracleREST
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- oracle 19c 安裝、解除安裝Oracle
- 8 Oracle Data Guard Broker 屬性Oracle
- 9 Oracle Data Guard 故障診斷Oracle
- [20221111]19c配置Data Guard Broker問題.txt