Oracle使用DataGuard

# Oracle使用DataGuard
## 环境说明
- Oracle Rac 版本 11gr2(11.2.0.4.0)
- OracleLinux 6.8

## 检查Oracle
### 归档模式
```sql
SQL> archive log list;
```

如果`Database log mode`的值为`Database log mode`，那说明没有开启ArchiveLog，需要手动开启然后再进行DataGuard和Rman的配置。如果已经启用，[转到配置Rman](##配置Rman)
```sql
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     29
Current log sequence           31
```

- 查看运行节点
```sql
SQL> select instance_name,host_name,status from gv$instance;
```

### 启用归档模式
#### 1. 备份spfile
```sql
SQL> create pfile='/${PATH_TO_BACKUP}/${SID}.ora' from spfile;

# create pfile='/opt/OracleBak/RmanBackup/REGTEST/REGTEST1-20210418.ora' from spfile;
# create pfile='/opt/OracleBak/RmanBackup/REGTEST/REGTEST2-20210418.ora' from spfile;
```

#### 2. 关闭实例集群模式
集群模式配置为静态参数需要使用到spfile。
```sql
SQL> alter system set cluster_database=false scope=spfile sid='${SID}';

# alter system set cluster_database=false scope=spfile sid='sustc';
# 查看集群模式
SQL> show parameter cluster;
```

#### 3. 开启archivelog模式
```sql
# RAC环境
[root@db1 ~]# srvctl stop database -d ${SID}

# 单节点，连接到数据库后
SQL> shutdown immediate

# 启动RAC单节点
srvctl start instance -d REGTEST -i REGTEST1 -o mount

# 启动数据库
SQL> shutdown immediate

[root@db1 ~]# sqlplus / as sysdba
# 设置主库开启归档日志
SQL> alter database archivelog;
# 设置主库开启强制记录日志
SQL> alter database force logging;
# 修改归档日志位置
SQL> alter database add standby logfile group 4 '/oracle/app/oracle/oradata/${SID}/standby04.log' size 50m;
SQL> alter database add standby logfile group 5 '/oracle/app/oracle/oradata/${SID}/standby05.log' size 50m;
SQL> alter database add standby logfile group 6 '/oracle/app/oracle/oradata/${SID}/standby06.log' size 50m;
SQL> alter database add standby logfile group 7 '/oracle/app/oracle/oradata/${SID}/standby07.log' size 50m;
# 查询主库归档模式及日志强制记录
SQL> select log_mode,force_logging from v$database;
# 查看归档日志
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
SQL> select group#, status, type, member from v$logfile;
# 查看更新情况
SQL> show parameter log_archive_dest;
# 创建spfile
SQL> create pfile from spfile;
```
```shell
scp -P11022 initosaorcl.ora oracle@dg:/oracle/app/oracle/product/dbhome_1/dbs/initdg.ora
scp -P11022 orapwosaorcl oracle@1.1.1.1:/oracle/app/oracle/product/dbhome_1/dbs/orapwdg
# 备份spfile到dg，然后修改主库spfile添加,osaorcl为主库sid，dg为备库sid
DB_UNIQUE_NAME=osaorcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(osaorcl,dg)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/osaorcl/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=osaorcl'
LOG_ARCHIVE_DEST_2= 'SERVICE=dg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=dg
FAL_CLIENT=osaorcl
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/dg','/oracle/app/oracle/oradata/osaorcl'
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/dg','/oracle/app/oracle/oradata/osaorcl'
STANDBY_FILE_MANAGEMENT=AUTO

# 然后使用新的spfile启动主库
SQL> startup pfile='/oracle/app/oracle/product/dbhome_1/dbs/initosaorcl.ora';
# 查看更新情况
SQL> show parameter log_archive_dest;
```

```shell
# 然后修改备库spfile添加,osaorcl为主库sid，dg为备库sid
DB_UNIQUE_NAME=dg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg,osaorcl)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/dg/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'
LOG_ARCHIVE_DEST_2= 'SERVICE=osaorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=osaorcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=osaorcl
FAL_CLIENT=dg
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/osaorcl','/oracle/app/oracle/oradata/dg'
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/osaorcl','/oracle/app/oracle/oradata/dg'
STANDBY_FILE_MANAGEMENT=AUTO

# 创建dg备库文件夹
mkdir -p /oracle/app/oracle/oradata/dg
mkdir -p /oracle/app/oracle/fast_recovery_area/dg
mkdir -p /oracle/app/oracle/admin/dg/{a,dp}dump
mkdir -p /oracle/app/oracle/recovery_area
mkdir -p /oracle/app/oracle/admin/osaorcl/{a,dp}dump
mkdir -p /oracle/app/oracle/recovery_area/osaorcl/
mkdir -p /oracle/app/oracle/oradata/dg
mkdir -p /oracle/app/oracle/oradata/osaorcl
# 然后使用新的spfile启动备库
SQL> create spfile from pfile;
SQL> startup pfile='/oracle/app/oracle/product/dbhome_1/dbs/initdg.ora' nomount;

```
```shell
# 修改主库tnsnames.ora
OSAORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = osa-orcl67)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = osaorcl)
    )
  )

DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = osa-orcl-dg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg)
    )
  )

# 修改备库tnsnames.ora
OSAORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = osa-orcl67)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = osaorcl)
    )
  )

DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = osa-orcl-dg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg)
    )
  )
  
# 修改备库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dg)
      (ORACLE_HOME = /oracle/app/oracle/product/dbhome_1)
        (SID_NAME = dg)
    )
  )
```

```shell
# 备库登陆rman
rman target sys/${password}@osaorcl auxiliary sys/${password}@dg
# 执行duplicate
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

# Duplicate完成后，在备库启用实时ADG
SQL> select database_role,open_mode from v$database;
# 开启ADG
SQL> recover managed standby database using current logfile disconnect from session;
（停止日志应用的命令是：alter database recover managed standby database cancel;）
Media recovery complete.
############## 修改为只读模式
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
###############
# 查看ADG状态
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

# 主备查看数据库保护模式
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

# 查看dg日志状态
select * from v$dataguard_status;
```