1.环境准备,observer安装在192.168.56.100
[oracle@observer admin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.100 observer
192.168.56.101 dg01
192.168.56.102 dg02
2.observer的tnsnames.ora文件查看https://www.cndba.cn/hbhe0316/article/5339
https://www.cndba.cn/hbhe0316/article/5339
[oracle@observer admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_p)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL_P_DGMGRLl)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = ORCL)
)
(SID_DESC =
(GLOBAL_DBNAME = hbhe)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_s_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_s)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = observer)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
~
[oracle@observer admin]$ cat tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = observer)(PORT = 1521))
ORCL_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_p)
)
)
ORCL_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_s)
)
)
ORCL_P_DGMGRL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_P_DGMGRL)
)
)
ORCL_S_DGMGRL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_S_DGMGRL)
)
)
3.dg01的tnsnamer.ora文件和listener.ora文件
[oracle@dg01 admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@dg01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_p)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_p_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = hbhe)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg01 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
ORCL_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_p)
)
)
ORCL_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_s)
)
)
4.dg02的tnsnamer.ora文件和listener.ora文件
[oracle@dg02 admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@dg02 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_s_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_s)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = hbhe)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg02 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_p)
)
)
ORCL_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_s)
)
)
5.在dg01上执行命令解锁sysdg用户
SQL> alter user sysdg identified by wwwwww account unlock;
User altered.
SQL> grant sysdg to sysdg;
Grant succeeded.
SQL> set linesize 200
SQL> set pagesize 200
SQL> col username for a20
SQL> col sysbackup for a10
SQL> col sysdg for a20
SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;
USERNAME SYSBACKUP SYSDG
-------------------- ---------- --------------------
SYS FALSE FALSE
SYSDG FALSE TRUE
6.在dg01和dg02上执行命令,注意:两边都需要执行。
SQL> alter system set dg_broker_start=true;
System altered.
在dg02上执行命令
SQL> alter system set LOG_ARCHIVE_DEST_2='';
System altered.
7.在observer上测试连接https://www.cndba.cn/hbhe0316/article/5339
[oracle@observer admin]$ dgmgrl sysdg@orcl_p
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Nov 13 21:00:44 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "orcl_p"
Connected as SYSDG.
8.在observer上配置主库和备库https://www.cndba.cn/hbhe0316/article/5339https://www.cndba.cn/hbhe0316/article/5339
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS ORCL_P CONNECT IDENTIFIER IS ORCL_P;
Configuration "dg_config" created with primary database "orcl_p"
DGMGRL> Add database 'ORCL_S' as connect identifier is ORCL_S maintained as physical;
Database "ORCL_S" added
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
orcl_p - Primary database
ORCL_S - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
orcl_p - Primary database
ORCL_S - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 4 seconds ago)
DGMGRL> show database orcl_p;
Database - orcl_p
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> show database orcl_s;
Database - ORCL_S
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 17.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Database Status:
SUCCESS
9.主备切换https://www.cndba.cn/hbhe0316/article/5339
DGMGRL> VALIDATE DATABASE orcl_p;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
orcl_p: Off
Managed by Clusterware:
orcl_p: NO
Validating static connect identifier for the primary database orcl_p...
The static connect identifier allows for a connection to database "orcl_p".
DGMGRL> VALIDATE DATABASE orcl_s;
Database Role: Physical standby database
Primary Database: orcl_p
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
orcl_p: Off
ORCL_S: Off
Managed by Clusterware:
orcl_p: NO
ORCL_S: NO
Validating static connect identifier for the primary database orcl_p...
The static connect identifier allows for a connection to database "orcl_p".
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ORCL_S) (orcl_p)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on orcl_p
Transport-Related Property Settings:
Property orcl_p Value ORCL_S Value
LogXptMode ASYNC SYNC
DGMGRL> show database VERBOSE orcl_p;
Database - orcl_p
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_p'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'dg01'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_p_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl_p/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl_p/orcl/trace/drcorcl.log
Database Status:
SUCCESS
DGMGRL> show database VERBOSE orcl_s;
Database - ORCL_S
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 5.00 KByte/s
Active Apply Rate: 412.00 KByte/s
Maximum Apply Rate: 435.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_s'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'dg02'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_s_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcl_s/orcl/trace/alert_orcl.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl_s/orcl/trace/drcorcl.log
Database Status:
SUCCESS
在dg01上检查
https://www.cndba.cn/hbhe0316/article/5339
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY
10.切换https://www.cndba.cn/hbhe0316/article/5339
DGMGRL> connect sys@orcl_p
Password:
Connected to "orcl_p"
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO 'orcl_s';
Performing switchover NOW, please wait...
Operation requires a connection to database "ORCL_S"
Connecting ...
Connected to "orcl_s"
Connected as SYSDBA.
New primary database "ORCL_S" is opening...
Operation requires start up of instance "orcl" on database "orcl_p"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_p"
Database mounted.
Database opened.
Connected to "orcl_p"
Switchover succeeded, new primary is "orcl_s"
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY
11.重新切回来https://www.cndba.cn/hbhe0316/article/5339https://www.cndba.cn/hbhe0316/article/5339
DGMGRL> SWITCHOVER TO 'orcl_p';
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_p"
Connecting ...
Connected to "orcl_p"
Connected as SYSDBA.
New primary database "orcl_p" is opening...
Operation requires start up of instance "orcl" on database "ORCL_S"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_s"
Database mounted.
Database opened.
Connected to "orcl_s"
Switchover succeeded, new primary is "orcl_p"
版权声明:本文为博主原创文章,未经博主允许不得转载。
Linux,oracle