自己动手配置oracle dataguard
来源:互联网 发布:数据分析相关工作职位 编辑:程序博客网 时间:2024/05/23 13:19
1、在主机上创建新数据库实例mt01,密码mt01mt01
2、修改.bash_profile文件中的SID为正确上面新建实例的名称
修改 /etc/hosts 文件中的地址与机器名对应关系
3、修改主机为归档模式
--获取实例名
SQL> select instance_name from v$instance;
--设置归档文件默认路径
SQL> alter system set log_archive_dest_1='location=/oracle/arch' scope=spfile;
--设置归档文件名格式
SQL> alter system set log_archive_format='MT01_%t_%s_%r.arc' scope=spfile;
--重启数据库使其处于归档模式
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
--强制数据库归档当前日志
SQL> alter system archive log current;
--查看归档目录是否成功生成归档文件
SQL> !ls -l /oracle/arch
-rw-r----- 1 oracle oinstall 858112 Oct 9 16:15 db10g_1_22_814630003.arc
4、设置主机数据库参数
SQL> alter system set standby_archive_dest='/oracle/arch' scope=spfile;
SQL> alter system set standby_file_management=auto scope=spfile;
SQL> alter system set log_archive_dest_2='service=standby_db' scope=spfile;
SQL> alter system set fal_server='primary_db' scope=spfile;
SQL> alter system set fal_client='standby_db' scope=spfile;
5、配置主机数据库 $ORACLE_HOME/network/admin/tnsnames.ora 文件为以下内容
primary_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
standby_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
6、主机RMAN生成数据库文件和归档文件的备份(RMAN备份文件不要和日志文件在同一个目录)
$ rman target /
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt backup;
delete noprompt archivelog all;
backup filesperset 2 database format '/oracle/arch/full_%d_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all format '/oracle/arch/arc_%d_%T_%s_%p' delete input;
}
7、在主机端产生standby controlfile备份
SQL> alter database create standby controlfile as '/oracle/arch/ctl_std';
8、在主机端生成standby pfile文件(pfile就是 $ORACLE_HOME/dbs/ 下的 initmt01.ora 文件)
SQL> create pfile='/oracle/arch/pfile' from spfile;
SQL> create spfile from pfile='/oracle/arch/pfile';
9、把主机端密码文件也备份到 /oracle/arch/ 目录下
$ cp $ORACLE_HOME/dbs/orapwmt01 /oracle/arch/
10、拷贝主机文件到standby主机
$scp /oracle/arch/* 192.168.56.104:/oracle/arch
11、配置standby主机数据库 $ORACLE_HOME/network/admin/tnsnames.ora 文件为以下内容
primary_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
standby_db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mt01)
)
)
12、覆盖standby主机pfile文件 initmt01.ora,修改参数文件
$ cp /oracle/arch/pfile /oracle/app/oracle/product/10.2/db_1/dbs/initmt01.ora
$ vi /oracle/app/oracle/product/10.2/db_1/dbs/initmt01.ora
12.1、创建参数文件中的对应路径下的adump bdump cdump udump目录
12.2、修改参数 log_archive_dest_2 的值为空
SQL> alter system set log_archive_dest_2='' ;
13、覆盖standby主机密码文件
$ cp /oracle/arch/orapwmt01 /oracle/app/oracle/product/10.2/db_1/dbs/orapwmt01
14、恢复standby主机控制文件(重启数据库到nomount状态)
SQL> shutdown immediate
SQL> startup nomount
$ rman target /
RMAN> restore controlfile from '/oracle/arch/ctl_std';
15、恢复standby主机数据库和归档(在mount状态下)
RMAN> alter database mount;
RMAN> run {allocate channel c1 type disk; allocate channel c2 type disk; restore database;}
RMAN> run {allocate channel c1 type disk; allocate channel c2 type disk; recover database;}
16、standby数据库的启动过程
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
17、主库备库切换
17.1、检查主库是否支持switchover操作
SQL> select switchover_status from v$database;
如果switchover_status 为TO STANDBY 则执行
SQL> alter database commit to switchover to physical standby;
若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行
SQL> alter database commit to switchover to physical standby with session shutdown;
修改参数 log_archive_dest_2 的值为空
SQL> alter system set log_archive_dest_2='' scope=both;
17.2、重启主库到备库状态
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
查看数据库是否切换成功
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------- ---------------- ------------------------------
MT01 READ WRITE PHYSICAL STANDBY mt01
17.3、备库检查状态,并转换为主库
SQL> select switchover_status from v$database;
如果 switchover_status 为 TO PRIMARY 则执行
SQL> alter database commit to switchover to primary;
若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行
SQL> alter database commit to switchover to primary with session shutdown;
修改参数 log_archive_dest_2 的值为 service=standby_db
SQL> alter system set log_archive_dest_2='service=standby_db';
启动数据库到open状态
SQL> alter database open;
查看数据库是否切换成功
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- ---------- ---------------- ------------------------------
MT01 READ WRITE PRIMARY mt01
- 自己动手配置oracle dataguard
- 配置Oracle physical DataGuard
- oracle dataguard配置步骤
- Oracle DataGuard 简单配置~
- oracle win2003 dataguard配置
- Oracle dataguard 配置
- Oracle 的dataguard配置
- Oracle数据库DataGuard配置
- Oracle DataGuard 一主多备配置
- Oracle 11g DataGuard 配置
- oracle 11g dataguard配置
- Oracle Dataguard 的配置安装
- oracle dataguard 安装配置手册
- Oracle 11gR2 Dataguard配置
- oracle-11g-配置dataguard
- Oracle dataguard配置Step by Step
- Oracle 10g 数据库DataGuard配置规范
- 配置Oracle 11g Active Dataguard
- CCAnimation创建动画cocos2d-x
- GCD介绍(一): 基本概念和Dispatch Queue
- HTTP请求:GET与POST方法的区别
- 笔试面试题:25匹赛马,5个跑道,每次有5匹马可以同时比赛。问最少比赛多少次可以知道跑得最快的5匹马
- rqnoj-116-质数取石子-dp
- 自己动手配置oracle dataguard
- 禁用屏幕拷贝(Print Screen),调用派生的析构函数及其它
- 面试题-前端-问答题-javascript中字符串格式json如何转化成json对象
- k路归并 - 败者树实现
- Cmdlet学习笔记(1)——Hello Cmdlet
- Oracle RMAN数据库备份脚本
- DFS+思维-PKU Online Judge-1054:Cube
- 发生问题时程序员最常见的 30 种反应
- GCD介绍(二): 多核心的性能