database.php.bak,使用duplicate database 创建物理standby database

2.在备库上的操作

2.1 创建参数文件

将步骤1.2生成的主库的参数文件拷贝到$ORACLE_HOME/dbs/下做相应修改,最后修改后的内容如下:

*.db_name=oracl*.db_unique_name=oraclbak

*.log_archive_config='dg_config=(oracl,oraclbak)'

*.log_archive_dest_1=

'location=/u01/app/oracle/duplicate/

valid_for=(all_logfiles,all_roles)

db_unique_name=oraclbak'

*.log_archive_dest_2=

'service=tar_oracl async

valid_for=(online_logfiles,primary_role)

db_unique_name=oracl'

*.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

*.standby_file_management=auto

*.fal_server=tar_oracl

*.fal_client=dup_oracl

*.db_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata

/oracl/'

*.log_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradat

a/oracl/'

2.2 创建spfile

create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora';

2.3 创建密码文件

cd $ORACLE_HOME/dbs

orapwd file=orapworaclbak password=sys entries=10 force=y

2.4 设置ORACLE_SID

修改oracle用户下的.bash_profile添加ORACLE_SID=oraclbak,然后重登陆使该环境变量生效.

2.5.启动数据库到nomount状态

startup nomount

在nomount之前需要创建备库初始化参数中定义的所定义的目录事先创建好,否则的话会报如下错误:

SQL> startup nomount

ORA-09925: Unable to create audit trail file

Linux Error: 2: No such file or directory

Additional information: 9925

2.6 配置tnsnames.ora文件

将主库上的tnsnames.ora文件拷贝到$ORACLE_HOME/NETWORK/ADMIN目录下.

2.7 复制数据库创建standby database

rman target sys/sys@tar_oracl

connect auxiliary

RMAN>run{

duplicate target database for standby dorecover nofilenamecheck;

}

Starting Duplicate Db at 14-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

set until scn  1621751;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-FEB-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/duplicate/ct_t775282456_s97_p1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/duplicate/ct_t775282456_s97_p1 tag=TAG20120215T041414

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06

output filename=/u01/app/oracle/oradata/oracl/control01.ctl

output filename=/u01/app/oracle/oradata/oracl/control02.ctl

output filename=/u01/app/oracle/oradata/oracl/control03.ctl

Finished restore at 14-FEB-12

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

set until scn  1621751;

set newname for tempfile  1 to

"/u01/app/oracle/oradata/oracl/temp01.dbf";

switch clone tempfile all;

set newname for datafile  1 to

"/u01/app/oracle/oradata/oracl/system01.dbf";

set newname for datafile  2 to

"/u01/app/oracle/oradata/oracl/undotbs01.dbf";

set newname for datafile  3 to

"/u01/app/oracle/oradata/oracl/sysaux01.dbf";

set newname for datafile  4 to

"/u01/app/oracle/oradata/oracl/users01.dbf";

set newname for datafile  5 to

"/u01/app/oracle/oradata/oracl/hxl01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/oracl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-FEB-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/oracl/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/oracl/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/oracl/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/oracl/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/oracl/hxl01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/duplicate/df_t775282365_s95_p1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/duplicate/df_t775282365_s95_p1 tag=TAG20120215T041239

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:18

Finished restore at 14-FEB-12

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=22 stamp=775263983 filename=/u01/app/oracle/oradata/oracl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=23 stamp=775263984 filename=/u01/app/oracle/oradata/oracl/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=24 stamp=775263984 filename=/u01/app/oracle/oradata/oracl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=25 stamp=775263984 filename=/u01/app/oracle/oradata/oracl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=26 stamp=775263984 filename=/u01/app/oracle/oradata/oracl/hxl01.dbf

contents of Memory Script:

{

set until scn  1621751;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-12

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=55

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=56

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/duplicate/al_t775282475_s98_p1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/duplicate/al_t775282475_s98_p1 tag=TAG20120215T041426

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06

archive log filename=/u01/app/oracle/duplicate/log1_55_769179320.arc thread=1 sequence=55

channel clone_default: deleting archive log(s)

archive log filename=/u01/app/oracle/duplicate/log1_55_769179320.arc recid=2 stamp=775264007

archive log filename=/u01/app/oracle/duplicate/log1_56_769179320.arc thread=1 sequence=56

channel clone_default: deleting archive log(s)

archive log filename=/u01/app/oracle/duplicate/log1_56_769179320.arc recid=1 stamp=775264007

media recovery complete, elapsed time: 00:00:11

Finished recover at 14-FEB-12

Finished Duplicate Db at 14-FEB-12

2.8 应用Redo日志

alter database recover managed standby database disconnect from session;

3.测试验证

3.1 在备库查看归档日志

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

SEQUENCE# FIRST_TIM NEXT_TIME

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

55 15-FEB-12 15-FEB-12

56 15-FEB-12 15-FEB-12

57 15-FEB-12 15-FEB-12

58 15-FEB-12 15-FEB-12

59 15-FEB-12 15-FEB-12

60 15-FEB-12 15-FEB-12

61 15-FEB-12 15-FEB-12

62 15-FEB-12 15-FEB-12

63 15-FEB-12 15-FEB-12

64 15-FEB-12 15-FEB-12

65 15-FEB-12 15-FEB-12

11 rows selected.

3.2 在主库上切换日志

alter system switch logfile;

3.3 在备库查看归档日志

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

SEQUENCE# FIRST_TIM NEXT_TIME

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

55 15-FEB-12 15-FEB-12

56 15-FEB-12 15-FEB-12

57 15-FEB-12 15-FEB-12

58 15-FEB-12 15-FEB-12

59 15-FEB-12 15-FEB-12

60 15-FEB-12 15-FEB-12

61 15-FEB-12 15-FEB-12

62 15-FEB-12 15-FEB-12

63 15-FEB-12 15-FEB-12

64 15-FEB-12 15-FEB-12

65 15-FEB-12 15-FEB-12

SEQUENCE# FIRST_TIM NEXT_TIME

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

66 15-FEB-12 15-FEB-12

12 rows selected.

归档日志数由11个变成了12个,说明主库上的归档日志已经传送到备库.

3.4 查看备库的日志应用情况

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

SEQUENCE# APP

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

55 YES

56 YES

57 YES

58 YES

59 YES

60 YES

61 YES

62 YES

63 YES

64 YES

65 YES

SEQUENCE# APP

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

66 YES

12 rows selected.

3.5 查看主库的保护模式

sql> select protection_mode from v$database;

protection_mode

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

maximum performance

配置过程总结:

1.使用duplicate创建standby database不用单独创建standby的控制文件,步骤相对简单些.

2.log_archive_dest_2中的service参数中需要填写的是tnsnames.ora文件中配置的别名,而不是服务名.fal_server配置的也是tnsnames.ora文件中配置的别名,而不是服务名(service_name).一开始的时候log_archive_dest_2中的service填写的是服务名,主库的日志文件无法传送到目的库,最后修改为tns中配置的别名就没问题了.

3.如果在主库执行 alter database clear unarchived logfile或alter database open resetlogs ,则dataguard要重建.

4.在连续恢复模式下工作之前，需要保证之前所有的归档日志己经应用到备用库上。因为在连续恢复模式的情况下，oracle不会应用之前的归档日志，而只会应用后面陆续到来的归档日志.

5.出现归档日志gap时，需要找出相应的归档日志，然后将这些归档日志copy到备用节点的standby_archive_dest和log_archive_dest目录下面。需要注意的是log_archive_dest目录下也需要copy。然后ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

6.新建表、表空间、datafile都能通过日志应用到备库，但新建一个临时表空间,rename datafile 均不能应用到备库上.

7.应当实时察看standby库的alert文件，就能清晰明了地知道主备更新的情况。这也是排错的重要方法,切记!!

8.failover和switchover

Failover:将主数据库offline,备用数据库online,这种操作由系统和软件失败引起。 即使在备用数据库上应用重做日志，也可能出现数据丢失的现象，除非备用数据库运行在guaranteed protection 模式。原主数据库重新使用时必须重新启动实例。其它的备用数据库也需重新启动实例。

Switchover:故意将主数据库offline,而将另一备用数据库online,它能够切换到备用数据库而不需同步操作。如：可使用 Switchover 完成系统的平滑升级.即使在备用数据库上不应用重做日志，也不会造成数据的丢失。 数据库不需重新启动实例。这使主数据库几乎能立即在备用数据库上恢复它的功能，因此可经常进行定期维护而不需中断操作。Failover和Switchover的区别为：当Failover发生，备用数据库切换为主数据库之后，它丢失了备用数据库的所有能力，也就是说，不能再返回到备用模式；而Switchover可以，备用数据库可切换为主数据库，也可从主数据库再切换回备用数据库。

9.相关视图

DBA_LOGSTDBY_EVENTS (Logical Standby Databases Only)

DBA_LOGSTDBY_LOG (Logical Standby Databases Only)

DBA_LOGSTDBY_NOT_UNIQUE (Logical Standby Databases Only)

DBA_LOGSTDBY_PARAMETERS (Logical Standby Databases Only)

DBA_LOGSTDBY_PROGRESS (Logical Standby Databases Only)

DBA_LOGSTDBY_SKIP (Logical Standby Databases Only)

DBA_LOGSTDBY_SKIP_TRANSACTION (Logical Standby Databases Only)

DBA_LOGSTDBY_UNSUPPORTED (Logical Standby Databases Only)

V$ARCHIVE_DEST

V$ARCHIVE_DEST_STATUS

V$ARCHIVE_GAP

V$ARCHIVED_LOG

V$DATABASE

V$DATAFILE

V$DATAGUARD_STATUS

V$LOG

V$LOGFILE

V$LOG_HISTORY

V$LOGSTDBY (Logical Standby Databases Only)

V$LOGSTDBY_STATS (Logical Standby Databases Only)

V$MANAGED_STANDBY (Physical Standby Databases Only)

V$STANDBY_LOG

参数说明:

以下两个参数若不做主备库切换,只需要在备库的参数文件中配置即可.

db_file_name_convert='主库目录,备库目录'

如:

db_file_name_convert='/u02/app/oracle/oradata/oracl/', '/u02/app/oracle/oradata/oraclbak/'

在主库目录'/u02/app/oracle/oradata/oracl/下创建的数据文件会自动传输到备库的目录'/u02/app/oracle/oradata/oraclbak/',如主库上创建的数据文件不在'/u02/app/oracle/oradata/oracl/下,则传过来的数据文件会保留在'/u02/app/oracle/oradata/oracl/', 这里有点不明白ORACLE为什么这么处理.

log_file_name_convert='主库目录,备库目录'

--The End--