一、基本信息

二、数据库配置

2.1 配置双库

强制记录日志

SQL> startup mount
SQL> alter database force logging;
SQL> alter database archivelog;

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES
复制

修改$ORACLE_HOME/network/admin/tnsnames.ora

tns_ora_src=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora_src)
      (UR=A)
    )
  )
tns_ora_dst=  
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.207.6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora_dst)
      (UR=A)
    )
  )测试连接
复制

sqlplus sys/Password1@tns_ora_src as sysdba
sqlplus sys/Password1@tns_ora_dst as sysdba
复制

2.2 配置主库

1. 设置数据库参数

   db_unique_name：数据库唯一名。

   SQL> alter system set db_unique_name='ora_src' scope=spfile;
   SQL> shutdown immediate
   SQL> startup
   复制

   log_archive_config：启用或禁用将redo日志发送到远程目标和接收远程重做日志，并配置为主库与备库指定唯一的数据库名称(DB_UNIQUE_NAME)，12c最多30个并逗号隔开。

   -- alter system set log_archive_config='dg_config=(,,,...)' scope=both;
   SQL> alter system set log_archive_config='dg_config=(ora_src,ora_dst)' scope=both;
   复制

   log_archive_dest_1：主库的存档位置(通过show parameter log_archive_dest_1查看)、db_unique_name：主库名称

   log_archive_dest_state_2：默认enable

   SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata valid_for=(all_logfiles,all_roles) db_unique_name=ora_src' scope=both;
   SQL> alter system set log_archive_dest_state_1=enable scope=both;
   复制

   log_archive_dest_2：备库的TNSNAME、db_unique_name：备库名称

   log_archive_dest_state_2：默认enable

   SQL> alter system set log_archive_dest_2='service=tns_ora_dst lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora_dst' scope=both;
   SQL> alter system set log_archive_dest_state_2=enable scope=both;
   复制

   fal_server：备库的TNSNAME、fal_client：主库的TNSNAME

   SQL> alter system set fal_server='tns_ora_src' scope=both;
   复制

   db_file_name_convert：备库数据文件目录，其次是主库数据文件目录,

   SQL> alter system set db_file_name_convert='/u01/app/oracle','/u01/app/oracle' scope=spfile;
   复制

   log_file_name_convert：备库归档日志目录，其次是主库归档日志目录

   SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata' scope=spfile;
   复制

   standby_file_management：设置为auto可以自动管理表空间文件，设置为manual需要手动删除

   alter system set standby_file_management=auto scope=both;
   复制

2. 主库重启

   SQL> shutdown immediate
   SQL> startup
   # 可发现数据库配置已被更改
   SQL> show parameter name
   复制

3. 主库上创建备库的pfile

   SQL> create pfile='/home/oracle/ora_dst_pfile' from spfile;
   复制

   编辑/home/oracle/ora_dst_pfile，如果ORALCE_SID不同,修改control_files等参数的路径

   *.db_unique_name='ora_dst'
   *.control_files='/u01/app/oracle/oradata/oracle/control01.ctl','/u01/app/oracle/flash_recovery_area/oracle/control02.ctl'
   *.log_archive_dest_1='location=/u01/app/oracle/oradata valid_for=(all_logfiles,all_roles) db_unique_name=ora_dst'
   *.log_archive_dest_2='service=tns_ora_src lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora_src'
   复制

4. 复制pfile到备库

   scp /home/oracle/ora_dst_pfile oracle@192.168.207.6:
   复制

2.3 配置备库

1. 通过pfile启动到nomount模式（注意备份参数文件）

   SQL> startup nomount pfile=/home/oracle/ora_dst_pfile;
   SQL> create spfile from pfile='/home/oracle/ora_dst_pfile';
   复制

2. 检查参数，能够看到变化的参数

   SQL> show parameter name
   复制

3. 修改数据库角色

   -- mount状态下
   SQL> SELECT database_role FROM v$database;
   DATABASE_ROLE
   ----------------
   PRIMARY
   
   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
   Database altered.
   复制

三、初始化数据（主库->备库）

3.1 复制数据文件

主库执行（主库read write状态，备库nomount状态）

[oracle@ora1 admin]$ rman target sys/password@tns_ora_src auxiliary sys/password@tns_ora_dst
connected to target database: ORACLE (DBID=1956275221)
connected to auxiliary database: ORACLE (not mounted)

rman> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
复制

备库执行

SQL> select group#,bytes from v$standby_log;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/redo1.log' size 50M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/redo2.log' size 50M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/redo3.log' size 50M;
alter database add standby logfile thread 1 '/u01/app/oracle/oradata/redo4.log' size 50M;
alter database add standby logfile thread 2 '/u01/app/oracle/oradata/redo5.log' size 50M;
alter database add standby logfile thread 2 '/u01/app/oracle/oradata/redo6.log' size 50M;
alter database add standby logfile thread 2 '/u01/app/oracle/oradata/redo7.log' size 50M;
alter database add standby logfile thread 2 '/u01/app/oracle/oradata/redo8.log' size 50M;
复制

实时应用，备库开启至open状态

SQL> alter database recover managed standby database using current logfile disconnect from session;
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 from session;
复制

此时可以插入数据，查看是否同步

3.2 快速测试

主库

SQL> alter system switch logfile
复制

主库、备库查看序列号同步情况

-- 11g
SQL> select max(sequence#) from v$log;
-- 12c,不要使用archive log list查看(Doc ID 2041137.1)
SQL> select max(sequence#) from v$archived_log;
复制