结合BROKER创建DG
本人懒，不喜欢记东西，DG那么多参数，好JB难记
结合BROKER创建DG，不用在去记那些参数，不用看文档，真傻瓜
1.先要配置好DGMGRL用的监听,还有TNS
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary_DGMGRL)
(SID_NAME = primary)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME = primary)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
-----------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL)
(SID_NAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME = stby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
----------------------------------------------------------
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby)
)
)
***************************************************************************************
2.参数
--备份主库
RMAN> backup format '/tmp/%U' database;
--备份同时，准备备库需要的东西
--主库(建库默认的)
NAME VALUE
------------------------------ --------------------------------------------------
processes 150
nls_territory AMERICA
sga_target 205520896
control_files /dev/raw/raw2, /dev/raw/raw3
db_block_size 8192
compatible 10.2.0.1.0
db_file_multiblock_read_count 16
undo_management AUTO
undo_tablespace UNDOTBS1
remote_login_passwordfile EXCLUSIVE
db_domain
dispatchers (PROTOCOL=TCP) (SERVICE=primaryXDB)
job_queue_processes 10
background_dump_dest /u01/app/oracle/admin/primary/bdump
user_dump_dest /u01/app/oracle/admin/primary/udump
core_dump_dest /u01/app/oracle/admin/primary/cdump
audit_file_dest /u01/app/oracle/admin/primary/adump
db_name primary
open_cursors 300
pga_aggregate_target 68157440
--配置DG参数
processes=150
nls_territory=AMERICA
sga_target=205520896
control_files=/dev/raw/raw2, /dev/raw/raw3
db_block_size=8192
compatible=10.2.0.1.0
db_file_multiblock_read_count=16
undo_management=AUTO
undo_tablespace=UNDOTBS1
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=primaryXDB)"
job_queue_processes=10
background_dump_dest=/u01/app/oracle/admin/primary/bdump
user_dump_dest=/u01/app/oracle/admin/primary/udump
core_dump_dest=/u01/app/oracle/admin/primary/cdump
audit_file_dest=/u01/app/oracle/admin/primary/adump
db_name=primary
open_cursors=300
pga_aggregate_target=68157440
--只需要加个db_unique_name
db_unique_name=stby
--生成STANDBY控制文件，FTP到备库，移动在指定位置
alter database create standby controlfile as '/tmp/stby.ctl';
--启动备库
SQL> create spfile from pfile='/tmp/init.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 67112016 bytes
Database Buffers 134217728 bytes
Redo Buffers 2924544 bytes
SQL> alter database mount;
Database altered.
--开启broker (主备库)
alter system set dg_broker_start=true;
***************************************************************************************
3.备份的同时，配置BROKER
DGMGRL> create configuration dgcfg1 as primary database is primary connect identifier is primary;
Configuration "dgcfg1" created with primary database "primary"
DGMGRL> add database stby as connect identifier is standby maintained as physical;
Database "stby" added
DGMGRL> edit database primary set property StandbyArchiveLocation='/u01/app/oracle/arch/';
Property "standbyarchivelocation" updated
DGMGRL> edit database stby set property StandbyArchiveLocation='/u01/app/oracle/arch/';
Property "standbyarchivelocation" updated
DGMGRL> ENABLE CONFIGURATION;
Enabled.
--主库
Mon Aug 2 23:56:41 2010
ALTER SYSTEM SET log_archive_config='dg_config=(stby)' SCOPE=BOTH;
RSM0 started with pid=23, OS id=28791
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET standby_archive_dest='' SCOPE=BOTH SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM ARCHIVE LOG
Mon Aug 2 23:57:29 2010
Thread 1 advanced to log sequence 60 (LGWR switch)
Current log# 2 seq# 60 mem# 0: /dev/raw/raw6
Current log# 2 seq# 60 mem# 1: /dev/raw/raw7
Mon Aug 2 23:57:51 2010
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Mon Aug 2 23:57:51 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
--备库
Mon Aug 2 23:20:37 2010
Starting Data Guard Broker (DMON)
INSV started with pid=19, OS id=28062
Mon Aug 2 23:21:17 2010
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
Mon Aug 2 23:21:33 2010
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/10.2.0/db_1/dbs/arch
NSV0 started with pid=22, OS id=28072
RSM0 started with pid=23, OS id=28076
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest='' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_duplex_dest='' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest_1='location=dgarc2 mandatory valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_dest_2='location="/u01/app/oracle/arch/"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET standby_archive_dest='/u01/app/oracle/arch/' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=primary_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_config='dg_config=(primary)' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:26:01 2010
Attempt to start background Managed Standby Recovery process (stby)
MRP0 started with pid=24, OS id=28078
Mon Aug 2 23:26:01 2010
MRP0: Background Managed Standby Recovery process started (stby)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Mon Aug 2 23:26:06 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28078.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:26:06 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28078.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:26:06 2010
MRP0: Background Media Recovery process shutdown (stby)
Mon Aug 2 23:26:07 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:26:22 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 28080
RFS[1]: Identified database type as 'physical standby'
Mon Aug 2 23:26:22 2010
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: '/u01/app/oracle/arch/1_59_726013115.dbf'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 28082
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Archived Log: '/u01/app/oracle/arch/1_60_726013115.dbf'
RFS[2]: Archived Log: '/u01/app/oracle/arch/1_58_726013115.dbf'
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_dest_2='location="/u01/app/oracle/arch/"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET standby_archive_dest='/u01/app/oracle/arch/' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=primary_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:27:16 2010
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:27:16 2010
Attempt to start background Managed Standby Recovery process (stby)
MRP0 started with pid=25, OS id=28084
Mon Aug 2 23:27:16 2010
MRP0: Background Managed Standby Recovery process started (stby)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Mon Aug 2 23:27:21 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28084.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:27:21 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28084.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:27:21 2010
MRP0: Background Media Recovery process shutdown (stby)
Mon Aug 2 23:27:22 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:27:47 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 28086
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Archived Log: '/u01/app/oracle/arch/1_61_726013115.dbf'
***************************************************************************************
4.做次恢复
RMAN> catalog backuppiece '/tmp/01lkcbo0_1_1';
RMAN> catalog backuppiece '/tmp/02lkcbql_1_1';
RMAN> restore database;
RMAN> delete backup;
***************************************************************************************
5.重新启用下配置
DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
Enabled.
***************************************************************************************
6.测试switchover
DGMGRL> switchover to standby;
Site "standby" was not found
DGMGRL> switchover to stby;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primary" on database "primary"
Shutting down instance "primary"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primary" on database "primary"
Starting instance "primary"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stby"
DGMGRL> switchover to primary
Performing switchover NOW, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "primary" on database "primary"
Shutting down instance "primary"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "primary" on database "primary"
Starting instance "primary"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "primary"
DGMGRL>
***************************************************************************************
7.切换到最大可用模式
--第二步创建STANDBY CONTROLFILE的时候，会自动生成创建STANDBY LOGFILE的脚本
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 26214400;
--按照规划进行修改
--主库
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw17','/dev/raw/raw18') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw19','/dev/raw/raw20') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw21','/dev/raw/raw22') SIZE 26214400;
--备库
SQL> shutdown immediate
SQL> startup mount
--这步要快，broker要给你启动recover状态，等recover起来了就加不了了，当然，CANCEL掉RECOVER也可以，但是我记不住命令
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw17','/dev/raw/raw18') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw19','/dev/raw/raw20') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw21','/dev/raw/raw22') SIZE 26214400;
--切换到最大可用
DGMGRL> edit database primary set property LogXptMode ='SYNC';
DGMGRL> edit database stby set property LogXptMode ='SYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
***************************************************************************************
8.总结
就是懒，不想记东西
这样做不用翻什么文档，不用记什么命令，不用记参数，简单高效
当然，也可以把STNDBY恢复好了在去配置BROKER，也不用敲什么命令
Duplicate我也没怎么用不，不如弄个控制文件过来恢复方便
结合BROKER创建DG，不用在去记那些参数，不用看文档，真傻瓜
1.先要配置好DGMGRL用的监听,还有TNS
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary_DGMGRL)
(SID_NAME = primary)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME = primary)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
-----------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL)
(SID_NAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SERVICE_NAME = stby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
----------------------------------------------------------
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby)
)
)
***************************************************************************************
2.参数
--备份主库
RMAN> backup format '/tmp/%U' database;
--备份同时，准备备库需要的东西
--主库(建库默认的)
NAME VALUE
------------------------------ --------------------------------------------------
processes 150
nls_territory AMERICA
sga_target 205520896
control_files /dev/raw/raw2, /dev/raw/raw3
db_block_size 8192
compatible 10.2.0.1.0
db_file_multiblock_read_count 16
undo_management AUTO
undo_tablespace UNDOTBS1
remote_login_passwordfile EXCLUSIVE
db_domain
dispatchers (PROTOCOL=TCP) (SERVICE=primaryXDB)
job_queue_processes 10
background_dump_dest /u01/app/oracle/admin/primary/bdump
user_dump_dest /u01/app/oracle/admin/primary/udump
core_dump_dest /u01/app/oracle/admin/primary/cdump
audit_file_dest /u01/app/oracle/admin/primary/adump
db_name primary
open_cursors 300
pga_aggregate_target 68157440
--配置DG参数
processes=150
nls_territory=AMERICA
sga_target=205520896
control_files=/dev/raw/raw2, /dev/raw/raw3
db_block_size=8192
compatible=10.2.0.1.0
db_file_multiblock_read_count=16
undo_management=AUTO
undo_tablespace=UNDOTBS1
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=primaryXDB)"
job_queue_processes=10
background_dump_dest=/u01/app/oracle/admin/primary/bdump
user_dump_dest=/u01/app/oracle/admin/primary/udump
core_dump_dest=/u01/app/oracle/admin/primary/cdump
audit_file_dest=/u01/app/oracle/admin/primary/adump
db_name=primary
open_cursors=300
pga_aggregate_target=68157440
--只需要加个db_unique_name
db_unique_name=stby
--生成STANDBY控制文件，FTP到备库，移动在指定位置
alter database create standby controlfile as '/tmp/stby.ctl';
--启动备库
SQL> create spfile from pfile='/tmp/init.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 67112016 bytes
Database Buffers 134217728 bytes
Redo Buffers 2924544 bytes
SQL> alter database mount;
Database altered.
--开启broker (主备库)
alter system set dg_broker_start=true;
***************************************************************************************
3.备份的同时，配置BROKER
DGMGRL> create configuration dgcfg1 as primary database is primary connect identifier is primary;
Configuration "dgcfg1" created with primary database "primary"
DGMGRL> add database stby as connect identifier is standby maintained as physical;
Database "stby" added
DGMGRL> edit database primary set property StandbyArchiveLocation='/u01/app/oracle/arch/';
Property "standbyarchivelocation" updated
DGMGRL> edit database stby set property StandbyArchiveLocation='/u01/app/oracle/arch/';
Property "standbyarchivelocation" updated
DGMGRL> ENABLE CONFIGURATION;
Enabled.
--主库
Mon Aug 2 23:56:41 2010
ALTER SYSTEM SET log_archive_config='dg_config=(stby)' SCOPE=BOTH;
RSM0 started with pid=23, OS id=28791
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET standby_archive_dest='' SCOPE=BOTH SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='primary';
Mon Aug 2 23:57:28 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:57:29 2010
ALTER SYSTEM ARCHIVE LOG
Mon Aug 2 23:57:29 2010
Thread 1 advanced to log sequence 60 (LGWR switch)
Current log# 2 seq# 60 mem# 0: /dev/raw/raw6
Current log# 2 seq# 60 mem# 1: /dev/raw/raw7
Mon Aug 2 23:57:51 2010
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" register net_timeout=180 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Mon Aug 2 23:57:51 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
--备库
Mon Aug 2 23:20:37 2010
Starting Data Guard Broker (DMON)
INSV started with pid=19, OS id=28062
Mon Aug 2 23:21:17 2010
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
Mon Aug 2 23:21:33 2010
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/10.2.0/db_1/dbs/arch
NSV0 started with pid=22, OS id=28072
RSM0 started with pid=23, OS id=28076
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest='' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_duplex_dest='' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest_1='location=dgarc2 mandatory valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
Mon Aug 2 23:26:00 2010
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_dest_2='location="/u01/app/oracle/arch/"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET standby_archive_dest='/u01/app/oracle/arch/' SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='stby';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=primary_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER SYSTEM SET log_archive_config='dg_config=(primary)' SCOPE=BOTH;
Mon Aug 2 23:26:01 2010
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:26:01 2010
Attempt to start background Managed Standby Recovery process (stby)
MRP0 started with pid=24, OS id=28078
Mon Aug 2 23:26:01 2010
MRP0: Background Managed Standby Recovery process started (stby)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Mon Aug 2 23:26:06 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28078.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:26:06 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28078.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:26:06 2010
MRP0: Background Media Recovery process shutdown (stby)
Mon Aug 2 23:26:07 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:26:22 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 28080
RFS[1]: Identified database type as 'physical standby'
Mon Aug 2 23:26:22 2010
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: '/u01/app/oracle/arch/1_59_726013115.dbf'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 28082
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Archived Log: '/u01/app/oracle/arch/1_60_726013115.dbf'
RFS[2]: Archived Log: '/u01/app/oracle/arch/1_58_726013115.dbf'
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_dest_2='location="/u01/app/oracle/arch/"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET standby_archive_dest='/u01/app/oracle/arch/' SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='stby';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=primary_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:27:16 2010
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stby_XPT)(INSTANCE_NAME=stby)(SERVER=dedicated)))' SCOPE=BOTH;
Mon Aug 2 23:27:16 2010
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:27:16 2010
Attempt to start background Managed Standby Recovery process (stby)
MRP0 started with pid=25, OS id=28084
Mon Aug 2 23:27:16 2010
MRP0: Background Managed Standby Recovery process started (stby)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Mon Aug 2 23:27:21 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28084.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:27:21 2010
Errors in file /u01/app/oracle/admin/primary/bdump/stby_mrp0_28084.trc:
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/dev/raw/raw8'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Aug 2 23:27:21 2010
MRP0: Background Media Recovery process shutdown (stby)
Mon Aug 2 23:27:22 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Aug 2 23:27:47 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 28086
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Archived Log: '/u01/app/oracle/arch/1_61_726013115.dbf'
***************************************************************************************
4.做次恢复
RMAN> catalog backuppiece '/tmp/01lkcbo0_1_1';
RMAN> catalog backuppiece '/tmp/02lkcbql_1_1';
RMAN> restore database;
RMAN> delete backup;
***************************************************************************************
5.重新启用下配置
DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
Enabled.
***************************************************************************************
6.测试switchover
DGMGRL> switchover to standby;
Site "standby" was not found
DGMGRL> switchover to stby;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primary" on database "primary"
Shutting down instance "primary"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primary" on database "primary"
Starting instance "primary"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stby"
DGMGRL> switchover to primary
Performing switchover NOW, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "primary" on database "primary"
Shutting down instance "primary"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "primary" on database "primary"
Starting instance "primary"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "primary"
DGMGRL>
***************************************************************************************
7.切换到最大可用模式
--第二步创建STANDBY CONTROLFILE的时候，会自动生成创建STANDBY LOGFILE的脚本
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 26214400;
--按照规划进行修改
--主库
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw17','/dev/raw/raw18') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw19','/dev/raw/raw20') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw21','/dev/raw/raw22') SIZE 26214400;
--备库
SQL> shutdown immediate
SQL> startup mount
--这步要快，broker要给你启动recover状态，等recover起来了就加不了了，当然，CANCEL掉RECOVER也可以，但是我记不住命令
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw17','/dev/raw/raw18') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw19','/dev/raw/raw20') SIZE 26214400;
ALTER DATABASE ADD STANDBY LOGFILE ('/dev/raw/raw21','/dev/raw/raw22') SIZE 26214400;
--切换到最大可用
DGMGRL> edit database primary set property LogXptMode ='SYNC';
DGMGRL> edit database stby set property LogXptMode ='SYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
***************************************************************************************
8.总结
就是懒，不想记东西
这样做不用翻什么文档，不用记什么命令，不用记参数，简单高效
当然，也可以把STNDBY恢复好了在去配置BROKER，也不用敲什么命令
Duplicate我也没怎么用不，不如弄个控制文件过来恢复方便
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-670387/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
HPUX 监听无法动态注册
请登录后发表评论
登录
全部评论
