oracle10g r2,oracle10G R2 dataguard

最新推荐文章于 2022-06-02 10:13:36 发布

weixin_39605347

最新推荐文章于 2022-06-02 10:13:36 发布

阅读量85

点赞数

文章标签： oracle10g r2

本文详细指导了如何在RHEL4.5系统中为Oracle 10g数据库配置DataGuard物理备库，包括检查支持、设置初始化参数、备份主库、创建备库、配置网络服务名及归档策略，最终完成备库的创建和主备库间的同步。

摘要由CSDN通过智能技术生成

RHEL4.5中安装Oracle10g DG物理备库原文出处

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

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