oracle创建dg条件,oracle DG的创建

一、主库的前期准备工作

1.Enable Forced Logging -- 主库设置强制日志

--当前force_logging的设置

SYS@orcl11g> select force_logging from v$database;

FOR

---

NO

--强制日志

SYS@orcl11g> alter database force logging;

SYS@orcl11g> select force_logging from v$database;

FOR

---

YES

2.检查主库的密码文件

[oracle@db253 ~]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ ls orapworcl11g

orapworcl11g

3.设置主库的参数文件

--生成静态参数文件

SYS@orcl11g> create pfile from spfile;

--备份一下静态参数文件

[oracle@db253 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ cp initorcl11g.ora initorcl11g.ora.bak

--修改参数文件，添加dg需要的参数

[oracle@db253 dbs]$ vi initorcl11g.ora

[oracle@db253 dbs]$ cat initorcl11g.ora

orcl11g.__db_cache_size=54525952

orcl11g.__java_pool_size=4194304

orcl11g.__large_pool_size=4194304

orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl11g.__pga_aggregate_target=146800640

orcl11g.__sga_target=276824064

orcl11g.__shared_io_pool_size=0

orcl11g.__shared_pool_size=163577856

orcl11g.__streams_pool_size=8388608

*._allow_resetlogs_corruption=TRUE

*.audit_file_dest='/u01/app/oracle/admin/orcl11g/adump'

*.audit_trail='OS'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl11g/control01.ctl','/u01/app/oracle/oradata/orcl11g/control02.ctl','/u01/app/oracle/oradata/control/control03.ctl'#Restore Controlfile

*.cursor_sharing='FORCE'

*.db_16k_cache_size=20971520

*.db_block_size=8192

*.db_domain='neves.com'

*.db_keep_cache_size=12582912

*.db_name='orcl11g'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.memory_target=422576128

*.nls_date_format='yyyy-mm-dd hh24:mi:ss'

*.open_cursors=300

*.processes=150

*.recyclebin='ON'

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=170

*.undo_tablespace='UNDOTBS2'

db_unique_name=pridb

log_archive_config='dg_config=(pridb,stddb)'

log_archive_dest_1='location=/u01/app/oracle/arch/pridb valid_for=(all_logfiles,all_roles) db_unique_name=pridb'

log_archive_dest_2='service=stddb valid_for=(online_logfiles,primary_role) db_unique_name=stddb'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=4

fal_server=stddb

db_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/orcl11g/'

log_file_name_convert='/u01/app/oracle/oradata/stddb/','/u01/app/oracle/oradata/orcl11g/'

STANDBY_FILE_MANAGEMENT=AUTO

4.给主库增加standby redo logfile

SYS@orcl11g> alter database add standby logfile group 4

2  '/u01/app/oracle/oradata/orcl11g/std_redo04.log' size 100M;

SYS@orcl11g> alter database add standby logfile group 5

2  '/u01/app/oracle/oradata/orcl11g/std_redo05.log' size 100m;

SYS@orcl11g> alter database add standby logfile group 6

2  '/u01/app/oracle/oradata/orcl11g/std_redo06.log' size 100M;

SYS@orcl11g> alter database add standby logfile group 7

2  '/u01/app/oracle/oradata/orcl11g/std_redo07.log' size 100m;

SYS@orcl11g> select group#,status,used from v$standby_log;

GROUP# STATUS           USED

---------- ---------- ----------

4 UNASSIGNED          0

5 UNASSIGNED          0

6 UNASSIGNED          0

7 UNASSIGNED          0

5.主库是否为归档模式

SYS@orcl11g> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/arch2

Oldest online log sequence     35

Next log sequence to archive   37

Current log sequence           37

6.主库一致性停库，并且启动到mount

--一致性停库

SYS@orcl11g> shutdown immediate;

--创建新的spfile

SYS@orcl11g> create spfile from pfile;

--启动主库到mount状态

SYS@orcl11g> startup mount;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1345380 bytes

Variable Size             327157916 bytes

Database Buffers           88080384 bytes

Redo Buffers                6086656 bytes

Database mounted.

7.创建一个备库的控制文件

SYS@orcl11g> alter database create standby controlfile as '/home/oracle/control01.ctl';

二、设置standby database

1.设置一个密码文件

--指定standby database的ORACLE_SID

[oracle@db253 dbs]$ export ORACLE_SID=stddb

--设置standby database的密码文件

[oracle@db253 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ orapwd file=orapwstddb password=oracle

--主库备库的sys用户的密码必须一致，如果出现ora-16191错误

--那么将主库的密码文件复制给备库

[oracle@db253 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ cp orapworcl11g orapwstddb

2.设置stnadby database的参数文件

--拷贝主库的静态参数文件，然后，做相应的修改

[oracle@db253 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ cp initorcl11g.ora initstddb.ora

--修改参数文件

[oracle@db253 dbs]$ cd $ORACLE_HOME/dbs

[oracle@db253 dbs]$ vi initstddb.ora

[oracle@db253 dbs]$ cat initstddb.ora

orcl11g.__db_cache_size=54525952

orcl11g.__java_pool_size=4194304

orcl11g.__large_pool_size=4194304

orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl11g.__pga_aggregate_target=146800640

orcl11g.__sga_target=276824064

orcl11g.__shared_io_pool_size=0

orcl11g.__shared_pool_size=163577856

orcl11g.__streams_pool_size=8388608

*._allow_resetlogs_corruption=TRUE

*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'

*.audit_trail='OS'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/stddb/control01.ctl'

*.cursor_sharing='FORCE'

*.db_16k_cache_size=20971520

*.db_block_size=8192

*.db_domain='neves.com'

*.db_keep_cache_size=12582912

*.db_name='orcl11g'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.memory_target=422576128

*.nls_date_format='yyyy-mm-dd hh24:mi:ss'

*.open_cursors=300

*.processes=150

*.recyclebin='ON'

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=170

*.undo_tablespace='UNDOTBS2'

db_unique_name=stddb

log_archive_config='dg_config=(pridb,stddb)'

log_archive_dest_1='location=/u01/app/oracle/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'

log_archive_dest_2='service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=4

fal_server=pridb

db_file_name_convert='/u01/app/oracle/oradata/orcl11g/','/u01/app/oracle/oradata/stddb/'

log_file_name_convert='/u01/app/oracle/oradata/orcl11g/','/u01/app/oracle/oradata/stddb/'

STANDBY_FILE_MANAGEMENT=AUTO

3.创建相应的路径

[oracle@db253 dbs]$ mkdir /u01/app/oracle/admin/stddb/adump -pv

mkdir: created directory `/u01/app/oracle/admin/stddb'

mkdir: created directory `/u01/app/oracle/admin/stddb/adump'

[oracle@db253 dbs]$ mkdir /u01/app/oracle/oradata/stddb

[oracle@db253 dbs]$ mkdir /u01/app/oracle/arch/stddb

4.启动备库实例

[oracle@db253 dbs]$ export ORACLE_SID=stddb

[oracle@db253 dbs]$ sqlplus / as sysdba

SYS@stddb> startup nomount;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1345380 bytes

Variable Size             255854748 bytes

Database Buffers          159383552 bytes

Redo Buffers                6086656 bytes

SYS@stddb>

三、配置网络

1.主库、备库做静态监听

[oracle@db253 dbs]$ cd $ORACLE_HOME/network/admin

[oracle@db253 admin]$ vi listener.ora

[oracle@db253 admin]$ cat listener.ora

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=db253.neves.com)(PORT=1521))

)

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=pridb.neves.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)

(SID_NAME=orcl11g)

)

(SID_DESC=

(GLOBAL_DBNAME=stddb.neves.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)

(SID_NAME=stddb)

)

)

2.重载监听

[oracle@db253 admin]$ lsnrctl reload

3.配置本地服务名

[oracle@db253 admin]$ cat tnsnames.ora

PRIDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db253.neves.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pridb.neves.com)

)

)

STDDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db253.neves.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stddb.neves.com)

)

)

4.测试网络连接

--tnsping：

[oracle@db253 admin]$ tnsping pridb

[oracle@db253 admin]$ tnsping stddb

--sqlplus测试

[oracle@db253 admin]$ sqlplus sys/oracle@pridb as sysdba

[oracle@db253 admin]$ sqlplus sys/oracle@stddb as sysdba

四、将主库的数据文件，和为备库创建的控制文件拷贝到备库

[oracle@db253 stddb]$ ls

control01.ctl   system01.dbf  tbs_arch01.dbf   temp01.dbf  tsd01.dbf

example01.dbf   tbs02.dbf     tbs_bmr01.dbf    tsa01.dbf   undotbs01.dbf

perf_tbs01.dbf  tbs03.dbf     tbs_catdb01.dbf  tsb01.dbf   undotbs2.dbf

sysaux01.dbf    tbs04.dbf     tbs_tran01.dbf   tsc01.dbf   users01.dbf

[oracle@db253 stddb]$

五、启动备库到mount状态

[oracle@db253 stddb]$ export ORACLE_SID=stddb

[oracle@db253 stddb]$ sqlplus / as sysdba

SYS@stddb> alter database mount;

六、主库open，查看日志传输情况

[oracle@db253 stddb]$ sqlplus sys/oracle@pridb as sysdba

SYS@pridb> alter database open;

--通过alert日志文件，来查看日志传输是否成功

--因为设置了db_unique_name，所以trace文件和alert日志文件的存储位置发生了改变

--查看一下备库的归档位置，是否存在了主库传过来的归档日志

七、应用传输过去的归档日志，同步数据

--备库在mount状态，启动日志应用服务

SYS@stddb> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APPLIED

---------- ---------

37 NO

38 NO

39 NO

40 NO

41 NO

42 NO

43 NO

44 NO

45 NO

46 NO

47 NO

48 NO

49 NO

--应用日志服务

SYS@stddb> alter database recover managed standby database disconnect from session;

--备库打开，需要停止日志应用服务

SYS@stddb> alter database recover managed standby database cancel;

--打开standby database

SYS@stddb> alter database open;

SYS@stddb> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORCL11G   READ ONLY

八、对于Oracle11g的版本，支持ADG ( Active Data Guard)，物理备库可以再open状态下，启动日志应用服务；

SYS@stddb> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORCL11G   READ ONLY

SYS@stddb> alter database recover managed standby database using current logfile disconnect;

Database altered.

SYS@stddb> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORCL11G   READ ONLY WITH APPLY