Skip to content

Latest commit

 

History

History
390 lines (163 loc) · 6.51 KB

dataguard.md

File metadata and controls

390 lines (163 loc) · 6.51 KB
Raw

-- DataGuard

-- 1. Primary DB (orcl_gy)

-- 1.1

alter database force logging;

-- 1.2

alter database add standby logfile size 200M;

alter database add standby logfile size 200M;

alter database add standby logfile size 200M;

alter database add standby logfile size 200M;

-- 1.3

alter system set log_archive_config='dg_config=(orcl_gy,orcl_bj)';

alter system set log_archive_dest_2='service=orcl_bj async valid_for=(online_logfile,primary_role) db_unique_name=orcl_bj';

alter system set standby_file_management=AUTO;

-- 1.4

alter database archivelog;

-- 1.5

cat /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

\(SID\_DESC =

  \(GLOABAL\_NAME = orcl\_gy.dataforum.org\)

  \(ORACLE\_HOME = /u01/app/oracle/product/12.2.0.1/db\_1\)

  \(SID\_NAME = orcl\)

\)

\(SID\_DESC =

  \(GLOABAL\_NAME = orcl\_bj.dataforum.org\)

  \(ORACLE\_HOME = /u01/app/oracle/product/12.2.0.1/db\_1\)

  \(SID\_NAME = orcl\)

\)

)

LISTENER =

(DESCRIPTION_LIST =

\(DESCRIPTION =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = gy.dataforum.org\)\(PORT = 1521\)\)

\)

)

-- 1.6

cat /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PDBORCL =

(DESCRIPTION =

\(ADDRESS\_LIST =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = gy.dataforum.org\)\(PORT = 1521\)\)

\)

\(CONNECT\_DATA =

  \(SERVICE\_NAME = pdborcl.org\)

\)

)

ORCLBJ =

(DESCRIPTION =

\(ADDRESS\_LIST =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = bj.dataforum.org\)\(PORT = 1521\)\)

\)

\(CONNECT\_DATA =

  \(SERVICE\_NAME = orcl\)

\)

)

ORCLGY =

(DESCRIPTION =

\(ADDRESS\_LIST =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = gy.dataforum.org\)\(PORT = 1521\)\)

\)

\(CONNECT\_DATA =

  \(SERVICE\_NAME = orcl\)

\)

)

ORCL_GY =

(DESCRIPTION =

\(ADDRESS\_LIST =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = gy.dataforum.org\)\(PORT = 1521\)\)

\)

\(CONNECT\_DATA =

  \(SERVICE\_NAME = orcl\_gy.org\)

\)

)

ORCL_BJ =

(DESCRIPTION =

\(ADDRESS\_LIST =

  \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = bj.dataforum.org\)\(PORT = 1521\)\)

\)

\(CONNECT\_DATA =

  \(SERVICE\_NAME = orcl\_bj.org\)

\)

)

-- 1.7

scp orapworcl [email protected]:/u01/app/oracle/product/12.2.0.1/db_1/dbs/

scp initorcl.ora [email protected]:/u01/app/oracle/product/12.2.0.1/db_1/dbs/

-- 2. Standby DB (orcl_bj)

cat /u01/app/oracle/product/12.2.0.1/db_1/dbs/initorcl.ora

*.db_domain='org'

*.db_name='orcl'

startup nomount pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/initorcl.ora

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_gy'

-- 3. Primary DB (orcl_gy)

rman target sys/Chen15285649896@orcl_gy auxiliary sys/Chen15285649896@orcl_bj

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate auxiliary channel aux type disk;

-- allocate auxiliary channel cstby2 type disk;

duplicate target database for standby from active database spfile

-- parameter_value_convert 'orcl_gy','orcl_bj'

set db_unique_name='orcl_bj'

-- set fal_client='orcl_bj'

-- set fal_server='orcl_gy'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(orcl_gy,orcl_bj)'

set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_bj'

set log_archive_dest_2='service=orcl_gy ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl_gy'

;

}

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate auxiliary channel aux type disk;

duplicate target database for standby from active database spfile

set db_unique_name='orcl_bj'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(orcl_gy,orcl_bj)'

set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_bj'

set log_archive_dest_2='service=orcl_gy ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl_gy'

;

}

alter database set standby database to maximize availability;

-- 4. Standby DB (orcl_bj)

-- 备库切换到open状态

-- 退出redo应用状态

-- 停止standby的redo应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;注意，此时只是暂时redo 应用，并不是停止Standby 数据库，standby 仍会保持接收只不过不会再应用接收到的归档，直到你再次启动redo 应用为止。类似mysql里面的stop slave功能;

alter database recover managed standby database cancel;

alter database open read only;

-- 再应用redo日志

alter database recover managed standby database using current logfile disconnect;

--去primary、standby库上面执行检查redo或archivelog应用情况

select sequence#, first_time, applied from v$archived_log order by sequence#;

-- 查看数据库状态

select dbid,name,open_mode,database_role,swichover_status from v$database;

-- 5. 主备切换

-- 5.1 (Primary DB)

-- 查看状态

select dbid,name,open_mode,database_role,switchover_status,protection_mode from v$database;

-- 当switchover_status为时"to standby"

alter database commit to switchover to physical standby;

-- 当switchover_status不为"to standby"

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

-- 重启数据库

shutdown immediate;

startup nomount;

alter database mount standby database;

-- 5.2 (Standby DB)

-- 应用redo

alter database recover managed standby database disconnect from session;

-- 查看状态

select dbid,name,open_mode,database_role,switchover_status,protection_mode from v$database;

-- 当switchover_status为时"to primary"

alter database commit to switchover to primary;

-- 当switchover_status不为"to primary"

alter database commit to switchover to primary with session shutdown;

-- 重启数据库

shutdown immediate;

startup;