使用DGMGRL管理DataGuard-创新互联

1．环境：Primary Database: ning
Physical Standby Database: test
2．修改初始化参数dg_broker_start
SQL> alter system set dg_broker_start=true;
3．确认数据库使用的是spfile启动

4．启动DGMGRL
C:>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
5．连接到Primary库DGMGRL> connect
Username: sys@ning
Password:
Connected.

6．创建配置
DGMGRL> create configuration 'DGTEST' as
> primary database is 'ning'
> connect identifier is ning;
Configuration "DGTEST" created with primary database "ning"
7．添加备用库
DGMGRL> add database 'test'
> as connect identifier is test
> maintained as physical;
Database "test" added
8．查看配置DGMGRL> show configuration

Configuration
Name: DGTEST
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ning - Primary database
test - Physical standby database

Current status for "DGTEST": DISABLED
9．设置数据库属性
DGMGRL>edit database 'ning' set property 'LogArchiveFormat' = 'log_%t_%s_%r_%d.arc';
Property "LogArchiveFormat" updated
10．显示数据库属性
DGMGRL> show database verbose 'ning'

Database
Name: ning
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s):
ning

Properties:
InitialConnectIdentifier = 'ning'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'dgmis'
SidName = 'ning'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=dgmis)(PORT=
1521))'
StandbyArchiveLocation = 'e:oraclearch3ning'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'log_%t_%s_%r_%d.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "ning":
DISABLED

11．启用配置
DGMGRL> enable configuration
Enabled.

DGMGRL> enable database 'ning'
Enabled.

DGMGRL> enable database 'test'
Enabled.
12．检查数据库状态
DGMGRL> show database 'ning'

Database
Name: ning
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ning

Current status for "ning":
SUCCESS

DGMGRL> show database 'test'

Database
Name: test
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
test

Current status for "test":
SUCCESS

DGMGRL> show configuration

Configuration
Name: DGTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ning - Primary database
test - Physical standby database

Current status for "DGTEST":
SUCCESS
13．修改Data Guard的保护模式
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.

14．更改备用库到只读状态
DGMGRL> edit database test set state='read-only';
Succeeded.
15．停止主库到从库的日志传送DGMGRL> edit database ning set state='log-transport-off';
Succeeded.
16．将主库离线
DGMGRL> edit database ning set state='offline';
Operation requires shutdown of instance "ning" on database "ning"
Shutting down instance "ning"...
Database closed.
Database dismounted.
ORACLE instance shut down.
注：要将主库重新上线，需要先手动启动数据库，然后
DGMGRL> connect sys@ning
Password:
Connected.
DGMGRL> edit database ning set state='online';
Succeeded.
17．禁用配置
DGMGRL> disable configuration
Disabled.
18．禁用某个备用库DGMGRL> disable database 'test';
Disabled.
19．从配置中删除备用库
DGMGRL> remove database 'test'
Removed database "test" from the configuration
20．删除配置
DGMGRL> remove configuration
Removed configuration.

21．执行Switchover切换
DGMGRL> switchover to 'test';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "ning" on database "ning"
Shutting down instance "ning"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "test" on database "test"
Shutting down instance "test"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ning" on database "ning"
Starting instance "ning"...
ORACLE instance started.
Database mounted.

Operation requires startup of instance "test" on database "test"
ORACLE instance started.
Database mounted
Switchover succeeded. New primary is "test"

DGMGRL> show configuration

Configuration
Name: DGTEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ning - Physical standby database
test - Primary database

Current status for "DGTEST":
SUCCESS
22．执行Failover切换
DGMGRL> FAILOVER TO "test";

23．生成数据库状态报告（如果你遇到Ora-16810时可以使用）
DGMGRL> show database 'ning' 'statusreport'
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT

附录：
ORA-16525: the Data Guard broker is not yet available请检查数据库的初始化参数dg_broker_start是否为true

ORA-16797: database is not using a server parameter file请检查数据库是否以spfile启动

ORA-16792: configuration property value is inconsistent with database setting
检查数据库的某些属性和数据的初始化参数设置是否一致，例如LogArchiveFormat属性和log_archive_format参数的设置是否一致

ORA-16505: site ID is invalid
检查该数据库是否处于enable状态