ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle 10g physical standby database creation

oracle 10g physical standby database creation

原创 Linux操作系统 作者：wisdomone1 时间：2010-01-19 23:27:45 0 删除 编辑

前言：
    主库os version:centos 3.9
    备库os version:rhel5
    oracle version:oracle10.2.0.1
构建物理备库步骤如下:
1.在主库，开启强制日志功能
  alter database force logging;--可从v$database查看
2,主备库皆需有对应的密码文件（对于dg必须配置哟）
  orapwd file=./orapwcentos password=system entries=10 force=y  --orapw+&ORACLE_SID
  orapwd file=./orapwrhel password=system entries=10 force=y
3,主库spfile配置如下
[oracle@localhost dbs]$ more spfilecentos.ora


centos.__db_cache_size=192937984
centos.__java_pool_size=4194304
centos.__large_pool_size=4194304
centos.__shared_pool_size=79691776
centos.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/centos/adump'
*.background_dump_dest='/oracle/admin/centos/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/centos/control01.ctl','/oracle/oradata/centos/control02.ctl','/oracle/oradata/centos/control03.ctl'
*.core_dump_dest='/oracle/admin/centos/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='centos'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=centosXDB)'
*.job_queue_processes=10
*.log_archive_config='dg_config=(centos,rhel)' ---centos与rhel为db_unique_name(for primary and standby db)
*.log_archive_dest_1='location=/oracle/centosarch' ---同下
*.log_archive_dest_2='SERVICE=rhel lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=rhel'  ---此参数可通过vi写入或者alter system动态修改皆可
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30 --归档进程数量
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/centos/udump'


---注：下为用于主库切换为备库的相关参数配置哟
FAL_SERVER=boston  --net service name
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'  --用于路径转换哟，如主备库路径相同，不用此参数
LOG_FILE_NAME_CONVERT=
 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'   --同上
STANDBY_FILE_MANAGEMENT=AUTO  --动态根据主库的动作在备库进行相关的增删操作

 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO


4,如主库未开启归档，配置归档模式
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;


5,对主库的datafile,logfile,controlfile,tempfile作个全备(备时，限制用户访问数据库）
sqlplus sys/system as sysdba
select * from v$datafile;
select * from v$tempfile;
select * from v$log;
select * from v$controlfile;

利用tar，进行对以上内容进行压缩

tar -cvf x.tar 以上相关文件列表内容


6，根据主库构建备库所用控制文件
在主库上，
startup mount
alter database create standby controlfile as '/tmp/rhel.ora';
alter database open;


7,为备库构建如下内容(依主库spfile为蓝本）
create pfile='/tmp/initrhel.ora' from pfile;--主库上
如下：备库pfile内容
-bash-3.1$ more spfilerhel.ora


centos.__db_cache_size=188743680
rhel.__db_cache_size=180355072
centos.__java_pool_size=4194304
rhel.__java_pool_size=4194304
centos.__large_pool_size=4194304
rhel.__large_pool_size=4194304
centos.__shared_pool_size=83886080
rhel.__shared_pool_size=92274688
centos.__streams_pool_size=0
rhel.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/centos/adump'  ---相关dump目录，我是主备库配置一致
*.background_dump_dest='/oracle/admin/centos/bdump'
*.compatible='10.2.0.1.0'
DB_NAME='centos'  --主备库的db_name参数值相同
DB_UNIQUE_NAME='rhel' --备库的db_unique_name不同于db_name（主库上可以不配置这个参数哟）<>
LOG_ARCHIVE_CONFIG='DG_CONFIG=(centos,rhel)'
CONTROL_FILES='/oracle/oradata/centos/control01.ctl','/oracle/oradata/centos/control02.ctl','/oracle/oradata/centos/control03.ctl' --这个备库控制文件，源于create pfile='/tmp/initrhel.ora' from pfile，在备库来个多元镜像就ok
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/oracle/rhelarch
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=rhel'
LOG_ARCHIVE_DEST_2=  ---此参数是用于把备库切换为主库时所用的
 'SERVICE=centos LGWR ASYNC  --log_archive_dest_2中的service值，请注意是netca配置的net service name(一般就是global database name)
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=centos'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=centos  ---net service name(产生归档的库）
FAL_CLIENT=rhel  --同上（接受归档的库）
 

8,如果是windows系统，在备库上配置一个oracle service

oradim -NEW -SID rhel -INTPWD system -STARTMODE manual

9,为主备库创建监听器和tnsnames.ora

主库
[oracle@localhost dbs]$ cd /oracle/product/10.2.0/db_1/network/admin/
[oracle@localhost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RHEL =  --备
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =centos) --service_name是global database name
    )
  )

CENTOS = --主
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = centos)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[oracle@localhost admin]$

[oracle@localhost admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =   --这个小节很重要，我是采用netmgr手工添加
      (GLOBAL_DBNAME = centos)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = centos)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )


--备库
-bash-3.1$ cd /oracle/product/10.2.0/db_1/network/admin/
-bash-3.1$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RHEL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =centos)
    )
  )

CENTOS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = centos)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

-bash-3.1$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  (SID_DESC =
      (GLOBAL_DBNAME=centos)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME=rhel)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

-bash-3.1$

以上配置完，记得用tnsping net service name测试，及sqlplus sys/system@net service name as sysdba测试(在主备库）


10,在备库上,以mount方式加载备库
export ORACLE_SID=rhel --if being rhel or unix os
startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;--备库日志恢复应用

11,检测备库日志应用
Step 1   Identify the existing archived redo log files.

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

3 rows selected.

Step 2   Force a log switch to archive the current online redo log file.

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3   Verify the new redo data was archived on the standby database.

On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
        11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
4 rows selected.

The archived redo log files are now available to be applied to the physical standby database.

Step 4   Verify new archived redo log files were applied.

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
  2  ORDER BY SEQUENCE#;

SEQUENCE# APP
--------- ---
        8 YES
        9 YES
       10 YES
       11 YES

4 rows selected.

 

12,如果要对物理备库进行查询，须先取消备库日志应用恢复，然后以只读方式打开备库

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel immediate;
alter database open read only;
13,当然，可以从第12步还原回备库日志应用模式


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;--备库日志恢复应用

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-625524/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 为物理备库添加standby logfile group相关事则
下一篇： oracle 10g logical standby db creation
请登录后发表评论 登录
全部评论
wisdomone1
提供针对oracle初学者及进阶的数据库培训，欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历： 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间：2008-04-04

  • 博文量
    2189
  • 访问量
    12689906

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593