DataGuard 通过RMAN创建Standby Database

最新推荐文章于 2021-04-08 06:11:48 发布

cnyp2016

最新推荐文章于 2021-04-08 06:11:48 发布

阅读量118

点赞数

文章标签：数据库网络操作系统

DataGuard 通过RMAN创建Standby Database

一、说明

1.环境

操作系统Red Linux Enterprise as 4

数据库版本Oracle 10g Release 2

2.primary databae

IP:10.87.0.198

ORACLE_SID=dbtest

db_unique_name=dbtest

3.standby database

IP:10.87.0.200

ORACLE_SID=standby

db_unique_name=standby

4. 附档为所有需要修改文件的完整版

二、调整primary database为创建standby database做准备

1.enable force logging

SQL> alter database force logging;

保证那些primary database中没有记录在log中的修改能伟到standby database中.

oracle document中有下面一段话

To protect against unlogged direct writes in the primary database that cannot be propagated to the standby database, turn on FORCE LOGGING at the primary database before performing datafile backups for standby creation. Keep the database in FORCE LOGGING mode as long as the standby database is required.

2. 创建密码

如果没有密码文件的话就创建一个.在Data Guard中的每一个数据库都必须有一个密码文件且sys用户的密码必须相同(保证redo伟输成功).

在Linux下你可以用下面的命令创建一个密码文件

orapwd file=$ORACLE_HOME/dbs/orapwdbtest password=sys entries=3

3. 修改primary database的初始化参数

3.1 alter system set log_archive_config='dg_config=(dbtest,standby)' scope=both;

3.2 alter system set log_archive_dest_1='location=/u10/arch/dbtest/ valid_for=(all_logfiles,all_roles) db_unique_name=dbtest' scope=both;

3.3 alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;

3.4 alter system set log_archive_dest_state_2=defer scope=both;

3.5 alter system set fal_server=standby scope=both;

3.6 alter system set fal_client=dbtest scope=both;

3.7 alter system set db_file_name_convert=’standby’,’dbtest’ scope=both alter system set db_file_name_convert='standby','dbtest' scope=spfile ;

3.8 alter system set log_file_name_convert='/u10/arch/standby','/u10/arch/dbtest' scope=spfile;

3.9 alter system set standby_file_management=auto scope=both;

4. 确保数据库运行在archivelog模式下

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u10/arch/dbtest/

Oldest online log sequence 2

Current log sequence 4

可以通过以下方式把数据库调整为archivelog模式下

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog ;

SQL> alter database open;

5. 生成数据库备份

RMAN> backup database plus archivelog;

三、创建standby database

1.生成standby database 的control file

[oracle@test01 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 28 01:44:57 2008

connected to target database: DBTEST (DBID=1048703746)

RMAN> backup current controlfile for standby format'/home/oracle/control.ctl';

Starting backup at 28-MAY-08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 28-MAY-08

channel ORA_DISK_1: finished piece 1 at 28-MAY-08

piece handle=/home/oracle/control.ctl tag=TAG20080528T014543 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-MAY-08

Starting Control File and SPFILE Autobackup at 28-MAY-08

piece handle=/u09/backup/rman/control/c-1048703746-20080528-00 comment=NONE

Finished Control File and SPFILE Autobackup at 28-MAY-08

2. 在standby database 创建pwdfile

orapwd file=$ORACLE_HOME/dbs/orapwstandby password=sys entries=3

密码要与primary database相同哦,否则会归档失败

3. 准备standby database初始化参数

3.1从primary database 生成文本初始化参数文件

create pfile='/home/oracle/initstandby.ora' from spfile;

3.2 修改initstandby.ora

与primary database不同的参数如下:

db_unique_name=standby

control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/oradata/standby/control02.ctl','/u01/app/oracle/oradata/standby/control03.ctl'

db_file_name_convert='dbtest','standby'

log_file_name_convert='/u10/arch/dbtest','/u10/arch/standby'

log_archive_dest_1='location=/u10/arch/standby/ valid_for=(all_logfiles,all_roles) db_unique_name=standby'

log_archive_dest_2='service=dbtest lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbtest'

log_archive_dest_state_2='enable'

fal_client='standby'

fal_server='dbtest'

user_dump_dest='/u01/app/oracle/admin/standby/udump'

core_dump_dest='/u01/app/oracle/admin/standby/cdump

background_dump_dest='/u01/app/oracle/admin/standby/bdump

audit_file_dest='/u01/app/oracle/admin/standby/adump'

4.创建好相应目录，dump文件目录，数据文件目录，归档目录

mkdir –p /u01/app/oracle/oradata/standby/

mkdir –p /u01/app/oracle/admin/standby

mkdir –p /u02/oradata/standby

cd /u01/app/oracle/admin/standby

mkdir udump

mkdir cdump

mkdir bdump

mkdir adump

4. 从primary database copy以下文件到standby database

4.1数据库RMAN备份

备份存放位置要与primary database RMAN备份文件的位置相同.

4.2standby database control file: control.ctl’

控制文件存放位置要位生成standby database controlfile的位置相同，否则duplicate过程中会报以下错：

ORA-19870: error reading backup piece /home/oracle/control.ctl

ORA-19505: failed to identify file "/home/oracle/control.ctl"

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

failover to previous backup

4.3初始化参数文件: initstandby.ora存放位置: $ORACLE_HOME/dbs/

5. 配置standby database的listener.ora 和tnsnames.ora文件(两个文件的详细附檔)

6. 配置primary database的tnsnmaes.ora文件,该文件与standby database的相同.

7. 重起监听并检验是否可以tnsping通

8. 启动standby database到nomount状态

SQL> startup nomount

9. 用RMAN的duplicate功能创建standby database

rman auxiliary /

RMAN> connect target sys/sys@dbtest

RMAN>duplicate target database for standby dorecover;

10. 检查standby database的状态

SQL> select database_role,protection_mode,protection_level from v$database;

11. 修改primary database数据库的log_archive_dest_state_2允许归档

SQL> alter system set log_archive_dest_state_2=enable scope=both;

12. 开始Redo应用

SQL> alter database recover managed standby database disconnect from session;

四、检查standby database是否创建成功

1.在primary database 上切换日志

SQL> alter system switch logfile

2.在primary database上运行下面的语句

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

3. 在standby database上运行下面的语句

SQL> select sequence# ,applied from v$archived_log order by sequence#;

SEQUENCE# APP

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

4 YES

5 YES

6 YES

7 YES

8 YES

9 YES

10 YES

11 YES

12 YES

13 YES

14 YES

SEQUENCE# APP

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

15 YES

16 YES

17 YES

若在上步中的max sequence#在的的app状态为YES说明standby database 成功创建.

五、让standby database以spfile启动

1. SQL> create spfile from pfile;

File created.

2.关闭数据库

SQL> shutdown immedaite

SP2-0717: illegal SHUTDOWN option

SQL> shutdwon immediate

SP2-0734: unknown command beginning "shutdwon i..." - rest of line ignored.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

3.开启数据库

SQL> startup mount

ORACLE instance started.

Total System Global Area 553648128 bytes

Fixed Size 1220508 bytes

Variable Size 155189348 bytes

Database Buffers 390070272 bytes

Redo Buffers 7168000 bytes

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

附檔:

1.primary database 初始化参数 spfiledbtest

dbtest.__db_cache_size=369098752

dbtest.__java_pool_size=4194304

dbtest.__large_pool_size=4194304

dbtest.__shared_pool_size=163577856

dbtest.__streams_pool_size=8388608

*.audit_file_dest='/u01/app/oracle/admin/dbtest/adump'

*.background_dump_dest='/u01/app/oracle/admin/dbtest/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/dbtest/control01.ctl','/u01/app/oracle/oradata/dbtest/control02.ctl','/u01/app/oracle/oradata/dbtest/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/dbtest/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='standby','dbtest'

*.db_name='dbtest'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbtestXDB)'

*.fal_client='DBTEST'

*.fal_server='STANDBY'

*.job_queue_processes=10

*.log_archive_config='dg_config=(dbtest,standby)'

*.log_archive_dest_1='location=/u10/arch/dbtest/ valid_for=(all_logfiles,all_roles) db_unique_name=dbtest'

*.log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='/u10/arch/standby','/u10/arch/dbtest'

*.open_cursors=300

*.pga_aggregate_target=183500800

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=550502400

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/dbtest/udump'

2.standby database 初始化参数initstandby

dbtest.__db_cache_size=377487360

dbtest.__java_pool_size=4194304

dbtest.__large_pool_size=4194304

dbtest.__shared_pool_size=155189248

dbtest.__streams_pool_size=8388608

*.audit_file_dest='/u01/app/oracle/admin/standby/adump'

*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle /oradata/standby/control02.ctl','/u01/app/oracle/oradata/standby/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='dbtest','standby'

*.db_name='dbtest'

*.db_unique_name=standby

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbtestXDB)'

*.fal_client='standby'

*.fal_server='dbtest'

*.job_queue_processes=10

*.log_archive_config='dg_config=(dbtest,standby)'

*.log_archive_dest_1='location=/u10/arch/standby/ valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=dbtest lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbtest'

*.log_archive_dest_state_2='enable'

*.log_file_name_convert='/u10/arch/dbtest','/u10/arch/standby'

*.open_cursors=300

*.pga_aggregate_target=183500800

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=550502400

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/standby/udump'

3.primary database listener.ora

# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = test01)(PORT = 1521))

)

4.standby database listener.ora

# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

5.primary database tnsname.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/n etwork/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DBTEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test01)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dbtest)

)

STANDBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.87.0.200)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

6.standby database tnsname.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DBTEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.87.0.198)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dbtest)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

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

转载于:http://blog.itpub.net/7419833/viewspace-324229/

cnyp2016

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DataGuard 通过RMAN创建Standby Database

DataGuard 通过RMAN创建Standby Database一、说明1.环境操作系统Red Linux Enterprise as 4数据库版本Oracle 10g Release 22.primar...
复制链接

扫一扫