OCM_Session8_1_Preparing the Primary Database for Standby Database Creation
来源：互联网 发布：mac迅雷提示信任 编辑：程序博客网 时间：2024/05/01 07:33
要求部署物理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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
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>
0 0
- OCM_Session8_1_Preparing the Primary Database for Standby Database Creation
- The logical standby database
- Reinstate Failed Primary Database into Physical Standby
- Standby Database
- Client instance is standby database instead of primary
- Delete archivelog in the primary database
- Flashing Back a Failed Primary Database into a Physical Standby Database
- DG(2)Flashing Back a Failed Primary Database into a Physical Standby Database
- Flashing Back a Failed Primary Database into a Physical Standby Database（12.1）
- Peoplesoft Database creation
- Recover standby database manually
- Tools for Administering the Database
- Primary Database Changes That Require Manual Intervention at a Physical Standby
- OCM_Session8_2_Step-by-Step Instructions for Creating a Physical Standby Database
- Use the physical standby database as a test environment
- ORA-16661: the standby database needs to be reinstated
- Duplicate standby database from active database
- Duplicate standby database from active database
- 今天2014－03－25，阴雨天
- android jsoup 解析 Html
- iPhone: BSD Socket
- 检查日志文件系统
- 黑马程序员之笔记4---异常处理
- OCM_Session8_1_Preparing the Primary Database for Standby Database Creation
- apidemos 实例解析
- 教你OpenERP 7.0 怎样使用本地addons而跳过使用官网模块
- 第五周项目3-对象数组操作长方体类
- C++一点感司
- c3p0连接测试的源码分析
- 关于unsigned long 转化我char[]的问题
- UML系列
- 应用程序内购买类型
