OCM_Session8_1_Preparing the Primary Database for Standby Database Creation

2021-08-09 06:53:23

要求部署物理standby并进行主备切换测试

官方文档：Data Guard Concepts and Administration -> 3 Creating a physical Standby Database 和 7 Role Transitions

注意事项：

（1）建议手工配置DG，手工配置和GC配置不可交叉

（2）不使用real-time (实时)方式启动自动恢复，采用性能最大化模式.LGWR ASYNC

（3）使用LGWR传输日志模式，也可以ARCH传输日志模式

（4）备库启动多个MRP恢复进程，加快日志应用效率

（5）进行两次主备切换

我的环境

OS:Oracle Linux 5.7U

database:10.2.0.1

主库：

ip：192.168.1.155

主库实例名：PROD

hostname:ocm1.localdomain

备库：

ip:192.168.1.156

备库实例名：PRODSTD

hostname：ocm2.localdomain

参考官方文档：

Data Guard Concepts and Administration -> 3 Creating a physical Standby Database 和 7 Role Transitions

http://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm

准备工作：

设置sqlplus命令行提示符样式：

ocm1

[oracle@ocm1 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql

最后一行添加以下内容

set sqlprompt "_user‘@‘_connect_identifier>"

[oracle@ocm1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 25 08:44:19 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SYS@PROD>

SYS@PROD>select instance_name,version,status,archiver,database_status from v$instance;

INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS

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

PROD 10.2.0.1.0 OPEN STARTED ACTIVE

检查数据文件和表空间

SYS@PROD>select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM

/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE

/u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS

SYS@PROD>select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMPTS1 NO NO YES

7 EXAMPLE YES YES YES

10 USERS YES NO YES

6 rows selected.

ocm2也一起设置：

[oracle@ocm2 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql

set sqlprompt "_user‘@‘_connect_identifier>"

这样方便识别那个用户登录了那台数据库。避免混淆。

开始建立物理备库

1.Preparing the Primary Database for Standby Database Creation

Table 3-1 Preparing the Primary Database for Physical Standby Database Creation

Reference	Task
Section 3.1.1	Enable Forced Logging
Section 3.1.2	Create a Password File
Section 3.1.3	Configure a Standby Redo Log
Section 3.1.4	Set Primary Database Initialization Parameters
Section 3.1.5	Enable Archiving

1.1确认归档，打开forced logging

SYS@PROD>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/PROD/Disk2/arch

Oldest online log sequence 7

Next log sequence to archive 11

Current log sequence 11

SYS@PROD> alter database force logging;

Database altered.

SYS@PROD>select NAME,LOG_MODE,FORCE_LOGGING from v$database;

NAME LOG_MODE FOR

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

PROD ARCHIVELOG YES

SYS@PROD>

1.2创建密码文件

之前已经创建orapwPROD了密码文件

[oracle@ocm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/

[oracle@ocm1 dbs]$ ll

total 6740

-rw-rw---- 1 oracle oinstall 1544 Mar 25 08:54 hc_PROD.dat

-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora

-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora

-rw-r--r-- 1 oracle oinstall 658 Mar 18 10:09 initPROD.ora

-rw-rw---- 1 oracle oinstall 24 Mar 18 10:18 lkPROD

-rw-r----- 1 oracle oinstall 5120 Mar 18 09:54 orapwPROD

-rw-r--r-- 1 oracle oinstall 318 Mar 17 20:37 pfile.ora

-rw-r----- 1 oracle oinstall 6832128 Mar 19 19:51 snapcf_PROD.f

-rw-r----- 1 oracle oinstall 3584 Mar 25 08:54 spfilePROD.ora

如果没有需要重新创建，使用以下语句：

[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle

1.3配置Standby Redo Log

a.standby logfile是备库组件，当成为备库时用来接收来自主库的redo日志内容;

b.添加standby logfile大小需要与logfile 大小保持一致，数量最好n+1用于日志缓冲;

①查看主库的日志组和日志成员

主库有5组日志，每组2个成员，文件大小为100M

SYS@PROD>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 9 104857600 2 YES INACTIVE 270814 25-MAR-14

2 1 10 104857600 2 YES INACTIVE 291251 25-MAR-14

3 1 11 104857600 2 NO CURRENT 311895 25-MAR-14

4 1 7 104857600 2 YES INACTIVE 238268 19-MAR-14

5 1 8 104857600 2 YES INACTIVE 244809 19-MAR-14

SYS@PROD>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo01.log NO

2 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo02.log NO

3 ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo03.log NO

1 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo01_1.log NO

2 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo02_1.log NO

3 ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo03_1.log NO

4 ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo04.log NO

4 ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo04_1.log NO

5 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo05.log NO

5 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo05_1.log NO

10 rows selected.

②即添加6组standby logfile

SYS@PROD>alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/PROD/Disk1/standby06.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby06_1.log‘) size 100m;

Database altered.

SYS@PROD>alter database add standby logfile group 7(‘/u01/app/oracle/oradata/PROD/Disk1/standby07.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby07_1.log‘) size 100m;

Database altered.

SYS@PROD>alter database add standby logfile group 8 (‘/u01/app/oracle/oradata/PROD/Disk1/standby08.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby08_1.log‘) size 100m;

Database altered.

SYS@PROD>alter database add standby logfile group 9(‘/u01/app/oracle/oradata/PROD/Disk1/standby09.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby09_1.log‘) size 100m;

Database altered.

SYS@PROD>alter database add standby logfile group 10 (‘/u01/app/oracle/oradata/PROD/Disk1/standby10.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby10_1.log‘) size 100m;

Database altered.

SYS@PROD>alter database add standby logfile group 11 (‘/u01/app/oracle/oradata/PROD/Disk1/standby11.log‘,‘/u01/app/oracle/oradata/PROD/Disk2/standby11_1.log‘) size 100m;

Database altered.

SYS@PROD>select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME

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

6 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

7 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

8 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

9 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

10 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

11 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0

6 rows selected.

SYS@PROD>

1.4 Set Primary Database Initialization Parameters

①创建PROD的pfile，然后修改之

SYS@PROD>show parameter spfile;

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/10.2.0

/db_1/dbs/spfilePROD.ora

SYS@PROD>create pfile from spfile;

File created.

SYS@PROD>!

[oracle@ocm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@ocm1 dbs]$ ll

total 6740

-rw-rw---- 1 oracle oinstall 1544 Mar 25 08:54 hc_PROD.dat

-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora

-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora

-rw-r--r-- 1 oracle oinstall 1163 Mar 25 09:34 initPROD.ora

-rw-rw---- 1 oracle oinstall 24 Mar 18 10:18 lkPROD

-rw-r----- 1 oracle oinstall 5120 Mar 18 09:54 orapwPROD

-rw-r--r-- 1 oracle oinstall 318 Mar 17 20:37 pfile.ora

-rw-r----- 1 oracle oinstall 6832128 Mar 19 19:51 snapcf_PROD.f

-rw-r----- 1 oracle oinstall 3584 Mar 25 08:54 spfilePROD.ora

[oracle@ocm1 dbs]$ cat initPROD.ora

PROD.__db_cache_size=348127232

PROD.__java_pool_size=4194304

PROD.__large_pool_size=4194304

PROD.__shared_pool_size=163577856

PROD.__streams_pool_size=0

*.BACKGROUND_DUMP_DEST=‘/u01/app/oracle/admin/PROD/bdump‘

*.control_files=‘/u01/app/oracle/oradata/PROD/Disk1/control01.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk2/control02.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk3/control03.ctl‘

*.CORE_DUMP_DEST=‘/u01/app/oracle/admin/PROD/cdump‘

*.DB_BLOCK_SIZE=8192

*.DB_CREATE_FILE_DEST=‘/u01/app/oracle/oradata/PROD/Disk1‘

*.DB_CREATE_ONLINE_LOG_DEST_1=‘/u01/app/oracle/oradata/PROD/Disk1‘

*.DB_NAME=‘PROD‘

*.dispatchers=‘(PROTOCOL=TCP)(DISPATCHERS=3)‘--------删除

*.job_queue_processes=15

*.local_listener=‘(ADDRESS = (PROTOCOL=TCP)(HOST=ocm1.localdomain)(PORT=1526))‘-----------删除，确保PRODSTD动态注册到1521端口上

*.LOG_ARCHIVE_DEST_1=‘location=/u01/app/oracle/oradata/PROD/Disk2/arch‘-------删除

*.max_dispatchers=10

*.max_shared_servers=30

*.processes=135

*.sessions=300

*.SGA_TARGET=500M

*.shared_server_sessions=200

*.shared_servers=10

*.UNDO_MANAGEMENT=‘auto‘

*.undo_retention=5400

*.UNDO_TABLESPACE=‘undotbs1‘

*.USER_DUMP_DEST=‘/u01/app/oracle/admin/PROD/udump‘

*.utl_file_dir=‘/home/oracle‘,‘/home/oracle/temp‘,‘/home/oracle/scripts‘

[oracle@ocm1 dbs]$

②修改参数，添加以下内容

##parameter for Primary Database

DB_NAME=PROD

DB_UNIQUE_NAME=PROD

LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(PROD,PRODSTD)‘

CONTROL_FILES=‘/u01/app/oracle/oradata/PROD/Disk1/control01.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk2/control02.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk3/control03.ctl‘

LOG_ARCHIVE_DEST_1=‘location=/u01/app/oracle/oradata/PROD/Disk2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD‘

LOG_ARCHIVE_DEST_2=‘SERVICE=PRODSTD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTD‘

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

##parameter for Standby Database

FAL_SERVER=PRODSTD

FAL_CLIENT=PROD

DB_FILE_NAME_CONVERT=‘PRODSTD‘,‘PROD‘

LOG_FILE_NAME_CONVERT=‘PRODSTD‘,‘PROD‘

STANDBY_FILE_MANAGEMENT=AUTO

[oracle@ocm1 dbs]$ vi initPROD.ora

修改后的pfile：

[oracle@ocm1 dbs]$ cat initPROD.ora

PROD.__db_cache_size=348127232

PROD.__java_pool_size=4194304

PROD.__large_pool_size=4194304

PROD.__shared_pool_size=163577856

PROD.__streams_pool_size=0

*.BACKGROUND_DUMP_DEST=‘/u01/app/oracle/admin/PROD/bdump‘

*.control_files=‘/u01/app/oracle/oradata/PROD/Disk1/control01.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk2/control02.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk3/control03.ctl‘

*.CORE_DUMP_DEST=‘/u01/app/oracle/admin/PROD/cdump‘

*.DB_BLOCK_SIZE=8192

*.DB_CREATE_FILE_DEST=‘/u01/app/oracle/oradata/PROD/Disk1‘

*.DB_CREATE_ONLINE_LOG_DEST_1=‘/u01/app/oracle/oradata/PROD/Disk1‘

*.DB_NAME=‘PROD‘

*.job_queue_processes=15

*.max_dispatchers=10

*.max_shared_servers=30

*.processes=135

*.sessions=300

*.SGA_TARGET=500M

*.shared_server_sessions=200

*.shared_servers=10

*.UNDO_MANAGEMENT=‘auto‘

*.undo_retention=5400

*.UNDO_TABLESPACE=‘undotbs1‘

*.USER_DUMP_DEST=‘/u01/app/oracle/admin/PROD/udump‘

*.utl_file_dir=‘/home/oracle‘,‘/home/oracle/temp‘,‘/home/oracle/scripts‘

##parameter for Primary Database

DB_NAME=PROD

DB_UNIQUE_NAME=PROD

LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(PROD,PRODSTD)‘

CONTROL_FILES=‘/u01/app/oracle/oradata/PROD/Disk1/control01.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk2/control02.ctl‘,‘/u01/app/oracle/oradata/PROD/Disk3/control03.ctl‘

LOG_ARCHIVE_DEST_1=‘location=/u01/app/oracle/oradata/PROD/Disk2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD‘

LOG_ARCHIVE_DEST_2=‘SERVICE=PRODSTD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTD‘

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

##parameter for Standby Database

FAL_SERVER=PRODSTD

FAL_CLIENT=PROD

DB_FILE_NAME_CONVERT=‘PRODSTD‘,‘PROD‘

LOG_FILE_NAME_CONVERT=‘PRODSTD‘,‘PROD‘

STANDBY_FILE_MANAGEMENT=AUTO

[oracle@ocm1 dbs]$

③通过pfile重建spfile

[oracle@ocm1 dbs]$ exit

exit

SYS@PROD>

SYS@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PROD>create spfile from pfile;

File created.

SYS@PROD>

OCM_Session8_1_Preparing the Primary Database for Standby Database Creation,布布扣,bubuko.com

OCM_Session8_1_Preparing the Primary Database for Standby Database Creation

码农公寓

相关文章