Chinaunix首页 | 论坛 | 博客
  • 博客访问: 25399
  • 博文数量: 35
  • 博客积分: 45
  • 博客等级: 民兵
  • 技术积分: 185
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-26 15:58
文章分类
文章存档

2013年(2)

2012年(33)

我的朋友

分类:

2012-11-01 09:59:05

环境:
OS:Red Hat Linux As 4
DB:10.2.0.4
 
1.在主库上的操作
 
1.1 将主库设置为force logging模式(若数据库不在归档模式下需要将数据库设置为归档模式)
sql>alter database force logging;
 
若没有开启force logging选择,在如下操作中是不会记录redo日志.
alter table hxl.tb_test nologging;
insert/*+ append */ into hxl.tb_objects
select * from dba_objects;
但insert /*+ append */ into hxl.tb_objects values(..)是会写日志的.
 
1.2 创建pfile用于修改相关参数(参数中指定的目录需要事先创建好)
create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora' from spfile;
修改文件initoracl.ora,添加如下内容(db_name,db_unique_name 已经存在的话就不用添加,关键是要添加红色部分)
 
*.db_name=oracl
*.db_unique_name=oracl
*.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=oracl'
*.log_archive_dest_2=
 'service=dup_oracl async
  valid_for=(online_logfiles,primary_role)
  db_unique_name=oraclbak'
*.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=dup_oracl
*.fal_client=tar_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/'

 
1.3 使用刚修改好的参数文件创建spfile
sql>shutdown immediate
sql>create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora';
 
1.4 停掉数据库后使用刚才创建的spfile启动数据库
sql>startup
 
1.5 备份数据库
Rman>run{
allocate channel dup type disk;
backup format '/u01/app/oracle/duplicate/df_t%t_s%s_p%p' database;
backup current controlfile for standby format '/u01/app/oracle/duplicate/ct_t%t_s%s_p%p';
sql 'alter system archive log current';
backup format '/u01/app/oracle/duplicate/al_t%t_s%s_p%p' archivelog all delete input;
release channel dup;
}
 
1.6 配置tnsnames
可以通过netca或是netmgr配置该文件,最后配置的tnsnames.ora文件的内容如下
primary database and standby database using the same tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TAR_ORACL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.43)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracl)
    )
  )
DUP_ORACL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.37)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oraclbak)
    )
  )

1.7 将主库的备份集传送到备库

将备份好的备份集文件传送到备库的/app/oracle/duplicate/下.
这里需要注意下,因为我们是使用duplicate做standby database,在做duplicate database的时候使用的是主库的控制文件,主库的备份集存放路径是/app/oracle/duplicate,所以将主库的备份集传送到备库也需要存放到该路径下,以便主库的控制文件能够识别到.
 
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--
阅读(162) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~