本实验主要是测试在同一台
LINUX机器上配置dataguard的物理备库。这次实验的环境是主库在ASM, 备库准备创建在OS的文件系统上。
下面是主库 和 备库的相关名称
1、准备主数据库用于创建备用数据库
1)查看主库是否已经开启归档模式
SQL> archive log list;
如果没有开启归档模式,则执行下面的语句
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
2)查看数据库是否已经启用强日志模式
SQL> select force_logging from v$database;
如果返回的是 “no”, 则执行下面的语句
SQL> alter database force logging;
2、修改主库参数文件
1)主库通过spfile 创建 pfile 文件
SQL> create pfile from spfile;
2)修改主库的参数pfile 文件(在$ORACLE_HOME/dbs/initorclpr.ora)
3、创建主库主库的密码文件
移除 $ORACLE_HOME/dbs 目录下面的orapworclpr文件([oracle@LINUX10 dbs]$ rm -f orapworclpr),再重新创建密码文件
[oracle@LINUX10 dbs]$ orapwd file=/u01/app/oracle/product/11.2.3/oracle/dbs/orapworclpr password='oracle' entries=20;
4、为备库创建控制文件
5、文件拷贝
由于这里是主库和备库都是公共使用的"$ORACLE_HOME/dbs" 目录,所以只需要在该目录进行参数文件和密码文件的拷贝即可
1)拷贝参数文件
[oracle@LINUX10 dbs]$ cp initorclpr.ora initorcldg.ora
2)拷贝密码文件
[oracle@LINUX10 dbs]$ cp orapworclpr orapworcldg
3)由于控制文件拷贝
[oracle@LINUX10 dbs]$ cp /u01/app/oracle/oradata/orcldg/control01.ctl /u01/app/oracle/oradata/orcldg/control02.ctl
6、修改备库的参数文件 initorcldg.ora
7、启动备库到mount状态
8、配置监听 和 tnsname
1)配置监听
我这里主库和备库使用的是同一个监听, 配置完成后,重新启动监听
2)配置tnsnames.ora文件
9、在主库添加备库日志组
注意备库的standby日志组数,必须比主库的在线日志数多一个。
用下面的sql检查主库的在线日志文件的组数standby logfile的组数比下面的结果多一组
select distinct group# from v$logfile where type='ONLINE';
使用下面的sql语句检查在线日志文件的大小,standby logfile 应该等于下面的结果
select max(bytes)/1024/1024 from v$log;
10、重启备库到 nomount 状态
11、创建主库的spfile,并重启主库
12、在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)
13、备库打开应用日志
主备切换的相关语句
下面是主库 和 备库的相关名称
parameter name | db_name | ORACLE_SID | db_unique_name | service_names |
Primary database | orclpr | orclpr | orclpr | orclpr, orcl |
Standby database | orclpr | orcldg | orcldg | orcldg |
1、准备主数据库用于创建备用数据库
1)查看主库是否已经开启归档模式
SQL> archive log list;
如果没有开启归档模式,则执行下面的语句
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
2)查看数据库是否已经启用强日志模式
SQL> select force_logging from v$database;
如果返回的是 “no”, 则执行下面的语句
SQL> alter database force logging;
2、修改主库参数文件
1)主库通过spfile 创建 pfile 文件
SQL> create pfile from spfile;
2)修改主库的参数pfile 文件(在$ORACLE_HOME/dbs/initorclpr.ora)
orclpr.__db_cache_size=423624704 orclpr.__java_pool_size=4194304 orclpr.__large_pool_size=4194304 orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orclpr.__pga_aggregate_target=209715200 orclpr.__sga_target=633339904 orclpr.__shared_io_pool_size=0 orclpr.__shared_pool_size=192937984 orclpr.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orclpr/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+FRA/ORCLPR/control01.ctl','+FRA/ORCLPR/control02.ctl' *.db_block_size=8192 *.db_create_file_dest='+FRA' *.db_domain='' *.db_name='orclpr' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.service_names='orclpr','orcl' *.sga_target=631242752 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orclpr LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclpr,orcldg)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLPR/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclpr' LOG_ARCHIVE_DEST_2='SERVICE=orcldg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcldg DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/datafile' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcldg','+FRA/orclpr/onlinelog' STANDBY_FILE_MANAGEMENT=AUTO |
3、创建主库主库的密码文件
移除 $ORACLE_HOME/dbs 目录下面的orapworclpr文件([oracle@LINUX10 dbs]$ rm -f orapworclpr),再重新创建密码文件
[oracle@LINUX10 dbs]$ orapwd file=/u01/app/oracle/product/11.2.3/oracle/dbs/orapworclpr password='oracle' entries=20;
4、为备库创建控制文件
- SQL> shutdow immediate;
- SQL> startup mount;
- SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/orcldg/control01.ctl';
- SQL> alter database open;
由于这里是主库和备库都是公共使用的"$ORACLE_HOME/dbs" 目录,所以只需要在该目录进行参数文件和密码文件的拷贝即可
1)拷贝参数文件
[oracle@LINUX10 dbs]$ cp initorclpr.ora initorcldg.ora
2)拷贝密码文件
[oracle@LINUX10 dbs]$ cp orapworclpr orapworcldg
3)由于控制文件拷贝
[oracle@LINUX10 dbs]$ cp /u01/app/oracle/oradata/orcldg/control01.ctl /u01/app/oracle/oradata/orcldg/control02.ctl
6、修改备库的参数文件 initorcldg.ora
orclpr.__db_cache_size=423624704 orclpr.__java_pool_size=4194304 orclpr.__large_pool_size=4194304 orclpr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orclpr.__pga_aggregate_target=209715200 orclpr.__sga_target=633339904 orclpr.__shared_io_pool_size=0 orclpr.__shared_pool_size=192937984 orclpr.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orclpr/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/oradata/orcldg/control02.ctl' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata/orcldg' *.db_domain='' *.db_name='orclpr' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclprXDB)' *.open_cursors=300 *.pga_aggregate_target=209715200 *.processes=150 *.service_names='orcldg' *.sga_target=631242752 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcldg LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldg,orclpr)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg' LOG_ARCHIVE_DEST_2='SERVICE=orclpr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orclpr DB_FILE_NAME_CONVERT='+FRA/orclpr/datafile','/u01/app/oracle/oradata/orcldg' LOG_FILE_NAME_CONVERT='+FRA/orclpr/onlinelog','/u01/app/oracle/oradata/orcldg' STANDBY_FILE_MANAGEMENT=AUTO |
7、启动备库到mount状态
- [oracle@LINUX10 archivelog]$ export ORACLE_SID=orcldg
- [oracle@LINUX10 archivelog]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.3.0 Production on Sun May 31 00:50:30 2015
-
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
- Connected to an idle instance.
-
- SQL> create spfile from pfile;
- SQL> startup nomount;
- SQL> alter database mount
1)配置监听
我这里主库和备库使用的是同一个监听, 配置完成后,重新启动监听
- [oracle@LINUX10 admin]$ vi listener.ora
-
- # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/listener.ora
- # Generated by Oracle configuration tools.
-
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = orclpr)
- (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
- (SID_NAME = orclpr)
- )
- (SID_DESC =
- (GLOBAL_DBNAME = orcldg)
- (ORACLE_HOME = /u01/app/oracle/product/11.2.3/oracle)
- (SID_NAME = orcldg)
- )
- )
-
- LISTENER =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
- )
-
- ADR_BASE_LISTENER = /u01/app/oracle
2)配置tnsnames.ora文件
- [oracle@LINUX10 admin]$ vi tnsnames.ora
-
- # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.3/oracle/network/admin/tnsnames.ora
- # Generated by Oracle configuration tools.
-
- ORCLPR =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = orclpr)
- )
- )
-
- ORCL =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = orcl)
- )
- )
-
- ORCLDG =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX10)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = orcldg)
- )
- )
注意备库的standby日志组数,必须比主库的在线日志数多一个。
用下面的sql检查主库的在线日志文件的组数standby logfile的组数比下面的结果多一组
select distinct group# from v$logfile where type='ONLINE';
使用下面的sql语句检查在线日志文件的大小,standby logfile 应该等于下面的结果
select max(bytes)/1024/1024 from v$log;
- SQL> alter database add standby logfile group 20('+FRA') size 50M;
- SQL> alter database add standby logfile group 21('+FRA') size 50M;
- SQL> alter database add standby logfile group 22('+FRA') size 50M;
- SQL> alter database add standby logfile group 23('+FRA') size 50M;
10、重启备库到 nomount 状态
11、创建主库的spfile,并重启主库
- SQL> shutdown immediate
- SQL> create spfile from pfile;
- SQL> startup
- [oracle@LINUX10 admin]$ rman target sys/oracle@orclpr auxiliary sys/oracle@orcldg
-
- Recovery Manager: Release 11.2.0.3.0 - Production on Sun May 31 01:23:32 2015
-
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
- connected to target database: ORCLPR (DBID=542685180)
- connected to auxiliary database: ORCLPR (not mounted)
-
- RMAN> duplicate target database for standby nofilenamecheck from active database
- SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
- -- 或者
- SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
-
- --备库以只读方式打开
- SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
- SQL> ALTER DATABASE OPEN read only;
- SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
主备切换的相关语句
- --前提是备库的参数文件中的 log_archive_dest_n进行了相关的设置,且db_file_name_convert,log_file_name_convert,fal_server 参数也进行了相关的设置。
-
- --查询主备是日志是否有缺口(主备都执行)
- select to_char(scn_to_timestamp(current_scn),\'yyyy-mm-dd hh24:mi:ss\') from v$database;
- select * from v$archive_gap;
-
- --在当前的主库上
- SQL> alter database commit to switchover to physical standby with session shutdown;
- SQL> shutdown immediate;
- SQL> startup mount;
- SQL> alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;
-
- --在备库上
- SQL> alter database commit to switchover to primary;
- SQL> shutdown immediate;
- SQL> startup
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1689999/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30150152/viewspace-1689999/