Oracle 12C 单实例DG搭建

• 主库搭建安装（仅安装数据库软件）

• 备库搭建安装（克隆安装）
• 打包安装完成的数据库软件传送至备机

[oracle@oraclenode1 ~]$ cd /data

[oracle@oraclenode1 ~]$tar zcf  oracle-clone.tar.gz  oracle/ oraInventory/

[oracle@oraclenode1 ~]$ scp oracle-clone.tar.gz oracle2:/data

[oracle@oraclenode2 ~]$rm –rf /data/oraInventory/*

[oracle@oraclenode2 ~]$$ORACLE_HOME/clone/bin/clone.pl

\ORACLE_BASE=/data/oracle  \ORACLE_HOME=/data/oracle/product/12.1.0/dbhome_1

\OSDBA_GROUP=dba OSOPER_GROUP=dba 

\OSBACKUPDBA_GROUP=dba 

\OSRACDBA_GROUP=dba 

\INVENTORY_LOCATION=/data/oraInventory -defaultHomeName

• 主数据库监听配置

# netca

• 主库数据库实例安装

# dbca

• 标准版没有此项

• 从库网络配置

# netmgr

• 从库配置netca

• 此处的service name为主库Service name

• 此处为主库的hostname

• 此处为主库的net service name

• 配置data guard
• 主库配置
• 归档配置
• 查看归档和日志强制logging状态

[oracle@oraclenode1 ~]$ sqlplus / as sysdba

SQL> select log_mode,force_logging from v$database;

• 把数据库启动到mount状态

SQL> shutdown immediate

SQL> startup mount

• 开启归档

SQL> alter database archivelog;

• 日志强制logging

SQL> alter database force logging;

• 查看是否开启

• 创建standby控制文件

SQL>alter database create standby controlfile as '/data/oracle/oradata/onisdb/standby.ctl';

• 添加standby的redo日志
• 查看主库在线日志大小和组数

SQL> select group#,bytes/1024/1024 from v$log;

• 查看从库在线日志大小和组数

select group#,bytes/1024/1024 from v$standby_log;

• 创建必要目录

mkdir -p /data/oracle/{onlinelog,datafile}

mkdir -p /data/oracle/onisdb/archivelog

• 增加standby日志

SQL> alter database add standby logfile group 4 '/data/oracle/onlinelog/stdredo01.log' size 50M;

SQL> alter database add standby logfile group 5 '/data/oracle/onlinelog/stdredo02.log' size 50M;

SQL> alter database add standby logfile group 6 '/data/oracle/onlinelog/stdredo03.log' size 50M;

SQL> alter database add standby logfile group 7 '/data/oracle/onlinelog/stdredo04.log' size 50M;

• 确认备库日志

SQL> select group#,bytes/1024/1024 from v$standby_log;

• 修改参数文件

SQL> alter system set log_archive_config='DG_CONFIG=(onisdb,sonisdb)'; 

SQL> alter system set log_archive_dest_2='SERVICE=sonisdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sonisdb' scope=spfile; 

SQL> alter system set log_archive_dest_state_1='enable'; 

SQL> alter system set log_archive_dest_state_2='enable'; 

SQL> alter system set db_file_name_convert='/data/oracle/datafile','/data/oracle/datafile' scope=spfile; 

SQL> alter system set log_file_name_convert='/data/oracle/onlinelog','/data/oracle/onlinelog' scope=spfile; 

SQL> alter system set fal_server='sonisdb'; 

SQL> alter system set fal_client='onisdb'; 

SQL> alter system set standby_file_management='AUTO';

SQL> quit

• 修改listener.ora配置文件

[oracle@oraclenode1 ~]$ vi /data/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

• 增加如下配置

SID_LIST_LISTENER =

 (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = onisdb)

    (ORACLE_HOME = /data/oracle/product/12.1.0/dbhome_1)

    (SID_NAME = onisdb)

    )

  )

修改tnsnames.ora配置文件

[oracle@oraclenode1 ~]$vi /data/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

• 增加如下配置

SONISDB =

 (DESCRIPTION =

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

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = sonisdb.localdomain)

    )

  )

• 重启监听

[oracle@oraclenode1 ~]$ lsnrctl stop

[oracle@oraclenode1 ~]$ lsnrctl start

• 生成参数文件

[oracle@oraclenode1 ~]$sqlplus / as sysdba

SQL> create pfile from spfile;

• 修改参数文件
• 添加如下行

*.db_unique_name='onisdb'

*.log_archive_dest_1='LOCATION=/data/oracle/sonisdb/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sonisdb'

SQL> create spfile from pfile;

• 打开数据库

SQL> alter database open;

• 传输文件到从库

[oracle@oraclenode1 ~]$ cd /data/oracle/product/12.1.0/dbhome_1/dbs/

[oracle@oraclenode1 dbs]$ scp initonisdb.ora orapwonisdb  

\oraclenode2:/data/oracle/product/12.1.0/dbhome_1/dbs/

• 从库配置
• 参数配置文件修改

[oracle@oraclenode2 ~]$ cd /data/oracle/product/12.1.0/dbhome_1/dbs/

[oracle@oraclenode2 dbs]$ mv initonisdb.ora initsonisdb.ora 

[oracle@oraclenode2 dbs]$ mv orapwonisdb orapwsonisdb

[oracle@oraclenode2 dbs]$ vi initsonisdb.ora

• 修改如下行

*.audit_file_dest='/data/oracle/admin/sonisdb/adump'

*.control_files='/data/oracle/oradata/sonisdb/control01.ctl','/data/oracle/oradata/sonisdb/control02.ctl'

*.db_unique_name='sonisdb'

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

*.fal_client='sonisdb'

*.fal_server='onisdb'

*.log_archive_config='DG_CONFIG=(onisdb,sonisdb)'

*.log_archive_dest_1='LOCATION=/data/oracle/sonisdb/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sonisdb'

*.log_archive_dest_2='SERVICE=onisdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=onisdb'

• 创建必要的目录

[oracle@oraclenode2 dbs]$ mkdir -p /data/oracle/admin/sonisdb/adump

[oracle@oraclenode2 dbs]$ mkdir -p /data/oracle/oradata/{onisdb,sonisdb}

[oracle@oraclenode2 dbs]$ mkdir -p /data/oracle/{datafile,onlinelog}

[oracle@oraclenode2 dbs]$ mkdir -p /data/oracle/sonisdb/archivelog

• 修改tnsnames.ora配置文件

[oracle@oraclenode1 ~]$vi /data/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

• 增加如下配置

SONISDB =

 (DESCRIPTION =

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

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = sonisdb.localdomain)

    )

  )

• 重启监听

[oracle@oraclenode1 ~]$ lsnrctl stop

[oracle@oraclenode1 ~]$ lsnrctl start

• 启动从库到nomount状态

[oracle@oraclenode2 ~]$ sqlplus / as sysdba

SQL> startup nomount

• 验证监听
• 主库验证监听

[oracle@oraclenode1 ~]$ tnsping onisdb

[oracle@oraclenode1 ~]$ tnsping sonisdb

[oracle@oraclenode1 ~]$ sqlplus sys/81977100@onisdb as sysdba

[oracle@oraclenode1 ~]$ sqlplus sys/81977100@sonisdb as sysdba

• 备库验证监听

[oracle@oraclenode2 ~]$ tnsping onisdb

[oracle@oraclenode2 ~]$ tnsping sonisdb

[oracle@oraclenode2 ~]$ sqlplus sys/81977100@onisdb as sysdba

[oracle@oraclenode2 ~]$ sqlplus sys/81977100@sonisdb as sysdba

• 恢复数据
• 主库操作

[oracle@oraclenode1~]$rman target sys/81977100@onisdb \

auxiliary sys/81977100@sonisdb

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

• 备库开启实时应用模式

SQL> alter database open read only;

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

alter system switch logfile;

select thread#,max(sequence#) from v$log_history group by thread#;

select process,status from v$managed_standby;

select max(sequence#) from v$standby_log;

select max(sequence#) from v$archived_log;

• 测试同步
• 主库

SQL>create table test as select * from dba_objects where rownum < 101;

• 备库

#su - oracle

$sqlplus / as sysdba

SQL>select count(*) from test;