搭建环境:
OS:Solaris10 x86-64
DB:Oracle 11.2.0
主库所在主机:sol01
备库所在主机:sol02
2 主库部署步骤
2.1 主库操作
创建归档目录,开启归档模式。
su – oracle
mkdir /orahome/oracle/archivelog
sqlplus /as sysdba
shutdown immediate
startup mount
alter database archivelog;
查看是否force_logging模式
SQL> select log_mode,force_logging from v$database;
开启force_logging模式
alter database force logging;
create pfile from spfile;
创建备库控制文件
alter database create standby controlfile as '/orahome/oracle/bak/controlfile01.ctl';
创建备库日志组路径
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
cd $ORACLE_HOME/dbs
备份参数文件
cp inittest.ora inittest.ora.bk
2.2 在inittest.ora参数文件中加入:
修改参数文件:
db_unique_name=test
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test'
log_archive_dest_2='service=test_dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test_dg
fal_client=test
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto
2.3 使更改参数生效:
sqlplus / as sysdba
startup nomount pfile= '$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
shutdown immediate
startup mount
2.4 把相应的文件SCP到standby主机:
控制文件:
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl
oracle口令文件:
scp orapwgisdb1 192.168.111.129:/home/oracle/u01/11.2.3/dbhome_1/dbs/orapwgisdb2
oracle数据文件:
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.dbf 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
oracle redo日志文件(不包括standyredo.log)
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.log 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
参数文件
scp $ORACLE_HOME/dbs/inittest.ora 192.168.0.22:$ORACLE_HOME/dbs/
2.5 更改备库使用的pfile:inittest.ora
db_unique_name=test_dg
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test_dg'
log_archive_dest_2='service=test lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test
fal_client=test_dg
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto
2.6 配置监听:
bash-3.00$ more tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
test_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test_dg)
)
)
将tnsnames.ora文件拷贝到备库相同位置。
2.7 打开主库:
Alter database open;
日志如下:
3 从库上操作:
3.1 创建需要的目录
mkdir -p $ORACLE_BASE/oradata/test
mkdir -p $ORACLE_BASE/admin/test/{a,dp}dump
sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
startup nomount
3.2 配置网络服务
启动监听(lsnrctl start),再tnsnames.ora中加入如下内容
bash-3.00$ more tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
test_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test_dg)
)
)
查看tnsping是否互通
将备库启动到mount状态
alter database mount;
添加备库日志组
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
3.3 开启日志应用
alter database recover managed standby database disconnect from session;
相关LOG:
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test)
Thu Sep 24 09:01:44 2015
MRP0 started with pid=26, OS id=3901
MRP0: Background Managed Standby Recovery process started (test)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /orahome/oracle/archivelog/1_79_891106389.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 80 (in transit)
4 测试:
4.1 主库上:
Conn scott/tiger
SQL> create table a as select * from emp;
Table created.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL>
4.2 从库上操作:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
成功完成了同步
5 转换成ADG(active dataguard)
/备库 Database mounted.///
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> SELECT OPEN_MODE FROM V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
6 Swithover 测试:
6.1 检查主从库的信息:
主库上查询:
SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
此处的信息一定要显示是“to standby”
从库信息:
SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
6.2 开始switchover:
-- 主库的执行:
SQL> alter database commit to switchover to physical standby with session shutdown ;
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2228904 bytes
Variable Size 339742040 bytes
Database Buffers 62914560 bytes
Redo Buffers 8486912 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database open;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
--- 从库上操行:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown ;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
---- 在现在的从库上(之前的主库上执行):
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
6.3 切换后 再次测试:
--- 主上执行:
SQL> conn scott/tiger
Connected.
SQL> create table b as select * from tab;
Table created.
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from a;
COUNT(*)
----------
14
SQL> insert into a select * from a;
14 rows created.
SQL> r
1* insert into a select * from a
28 rows created.
SQL> r
1* insert into a select * from a
56 rows created.
SQL> r
1* insert into a select * from a
112 rows created.
SQL> select count(*) from a;
COUNT(*)
----------
224
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
查看主库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
再从库上查询:
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
B TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
6 rows selected.
SQL> r
1* select count(*) from a
COUNT(*)
----------
224
查看备库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
OS:Solaris10 x86-64
DB:Oracle 11.2.0
主库所在主机:sol01
备库所在主机:sol02
2 主库部署步骤
2.1 主库操作
创建归档目录,开启归档模式。
su – oracle
mkdir /orahome/oracle/archivelog
sqlplus /as sysdba
shutdown immediate
startup mount
alter database archivelog;
查看是否force_logging模式
SQL> select log_mode,force_logging from v$database;
开启force_logging模式
alter database force logging;
create pfile from spfile;
创建备库控制文件
alter database create standby controlfile as '/orahome/oracle/bak/controlfile01.ctl';
创建备库日志组路径
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
cd $ORACLE_HOME/dbs
备份参数文件
cp inittest.ora inittest.ora.bk
2.2 在inittest.ora参数文件中加入:
修改参数文件:
db_unique_name=test
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test'
log_archive_dest_2='service=test_dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test_dg
fal_client=test
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto
2.3 使更改参数生效:
sqlplus / as sysdba
startup nomount pfile= '$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
shutdown immediate
startup mount
2.4 把相应的文件SCP到standby主机:
控制文件:
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl
oracle口令文件:
scp orapwgisdb1 192.168.111.129:/home/oracle/u01/11.2.3/dbhome_1/dbs/orapwgisdb2
oracle数据文件:
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.dbf 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
oracle redo日志文件(不包括standyredo.log)
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.log 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
参数文件
scp $ORACLE_HOME/dbs/inittest.ora 192.168.0.22:$ORACLE_HOME/dbs/
2.5 更改备库使用的pfile:inittest.ora
db_unique_name=test_dg
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test_dg'
log_archive_dest_2='service=test lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test
fal_client=test_dg
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto
2.6 配置监听:
bash-3.00$ more tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
test_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test_dg)
)
)
将tnsnames.ora文件拷贝到备库相同位置。
2.7 打开主库:
Alter database open;
日志如下:
3 从库上操作:
3.1 创建需要的目录
mkdir -p $ORACLE_BASE/oradata/test
mkdir -p $ORACLE_BASE/admin/test/{a,dp}dump
sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
startup nomount
3.2 配置网络服务
启动监听(lsnrctl start),再tnsnames.ora中加入如下内容
bash-3.00$ more tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.21 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
test_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test_dg)
)
)
查看tnsping是否互通
将备库启动到mount状态
alter database mount;
添加备库日志组
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
3.3 开启日志应用
alter database recover managed standby database disconnect from session;
相关LOG:
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test)
Thu Sep 24 09:01:44 2015
MRP0 started with pid=26, OS id=3901
MRP0: Background Managed Standby Recovery process started (test)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /orahome/oracle/archivelog/1_79_891106389.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 80 (in transit)
4 测试:
4.1 主库上:
Conn scott/tiger
SQL> create table a as select * from emp;
Table created.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL>
4.2 从库上操作:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
成功完成了同步
5 转换成ADG(active dataguard)
/备库 Database mounted.///
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> SELECT OPEN_MODE FROM V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
6 Swithover 测试:
6.1 检查主从库的信息:
主库上查询:
SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
此处的信息一定要显示是“to standby”
从库信息:
SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
STATUS GAP_STATUS
--------- ------------------------
VALID
VALID NO GAP
6.2 开始switchover:
-- 主库的执行:
SQL> alter database commit to switchover to physical standby with session shutdown ;
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2228904 bytes
Variable Size 339742040 bytes
Database Buffers 62914560 bytes
Redo Buffers 8486912 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database open;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
--- 从库上操行:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown ;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
---- 在现在的从库上(之前的主库上执行):
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
6.3 切换后 再次测试:
--- 主上执行:
SQL> conn scott/tiger
Connected.
SQL> create table b as select * from tab;
Table created.
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from a;
COUNT(*)
----------
14
SQL> insert into a select * from a;
14 rows created.
SQL> r
1* insert into a select * from a
28 rows created.
SQL> r
1* insert into a select * from a
56 rows created.
SQL> r
1* insert into a select * from a
112 rows created.
SQL> select count(*) from a;
COUNT(*)
----------
224
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
查看主库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
再从库上查询:
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
B TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
6 rows selected.
SQL> r
1* select count(*) from a
COUNT(*)
----------
224
查看备库角色
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
DataGuard是一种数据库级别的HA方案,最主要功能是冗灾、数据保护、故障恢复等. 它是在主节点与备用节点间通过日志同步来保证数据的同步,可以实现数据库快速切换与灾难性恢复效率.Data Guard只是在软件上对数据库进行设置,并不需要额外购买任何组件.用户能够在对主数据库影响很小的情况下,实现主备数据库的同步.而主备机之间的数据差异只限于在线日志部分,因此被不少企业用作数据容灾解决方案 ,而RAC一般翻译为"真正应用集群",它一般有两台或者两台以上同构计算机及共享存储设备构成,可提供强大的数据库处理能力差异.简单说dataguard是为了安全而做的自动化备份,RAC是为了提高效率而做的多台电脑共同处理的集群,现在都把两者配合使用.