前面写过一篇用图形界面来安装Physical Standby的,但是考试的时候如果搭建不起来gc,那就很被动和麻烦了。所以有时候我们需要做两手准备,图形界面用不了,我还能手工搭建。那么这篇文章就是纯手工实现所有data guard操作的。
1.在主库,修改db_unique_name,打开归档和flashback,force logging
SQL> alter system set db_unique_name=PROD5_T1 scope=spfile;
System altered.
shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;
alter database open;
alter database force logging;
alter system set standby_file_management=auto;
2.主库上增加standby log file,在做switch over的时候,与在线重做日志相比,每个线程应该多加一个额外的组。否则会影响switch over
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo01.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo02.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo03.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo04.log') SIZE 50M;
3.在主库和备库配置service
prod5_pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.us.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PROD5)
)
)
prod5_stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orae12.us.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PROD5)
)
)
3.在主库配置静态监听服务,这里注意名字要命名为PROD5_T1_DGMGRL
SID_LIST_LISTENER =
( SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD5_T1_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=PROD5)
)
)
4.在备库配置静态监听服务,这里注意名字要命名为PROD5_T2_DGMGRL
SID_LIST_LISTENER =
( SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD5_T2_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=PROD5)
)
)
5.在主库和备库均reload监听服务,查看监听状态.
lsnrctl reload
lsnrctl status
Service "PROD5_T1_DGMGRL" has 1 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD5_T2_DGMGRL" has 1 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
6.备库创建目录,按照主库的目录结构进行创建。
mkdir -p /u01/app/oracle/oradata/PROD5/datafile/
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/admin/PROD5/adump
7.在备库创建密码文件
orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPROD5 password=oracle entries=10
8.在备库创建参数文件,只需要设置db_name
cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs
cat initPROD5.ora
*.db_name='PROD5'
9.在备库启动之前,先要在/etc/oratab加入一行,这样可以使用12c的环境变量。
PROD5:/u01/app/oracle/product/12.1.0/dbhome_1:N
10.在主库使用DUPLICATE创建standby
先要在备库启动实例
. oraenv
sqlplus / as sysdba
startup nomount
然后主库执行DUPLICATE
rman target sys/oracle@prod5_pri auxiliary sys/oracle@prod5_stby
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='PROD5_T2' COMMENT 'this is standby'
NOFILENAMECHECK;
11.主库和备库同时打开dg_broker_start参数
ALTER SYSTEM SET dg_broker_start = true;
12.使用dg broker来配置主库和备库的关联关系,并启用配置
dgmgrl sys/oracle@prod5_pri
DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS PROD5_T1 CONNECT IDENTIFIER IS prod5_pri;
DGMGRL> ADD DATABASE PROD5_T2 AS CONNECT IDENTIFIER IS prod5_stby MAINTAINED AS PHYSICAL;
DGMGRL> show CONFIGURATION
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
prod5_t1 - Primary database
prod5_t2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> ENABLE CONFIGURATION;
Enabled.
13.检查配置的情况发现报ORA-16810
DGMGRL> show Configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
prod5_t1 - Primary database
prod5_t2 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 39 seconds ago)
先分别先检查主库和备库都是一些什么问题.执行show database verbose检查
DGMGRL> show database verbose "prod5_t1"
Database - prod5_t1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD5
Properties:
DGConnectIdentifier = 'prod5_pri'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD5_T1_DGMGRL)(INSTANCE_NAME=PROD5)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
DGMGRL> show database verbose "prod5_t2"
Database - prod5_t2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
PROD5
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16675: database instance restart required for property value modification to take effect
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
Database Error(s):
ORA-16766: Redo Apply is stopped
Properties:
DGConnectIdentifier = 'prod5_stby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orae12.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD5_T2_DGMGRL)(INSTANCE_NAME=PROD5)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
14.处理ORA-16714问题
备库出现一些ORA-16714错误。执行检查
DGMGRL> show database "prod5_t2" 'InconsistentProperties'
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PROD5 ArchiveLagTarget 0 0
PROD5 LogArchiveMaxProcesses 4 4
PROD5 LogArchiveMinSucceedDest 1 1
PROD5 LogArchiveTrace 0 (missing) 0
PROD5 LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf
这里面其实很多值是一样,SPFILE_VALUE为missing需要重启一下,重启备库之后在检查。
sqlplus / as sysdba
shutdown immediate
startup mount
DGMGRL> show database "prod5_t2" 'InconsistentProperties'
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PROD5 ArchiveLagTarget 0 0
PROD5 LogArchiveMaxProcesses 4 4
PROD5 LogArchiveMinSucceedDest 1 1
修改上述三个值。
edit database 'prod5_t2' set property 'ArchiveLagTarget'=0;
edit database 'prod5_t2' set property 'LogArchiveMaxProcesses'=4;
edit database 'prod5_t2' set property 'LogArchiveMinSucceedDest'=1;
全部改完之后再一次检查。就没有任何问题了。
DGMGRL> show Configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
prod5_t1 - Primary database
prod5_t2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 19 seconds ago)
DGMGRL> SHOW DATABASE prod5_t2;
Database - prod5_t2
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: 123.00 KByte/s
Real Time Query: ON
Instance(s):
PROD5
Database Status:
SUCCESS
15.执行switchover
[oracle@host01 dbs]$ dgmgrl sys/oracle@prod5_pri
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL>
DGMGRL> SWITCHOVER TO prod5_t2;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5_t2"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5_t2" is opening...
Operation requires start up of instance "PROD5" on database "prod5_t1"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5_t2"
在另外一个节点在反切回来
[oracle@orae12 trace]$ dgmgrl sys/oracle@prod5_stby
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO prod5_t1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5_t1"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5_t1" is opening...
Operation requires start up of instance "PROD5" on database "prod5_t2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5_t1"
16.转换备库为snapshot
[oracle@host01 dbs]$ dgmgrl sys/oracle@prod5_pri
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> CONVERT DATABASE "prod5_t2" TO SNAPSHOT STANDBY;
Converting database "prod5_t2" to a Snapshot Standby database, please wait...
Database "prod5_t2" converted successfully
DGMGRL> CONVERT DATABASE "prod5_t2" TO PHYSICAL STANDBY;
Converting database "prod5_t2" to a Physical Standby database, please wait...
Operation requires shut down of instance "PROD5" on database "prod5_t2"
Shutting down instance "PROD5"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "PROD5" on database "prod5_t2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Continuing to convert database "prod5_t2" ...
Database "prod5_t2" converted successfully
17.配置fast fail over,这里可以参考传送门
##########主库运行##########
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
prod5_t1 - Primary database
prod5_t2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 27 seconds ago)
edit database "prod5_t1" set property LogXptMode='SYNC';
edit database "prod5_t2" set property LogXptMode='SYNC';
edit configuration set protection mode as maxavailability;
edit database "prod5_t1" set property FastStartFailoverTarget='prod5_t2';
edit database "prod5_t2" set property FastStartFailoverTarget='prod5_t1';
edit database "prod5_t2" set state='apply-off';
##########备库运行##########
sqlplus / as sysdba
alter database flashback on;
##########主库运行##########
DGMGRL> edit database "prod5_t2" set state='apply-on';
edit database 'prod5_t1' set property TransportLagThreshold=30;
edit database 'prod5_t2' set property TransportLagThreshold=30;
edit database 'prod5_t1' set property ApplyLagThreshold=45;
edit database 'prod5_t2' set property ApplyLagThreshold=45;
DGMGRL> ENABLE FAST_START FAILOVER
Enabled.
DGMGRL> start observer
Observer started
#############做点破坏性动作,shutdown abort主库
SQL> shutdown abort;
ORACLE instance shut down
##########然后观察者可以看到下列输出
DGMGRL> start observer
Observer started
19:45:15.24 Sunday, May 27, 2018
Initiating Fast-Start Failover to database "prod5_t2"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prod5_t2"
19:45:19.26 Sunday, May 27, 2018
至此整个data guard全手工命令行搭建,及switch over,fail over,还有转换成snapshot都完成了。
Trackbacks & Pingbacks 1
[…] 第三章较为简单,这一场考试时间也是90分钟,如果用图形做的话时间会很充裕,前提是你第二章把Cloud Control搭起来,整个过程我都是用图形做的,除了最后的Fast failover以外(用DGMGRL配置)。但是这一章最好还是要防一手,假设你的GC没搭建起来,全程用命令行+DGMGRL配置起来也得会弄才行。具体这个可以参考我的文章:OCM 12C升级考试-手工搭建DATA GUARD和配置DG BROKER进行SWITCH OVER和FAIL OVER […]
Post a Comment