需求及场景：单节点实例（即，不是RAC环境），主库和备份库目录结构和ORACLE_SID不一样。
数据库名：PRM 主库实例名：PRM 备库实例名：STD 主备库目录结构一样 可用模式为：最大可用模式
1.主节点备份并生成备用数据库控制文件、standby日志文件、配置可用模式等
设置主节点为force Logging模式(为了双向切换,建议备用节点也设置为force logging模式)
SQL> alter database force logging;
主节点数据库必须是归档模式，查看并设置主节点为归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archives
Oldest online log sequence 115
Next log sequence to archive 117
Current log sequence 117
SQL>
通过如下查询确定
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select group#,member from v$logfile;
如果存在多余的或跨目录的文件将其上删除；
drop tablespace abc including contents and datafiles cascade constraints;
添加备用的logfile group，大小和数量大于online redo log（如果online log 为3
组，则创建standby log 为4 组）【使用lgwr async 方式】
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/PRM/stdredo01.log' size 100m;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/PRM/stdredo02.log' size 100m;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/PRM/stdredo04.log' size 100m;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/PRM/stdredo05.log' size 100m;
查看归档状态：
SQL> archive log list;
---------------------------------------------------------
如果不在归档，则个改为归档模式，如果在归档状态则直接关闭。
改为归档模式的操作：
SQL> alter system set log_archive_dest_1='location=/home/oracle/archives'
SQL> shutdown immediate ;
SQL> startup mount
SQL> alter database archivelog;
SQL> shutdown immediate;
---------------------------------------------------------
关闭数据库并对其进行物理备份
SQL> shutdown immediate
SQL> exit
将数据库的数据库文件，日志文件，spfile,密码文件等通过SCP或FTP方式复制到备份库相关的位置上。
[oracle@dbserv1 ~]$ cd $ORACLE_BASE/oradata/PRM
[oracle@dbserv1 PRM]$ scp *.dbf @dbserv2:/u01/app/oracle/oradata/PRM/
[oracle@dbserv1 PRM]$ scp *.log @dbserv2:/u01/app/oracle/oradata/PRM/
启动主库并生成备库所需的控制文件及初始化参数文件：
SQL> startup mount;
SQL> alter database create standby controlfile as '?/dbs/control01.ctl';
Database altered.
SQL> create pfile from spfile;
File created.
将dataguard 配置设定为最大可用模式（看题目要求）
SQL> alter database set standby database to maximize availability;
SQL> exit
[oracle@dbserv1 oracle]$ cd $ORACLE_HOME/dbs
[oracle@dbserv1 dbs]$ scp initPRM.ora orapwPRM control01.ctl @dbserv2:/u01/app/oracle/db10g/dbs/
oracle@dbserv2's password:
initPRM.ora 100% 762 0.7KB/s 00:00
orapwPRM 100% 2048 2.0KB/s 00:00
control01.ctl 100% 7024KB 6.9MB/s 00:01
2.在备库节点修改initSTD.ora文件
[oracle@dbserv2 ~]$ cd $ORACLE_HOME/dbs
修改控制文件名称及路径(如果和原配置不同)，增加几个参数,修改后如下:
vi initSTD.ora
*.background_dump_dest='/u01/app/oracle/admin/STD/bdump'
*.control_files='/u01/app/oracle/oradata/STD/control01.ctl','/u01/app/oracle/oradata/STD/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/STD/cdump'
*.db_name='PRM'
db_unique_name='STD'
log_archive_config='dg_config=(PRM,STD)'
*.log_archive_dest_1='location=/home/oracle/archives valid_for=(all_logfiles,all_roles) db_unique_name=STD'
*.log_archive_dest_2='service=PRM sync lgwr affirm valid_for=(online_logfiles,primary_role) db_unique_name=PRM '
db_file_name_convert='/u01/app/oracle/oradata/PRM','/u01/app/oracle/oradata/STD'
log_file_name_convert='/u01/app/oracle/oradata/PRM','/u01/app/oracle/oradata/STD'
*.pga_aggregate_target=200m
*.sga_target=512m
*.undo_management='auto'
*.undo_tablespace='undotbs'
*.user_dump_dest='/u01/app/oracle/admin/STD/udump'
将主库拷过来的standby controlfile 复制到对方初始化文件相应的位置：
[oracle@dbserv2 dbs]$ cp control01.ctl /u01/app/oracle/oradata/PRM/
[oracle@dbserv2 dbs]$ cp control01.ctl /u01/app/oracle/oradata/PRM/control02.ctl
3.创建必要的目录
[oracle@dbserv2 oracle]$ cd $ORACLE_BASE/admin
[oracle@dbserv2 admin]$ mkdir STD
[oracle@dbserv2 admin]$ cd STD/
[oracle@dbserv2 PRM]$ ls
[oracle@dbserv2 PRM]$ mkdir bdump cdump udump
4.配置主节点监听器及tnsnames.ora文件
配置后如下:
[oracle@dbserv1 admin]$ cat listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbserv1)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PRM)
(ORACLE_HOME=/u01/app/oracle/db10g)
(SID_NAME=PRM))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/db10g)
(PROGRAM=extproc)))
[oracle@dbserv1 admin]$ cat tnsnames.ora
PRM=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=dbserv1)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PRM)
(SERVER=dedicated)))
std=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=dbserv2)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=STD)
(SERVER=dedicated)))
5.配置备用数据库监听器及tnsnames.ora文件
配置后文件如下:
[oracle@dbserv2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/db10g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STD)
(ORACLE_HOME = /u01/app/oracle/db10g)
(SID_NAME = STD)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserv2.localdomain)(PORT = 1521))
)
[oracle@dbserv2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/db10g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserv1.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRM)
)
)
std =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserv2.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =STD)
)
)
6.在主备节点启动监听并用tnsping测试网络连通性
[oracle@dbserv1 admin]$ lsnrctl start
[oracle@dbserv2 admin]$ lsnrctl start
[oracle@dbserv1 admin]$ tnsping PRM
[oracle@dbserv1 admin]$ tnsping std
[oracle@dbserv2 admin]$ tnsping PRM
[oracle@dbserv2 admin]$ tnsping std
7.启动备用数据库
[oracle@dbserv2 ~]$ export ORACLE_SID=STD
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
8.在主节点设置归档路径相关参数，具体方法有两种，
第一种方法：是直接修改spfile，具体如下：
--------------------------------------------------------------
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
PRM MOUNTED
SQL> alter system set log_archive_config='dg_config=(PRM,STD)'
SQL> alter system set log_archive_dest_2='service=std lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=STD';
System altered.
SQL> alter system set standby_archive_dest='/home/oracle/stdarch';
SQL> alter databse open;
SQL> alter system switch logfile;
System altered.
在备用节点观察日志
---------------------------------------------------------------
第二种方法：关闭数据库，对pfile文件通过vi进行编辑后，再生成spfile，然后启动数据库：
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> exit;
vi initPRM.ora
*.background_dump_dest='/u01/app/oracle/admin/PRM/bdump'
*.control_files='/u01/app/oracle/oradata/PRM/control01.ctl','/u01/app/oracle/oradata/PRM/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/PRM/cdump'
*.db_name='PRM'
db_unique_name='PRM'
log_archive_config='dg_config=(PRM,STD)'
*.log_archive_dest_1='location=/home/oracle/archives valid_for=(all_logfiles,all_roles) db_unique_name=PRM'
*.log_archive_dest_2='service=std lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=STD'
db_file_name_convert='/u01/app/oracle/oradata/STD','/u01/app/oracle/oradata/PRM'
log_file_name_convert='/u01/app/oracle/oradata/STD','/u01/app/oracle/oradata/PRM'
*.pga_aggregate_target=200m
*.sga_target=512m
*.undo_management='auto'
*.undo_tablespace='undotbs'
*.user_dump_dest='/u01/app/oracle/admin/PRM/udump'
SQL> startup;
在备用节点观察日志
9.主备切换
将主库（PRM）改为备库
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220408 bytes
Variable Size 159383752 bytes
Database Buffers 369098752 bytes
Redo Buffers 7168000 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
将备库改为主库
[oracle@dbserv2 ~]$ export ORACLE_SID=STD
[oracle@dbserv2 ~]$ sqlplus / as sysdba
SQL> alter database commit to switchover to PRM;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> create table system.test as
2 select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
在新的备库改为readonly 模式看看同步情况：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from system.test;
COUNT(*)
----------
10227
SQL>
说明同步成功。
10.切换回来：
在dbserv2端：
SQL> alter database commit to switchover to physical standby ;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220432 bytes
Variable Size 155189424 bytes
Database Buffers 377487360 bytes
Redo Buffers 2973696 bytes
SQL> alter databse mount standby database;
alter databse mount standby database
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
在dbserv1端：
SQL> alter database commit to switchover to PRM;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL>
查看服务名称相关参数
主库：
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string PRM
db_unique_name string PRM
global_names boolean FALSE
instance_name string PRM
lock_name_space string
log_file_name_convert string
service_names string PRM
备库：
QL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string PRM
db_unique_name string STD
global_names boolean FALSE
instance_name string PRM
lock_name_space string
log_file_name_convert string
service_names string STD
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18841027/viewspace-1086259/ ，如需转载，请注明出处，否则将追究法律责任。
