Chinaunix首页 | 论坛 | 博客
  • 博客访问: 664689
  • 博文数量: 168
  • 博客积分: 2928
  • 博客等级: 中校
  • 技术积分: 1904
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-04 09:56
文章分类

全部博文(168)

文章存档

2010年(168)

我的朋友

分类: Oracle

2010-03-29 09:57:16

RHEL4.5中安装Oracle10g DG物理备库
2008-10-23 18:35
0、规划

Host IP            DB_NAME    DB_UNIQUE_NAME    Net Service Name
主库192.168.137.128    ORCLDB        WENDING        db_wending
备库192.168.137.129    ORCLDB        PHYSTDBY        db_phystdby
保护模式:默认最大性能模式

1、主库准备工作

1.1、检查数据库是否支持Data Guard(企业版才支持),是否归档模式,Enable force logging
$ sqlplus '/as sysdba'
SQL> select * from v$option where parameter = 'Managed Standby';
SQL> archive log list        (先检查是否归档模式,不是则修改)
SQL> alter database force logging;

1.2、如果主库没有密码文件则建立密码文件,从而可以OS验证的方式登陆
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5

1.3、配置standby redolog(最佳性能模式可以忽略,如果将来变成备库且要转为其它两种模式则要建立)
SQL> alter database add standby logfile
    group 4 ('/orahome/oradata/WENDING/stdby_redo04.log') size 100m,
    group 5 ('/orahome/oradata/WENDING/stdby_redo05.log') size 100m,
    group 6 ('/orahome/oradata/WENDING/stdby_redo06.log') size 100m,
    group 7 ('/orahome/oradata/WENDING/stdby_redo07.log') size 100m;
standby redolog的组数参考公式:(online redolog组数 + 1) * 数据库线程数;单机线程数为1,RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。

1.4、设置主库初始化参数
$ sqlplus '/as sysdba'
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(WENDING,PHYSTDBY)' scope=spfile;        (启动db接受或发送redo data,包括所有库的db_unique_name)
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/orahome/arch1/WENDING VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=WENDING' scope=spfile;        (主库归档目的地)
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=db_phystdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PHYSTDBY' scope=spfile;    (当该库充当主库角色时,设置物理备库redo data的传输目的地)
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=spfile;    (最大ARCn进程数)
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;    (允许redo传输服务传输数据到目的地,默认是enable)
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;    (同上)
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;    (exclusive or shared,所有库sys密码要一致,默认是exclusive)
--以下是主库切换为备库,充当备库角色时的一些参数设置,如果不打算做数据库切换就不用设置了
SQL> alter system set FAL_SERVER=db_phystdby scope=spfile;        (配置网络服务名,假如转换为备库角色时,从这里获取丢失的归档文件)
SQL> alter system set FAL_CLIENT=db_wending scope=spfile;        (配置网络服务名,fal_server拷贝丢失的归档文件到这里)
SQL> alter system set DB_FILE_NAME_CONVERT='PHYSTDBY','WENDING' scope=spfile;    (前为切换后的主库路径,后为切换后的备库路径,如果主备库目录结构完全一样,则无需设定)
SQL> alter system set LOG_FILE_NAME_CONVERT='PHYSTDBY','WENDING' scope=spfile;        (同上,这两个名字转换参数是主备库的路径映射关系,可能会是路径全名,看情况而定)
SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;        (auto后当主库的datafiles增删时备库也同样自动操作,且会把日志传送到备库standby_archive_dest参数指定的目录下,确保该目录存在,如果你的存储采用文件系统没有问题,但是如果采用了裸设备,你就必须将该参数设置为manual)
SQL> alter system set STANDBY_ARCHIVE_DEST='LOCATION=/orahome/arch1/WENDING' scope=spfile;    (一般和LOG_ARCHIVE_DEST_1的位置一样,如果备库采用ARCH传输方式,那么主库会把归档日志传到该目录下)
有了以上参数设置,则无论该库充当主库角色还是备库角色都无需再修改了。
然后重启数据库:
SQL> shutdown immediate
SQL> startup;

1.5、备份主库数据文件
关闭应用服务器,停止监听,开始rman备份:
$ lsnrctl stop
$ rman target /
RMAN> backup full database format '/backup/backup_%T_%s_%p.bak';
##RMAN> sql "alter system archive log current";
##RMAN> backup archive log all format='/backup/arch_%T_%s_%p.bak';

1.6、在主库上建立备库控制文件(控制文件通常需要有多份,手工将文件复制几份)
$ sqlplus '/as sysdba'
SQL> alter database create standby controlfile as '/backup/stdby_control01.ctl';
$ cd /backup/
$ cp sdtby_control01.ctl stdby_control02.ctl
$ cp sdtby_control01.ctl stdby_control03.ctl

1.7、为备库准备init参数
$ sqlplus '/as sysdba'
SQL> create pfile = '/backup/initPHYSTDBY.ora' from spfile;
$ cd /backup/
$ vi initPHYSTDBY.ora   
注意主备库不同角色的属性配置,注意文件路径等,注意db_name要和主库一致,主要是以下参数:
audit_file_dest='/u01/app/oracle/admin/PHYSTDBY/adump'
background_dump_dest='/u01/app/oracle/admin/PHYSTDBY/bdump'
core_dump_dest='/u01/app/oracle/admin/PHYSTDBY/bdump'
user_dump_dest='/u01/app/oracle/admin/PHYSTDBY/udump'
--
control_files='/orahome/oradata/stdby_control01.ctl','/orahome/oradata/stdby_control02.ctl','/orahome/oradata/stdby_control03.ctl'
db_unique_name='PHYSTDBY'
log_archive_config='DG_CONFIG=(PHYSTDBY,WENDING)'
log_archive_dest_1='LOCATION=/orahome/arch1/PHYSTDBY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PHYSTDBY'
log_archive_dest_2='SERVICE=db_wending LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=WENDING'
--
fal_client='DB_PHYSTDBY'
fal_server='DB_WENDING'
db_file_name_convert='WENDING','PHYSTDBY'
log_file_name_convert='WENDING','PHYSTDBY'
standby_archive_dest='LOCATION=/orahome/arch1/PHYSTDBY'
另外,如果备库将来要打开成只读模式,需要确认audit_trail参数不是含db,应该设成os或none。

1.8、拷贝上面生成的文件backup_%T.bak、stdby_control01/02/03.ctl、initPHYSTDBY.ora到备库所在主机
注意rman备份的文件在主备库主机上目录要一致。
$ scp backup*.bak 192.168.137.129:/backup/
$ scp initPHYSTDBY.ora 192.168.137.129:$ORACLE_HOME/dbs/
$ scp stdby_control*.ctl 192.168.137.129:/orahome/oradata/

1.9、建立主库监听和主备库的网络服务名(必须是dedicated的),并启动监听
$ netca    (是图形界面,或者手工从别的库把listener.ora和tnsnames.ora拷过来修改也行)
$ lsnrctl start
$ tnsping db_wending
$ tnsping db_phystdby    (此时tnsping还不通物理备库)
tnsping对方的时候,有可能linux防火墙限制了,会提示TNS-12560: TNS: 协议适配器错误。
临时禁用防火墙方法:
# service iptables stop
永久禁用防火墙方法:
# chkconfig --list iptables
# chkconfig --level 345 iptables off

2、建立备库(假设备库已经装了oracle软件,且目录结构和主库一致)

2.1、设置环境变量并建立备库一些必需目录
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export ORACLE_SID=PHYSTDBY
$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
--以下目录要看哪些地方可能会存放数据库文件,注意不能少建
$ mkdir -p /orahome/oradata/$ORACLE_SID
$ mkdir -p /u01/app/oracle/oradata/$ORACLE_SID
$ mkdir -p /orahome/arch1/$ORACLE_SID

2.2、在备库主机上生成密码文件,且sys密码和主库得一致
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5

2.3、在备库上建立监听,和主备库网络服务名(必须是dedicated的),并启动监听
$ netca    (是图形界面,或者手工从别的库把listener.ora和tnsnames.ora拷过来修改也行)
$ lsnrctl start
$ tnsping db_wending
$ tnsping db_phystdby

2.4、在备库上建立spfile
$ sqlplus '/as sysdba'
SQL> create spfile from pfile;
如果pfile没有放到$ORACLE_HOME/dbs/下,而是放在别的位置:
SQL> create spfile from pfile='/backup/initPHYSTDBY.ora';

2.5、启动物理备库
SQL> startup nomount
SQL> alter database mount standby database;

2.6、备库做rman恢复
$ rman target /       (要求主备库rman备份文件的存放路径和文件名一致)
RMAN> restore database;
##RMAN> restore archivelog all;
介质恢复后,rman 自动将standby 数据库打开到mount 状态。

2.7、配置standby redolog(最佳性能模式可以忽略,如果要转为其它两种模式则要建立)
SQL> alter database add standby logfile
    group 4 ('/orahome/oradata/PHYSTDBY/stdby_redo04.log') size 100m,
    group 5 ('/orahome/oradata/PHYSTDBY/stdby_redo05.log') size 100m,
    group 6 ('/orahome/oradata/PHYSTDBY/stdby_redo06.log') size 100m,
    group 7 ('/orahome/oradata/PHYSTDBY/stdby_redo07.log') size 100m;
standby redolog的组数参考公式:(online redolog组数 + 1) * 数据库线程数;单机线程数为1,RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。

2.8、在备库上,启动redo apply
SQL> alter database recover managed standby database disconnect from session;

到此物理备库创建完毕!

3、主备库各参数文件内容

3.1、主备库listener.ora一样,如果有不一样也是host不一样
----------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
)

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

3.2、主备库tnsnames.ora一样,如果有不一样也是host和port不一样
----------------------------------------
DB_WENDING =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.128)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = wending.lk)
    )
)

DB_PHYSTDBY =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = phystdby.lk)
    )
)

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


3.3、init$ORACLE_SID.ora

主库initWENDING.ora:
----------------------------------------
WENDING.__db_cache_size=226492416
WENDING.__java_pool_size=4194304
WENDING.__large_pool_size=4194304
WENDING.__shared_pool_size=96468992
WENDING.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/WENDING/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db','extended'
*.background_dump_dest='/u01/app/oracle/admin/WENDING/bdump'
*.control_files='/orahome/oradata/control1.ctl','/orahome/oradata/control2.ctl','/orahome/oradata/control3.ctl'
*.core_dump_dest='/u01/app/oracle/admin/WENDING/cdump'
*.db_block_size=8192
*.db_domain='LK'
*.db_file_name_convert='PHYSTDBY','WENDING'
*.db_name='ORCLDB'
*.db_unique_name='WENDING'
*.fal_client='DB_WENDING'
*.fal_server='DB_PHYSTDBY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(WENDING,PHYSTDBY)'
*.log_archive_dest_1='LOCATION=/orahome/arch1/WENDING VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=WENDING'
*.log_archive_dest_2='SERVICE=db_phystdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PHYSTDBY'
*.log_archive_max_processes=5
*.log_file_name_convert='PHYSTDBY','WENDING'
*.open_cursors=1500
*.processes=500
*.sga_max_size=320M
*.sga_target=320M
*.standby_archive_dest='LOCATION=/orahome/arch1/WENDING'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/WENDING/udump'
----------------------------------------

备库initPHYSTDBY.ora:
----------------------------------------
PHYSTDBY.__db_cache_size=226492416
PHYSTDBY.__java_pool_size=4194304
PHYSTDBY.__large_pool_size=4194304
PHYSTDBY.__shared_pool_size=96468992
PHYSTDBY.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PHYSTDBY/adump'
*.audit_sys_operations=TRUE
*.audit_trail='os'
*.background_dump_dest='/u01/app/oracle/admin/PHYSTDBY/bdump'
*.control_files='/orahome/oradata/stdby_control01.ctl','/orahome/oradata/stdby_control02.ctl','/orahome/oradata/stdby_control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/PHYSTDBY/cdump'
*.db_block_size=8192
*.db_domain='LK'
*.db_file_name_convert='WENDING','PHYSTDBY'
*.db_name='ORCLDB'
*.db_unique_name='PHYSTDBY'
*.fal_client='DB_PHYSTDBY'
*.fal_server='DB_WENDING'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PHYSTDBY,WENDING)'
*.log_archive_dest_1='LOCATION=/orahome/arch1/PHYSTDBY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PHYSTDBY'
*.log_archive_dest_2='SERVICE=db_wending LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=WENDING'
*.log_archive_max_processes=5
*.log_file_name_convert='WENDING','PHYSTDBY'
*.open_cursors=1500
*.processes=500
*.sga_max_size=320M
*.sga_target=320M
*.standby_archive_dest='LOCATION=/orahome/arch1/PHYSTDBY'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/PHYSTDBY/udump'
----------------------------------------

4、主库归档测试

主库归档前:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /orahome/arch1/WENDING
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

此时备库:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /orahome/arch1/PHYSTDBY
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           8

主库归档后:
SQL> ALTER SYSTEM SWITCH LOGFILE;    --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;    --对数据库中的所有实例执行日志切换
SQL> archive log list;               
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /orahome/arch1/WENDING
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

此时备库:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /orahome/arch1/PHYSTDBY
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           9

5、常用维护SQL

查询当前库的角色和保护模式:
SQL> select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

主库归档:
SQL> ALTER SYSTEM SWITCH LOGFILE;    --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;    --对数据库中的所有实例执行日志切换

在备库上,验证一下传过来的归档文件:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在主库上,查询待转换standby库的归档文件是否连接:
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

如过上面查到存在不连续的归档,那查找sequence 对应的归档文件:
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;

查询主备库已归档文件最大序号是否相同:
SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

在备库上,显示备库相关进程的当前状态信息:
SQL> select process,client_process,sequence#,status from v$managed_standby;

显示归档文件路径配置信息及redo apply情况:
SQL> select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name
from v$archive_dest_status where status='VALID';

检查应用模式(是否启用了实时应用):
如果打开了实时应用,则recovery_mode会显示为:MANAGED REAL TIME APPLY。
SQL> select dest_id,DEST_NAME,STATUS,TYPE,DATABASE_MODE, recovery_mode from v$archive_dest_status;

显示那些被自动触发写入alert.log或服务器trace文件的事件:
通常是在你不便访问到服务器查询alert.log时,可以临时访问本视图查看一些与dataguard 相关的信息。
SQL> select * from v$dataguard_status;

--End--
阅读(1033) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~