delete oracle dg config

发表于 | 分类于 ， | | 阅读次数

简述

反向操作，取消oracle dataguard，恢复数据库为非dg状态

操作

主库取消日志传输

在主库执行

1
2
3
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_2=''
alter system set log_archive_dest_state_2 = 'enable';

备注：在这里，我的dg日志传输的参数配置在log_archive_dest_state_2，具体，根据自己的配置。可使用如下命令查询

1
show parameter log_archive_dest

找到VALUE 为service的

前面的NAME 就是该参数名

主库取消FORCE LOGGING

1
2
3
4
5
6
SQL> ALTER DATABASE NO FORCE LOGGING;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE                             FORCE_LOGGING
------------------------------------ ---------------------------------------------------------------------------------------------------------------------
ARCHIVELOG                           NO

主库修改standby_file_management 为MANUAL

1
2
3
4
5
6
SQL> alter system set standby_file_management = 'MANUAL';
System altered.
SQL> show parameter standby_file_management;
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
standby_file_management              string                            MANUAL

修改归档日志的参数值，删除角色和DB_UNIQUE_NAME的指定

原值：

1
2
3
4
5
6
SQL> show parameter log_archive_dest_1
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_1                   string                            LOCATION=+ARCHIVE_LOG VALID_FO
                                                                       R=(ALL_LOGFILES,ALL_ROLES) DB_
                                                                       UNIQUE_NAME=srmcloud

改为：

1
2
3
4
SQL> show parameter log_archive_dest_1
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_1                   string                            LOCATION=+ARCHIVE_LOG

命令：

1
alter system set log_archive_dest_1='LOCATION=+ARCHIVE_LOG';

注：其中的LOCATION根据具体情况修改

设置FAL_SERVER为空

1
2
SQL> alter system set FAL_SERVER='';
System altered.

设置log_archive_config为空

1
alter system set log_archive_config='';

删除standby日志组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
    GROUP#    THREAD#  SEQUENCE# ARCHIVED  STATUS
---------- ---------- ---------- --------- ------------------------------
        11          0          0 YES       UNASSIGNED
        12          0          0 YES       UNASSIGNED
        13          0          0 YES       UNASSIGNED
        14          0          0 YES       UNASSIGNED
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 12;
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 13;
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 14;
Database altered.