WINDOWS SERVER 2012 R2(11gR2 RAC+单实例=DG)

基于主库操作

开启归档(mount状态)

Alter database archivelog;

开启闪回(mount状态)

Alter database flashback on;

设置数据库强制归档

Alter database force logging;

添加STANDBY日志文件

创建原则

1. 确保standby redo log 的大小与主库online redo log 的大小保持一致
2. 如主库为单实例数据库：standby redo log组数=主库日志组总数
3. 如果主库是RAC数据库：standby redo log组数=（每线程的日志组数+1）*最大线程数

例：两节点一共四组日志，每组两个member，standby logfile数量为每组3个member，共12个standby logfile

     4.不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输

 

修改参数文件

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstd)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DESTVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcl';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orclstd';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET FAL_CLIENT=orcl;

ALTER SYSTEM SET FAL_SERVER=orclstd;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA/orcl/datafile/','C:\app\Administrator\oradata\orclstd\ ' scope=spfile;

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA/orcl/onlinelog/'，'c:\app\Administrator\oradata\orclstd\' scope=spfile;

修改完成后重启数据库使参数生效。

修改监听文件

修改C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN目录下的listener.ora

增加以下内容

(SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (SID_NAME = orcl1)

)

重启监听并查看监听状态，是否生成静态监听。使用ORACLE家目录下的lsnrctl命令。

修改TNS文件

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

orcl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.160)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

orclstd =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.209)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstd)

    )

  )

拷贝参数文件

Create pfile=’c:\pfile1121.ora’ from spfile;

拷贝密码文件

C:\app\Administrator\product\11.2.0\dbhome_1\database\PWD实例名.ora

基于备库操作

修改参数文件

修改后内容参考如下（确定开启AMM）：

*.__db_cache_size=0

*.__java_pool_size=0

*.__large_pool_size=0

*.__oracle_base='C:\app\Administrator'

*.__pga_aggregate_target=0

*.__sga_target=0

*.__shared_io_pool_size=0

*.__shared_pool_size=0

*.__streams_pool_size=0

*.audit_file_dest='C:\app\Administrator\admin\orclstd\adump\'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.4.0'

*.control_files='C:\app\Administrator\oradata\orclstd\controlfile01.ctl','C:\app\Administrator\oradata\orclstd\controlfile02.ctl'

*.db_block_size=8192

*.db_create_file_dest=''

*.db_domain=''

*.db_file_name_convert='+DATA/orcl/datafile/','C:\app\Administrator\oradata\orclstd\'

*.db_name='orcl'

*.db_recovery_file_dest='C:\app\Administrator\flash_recovery_area\'

*.db_recovery_file_dest_size=4621074432

*.db_unique_name='orclstd'

*.diagnostic_dest='C:\app\Administrator'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCLSTD'

*.fal_server='ORCL'

*.instance_number=1

*.log_archive_config='DG_CONFIG=(orcl,orclstd)'

*.log_archive_dest_1='location=C:\app\Administrator\flash_recovery_area\ORCLSTD'

*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='ARC%S_%R.%T'

*.log_file_name_convert='+DATA/orcl/onlinelog/','c:\app\Administrator\oradata\orclstd\'

*.memory_target=1202716672

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

修改监听文件

(SID_DESC =

      (GLOBAL_DBNAME = orclstd)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (SID_NAME = orclstd)

    )

  )

重启监听并查看监听状态，是否生成静态监听。使用ORACLE家目录下的lsnrctl命令。

修改TNS文件

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

orcl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.167)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

orclstd =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.209)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstd)

    )

  )

创建orclstd服务

oradim -new -sid orclstd -startmode manual –spfile

启动监听

Lsnrctl start

启动数据库到NOMOUNT

如果遇到ORA-12560: TNS: 协议适配器错误，那么请检查下面2点：

1. 启动orclstd服务

net start oracleserviceorclstd

1. 设置实例名

Set ORACLE_SID=orclstd

Sqlplus 进入

Startup nomount pfile=’c:\pfile.ora’

如果还没搞定，自行百度。

--此处开始测试主备库的TNS

开始还原

rman target sys/oracle@主库tns auxiliary sys/oracle@备库tns

rman target sys/oracle@orcl auxiliary sys/oracle@orclstd

 

run{
allocate channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set newname for tempfile 1 to 'c:\app\oracle\oradata\orclstd\temp01.dbf';
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel c2;
}

 

alter database open;

开启实时同步

Alter database recover managed standby database using current logfile disconnect from session;

测试是否同步。

测试switch over

切换前准备

1. 在rac主库关掉一个节点的数据库

Shutdown immediate

1. 查看主库状态

SQL> select switchover_status,database_role from v$database; 

SWITCHOVER_STATUS    DATABASE_ROLE

-------------------- ----------------

TO STANDBY      PRIMARY

SQL>

若上面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换

1. 查看备库状态

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE

------------------------------------

TO PRIMARY       PHYSICAL STANDBY

SQL>

若上面查询结果显示为TO PRIMARY 或 SESSIONS ACTIVE表明可以切换成主库；

切换过程

主库执行（切换到备库）

1、alter database commit to switchover to physical standby with session shutdown;

2、startup mount

查看主库状态

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

备库执行（切换到主库）

1. alter database commit to switchover to primary with session shutdown;
2. alter database open;
3. 查看状态

SQL> select switchover_status,database_role,open_mode from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE

-------------------- ------------------------------------

SESSIONS ACTIVE      PRIMARY         READ WRITE

在新的备库开启实时同步

alter database recover managed standby database using current logfile disconnect from session;

验证实时传输