oracle snapshot standby database-技术圈

作者 | PGer

Snapshot standby database 快照备机 ，它的作用主要在于提供了某段时间物理备库的写功能，所以在生产灰度环境很有用处，基于 oracle physical standby，可以在 physical standby 和 snapshot standby 之间来回切换，非常方便。

这样可以实现每天晚上切回到 physical standby ，白天切回到 snapshot standby，开发可以白天在 snapshot standby 做测试，晚上同步数据。

记录一下physical standby到snapshot standy的创建步骤吧。

0 1

Physical standby

1.主库开启归档和force logging，修改相关参数

SQL> alter system set log_archive_dest_1='location=/oraarch' scope=both sid='*';

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database ARCHIVELOG;

SQL> alter database open;

SQL> alter database force logging;

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_dg)';

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl_standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_dg' sid='orcl';

SQL> alter system set FAL_SERVER=orcl_standby;

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

rman归档日志保留策略修改

RMAN> configure archivelog deletion policy to applied on standby;

2.拷贝密码文件至备库

[oracle@db1 ~]$ scp $ORACLE_HOME/dbs/orapworcl db2:$ORACLE_HOME/dbs/

3.备库提前创建audit目录

SQL> show parameter audit;

[oracle@db1~]$ mkdir $audit_file_dest

4.主备库准备连接串及监听文件

tnsnames.ora：primary && standby

ORCL_PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1531))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

ORCL_STANDBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1531))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(UR = A)

)

listener.ora：primary

LISTENER_DG =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1531))

)

SID_LIST_LISTENER_DG =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /oracle/product/19c)

(SID_NAME = orcl)

)

listener.ora：standby

LISTENER_DG =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1531))

)

SID_LIST_LISTENER_DG =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /oracle/product/19c)

(SID_NAME = orcl)

)

主备机上测试访问

[oracle@db1 ~]$ sqlplus sys/oracle@orcl_primary as sysdba

[oracle@db1 ~]$ sqlplus sys/oracle@orcl_standby as sysdba

5.准备备库启动参数文件

vi standby_init.ora

DB_NAME=orcl

DB_UNIQUE_NAME=orcl_dg

DB_BLOCK_SIZE=8192

sga_target = '2048M'

log_archive_dest_1='LOCATION=/oraarch'

control_files='/oradata/ORCL/control01.ctl','/oradata/ORCL/control02.ctl'

log_file_name_convert='/oradata/ORCL','/oradata/ORCL'

6.准备备库创建脚本

vi create_standby.sh

sqlplus "/ as sysdba" << EOF

shutdown abort

startup nomount pfile='/home/oracle/standby_init.ora'

connect sys/oracle@ORCL_PRIMARY as sysdba

connect sys/oracle@ORCL_STANDBY as sysdba

EOF

rman target sys/oracle@ORCL_PRIMARY auxiliary sys/oracle@ORCL_STANDBY << EOF

run {

allocate channel ch001 type disk;

allocate auxiliary channel ch002 type disk;

duplicate target database for standby from active database nofilenamecheck

spfile

parameter_value_convert 'orcl','orcl_dg'

set remote_listener=''

set cluster_database='false'

set db_name='orcl'

set db_unique_name='orcl_dg'

set instance_name='orcl'

set log_archive_dest_2=' '

set log_archive_dest_3=' '

set log_archive_dest_4=' '

set sga_target = '2048M'

set instance_number = '1'

set log_archive_dest_1='LOCATION=/oraarch'

set db_file_name_convert='/oradata/ORCL','/oradata/ORCL'

set log_file_name_convert='/oradata/ORCL','/oradata/ORCL'

set control_files='/oradata/ORCL/control01.ctl','/oradata/ORCL/control02.ctl';

release channel ch001;

release channel ch002;

}

EOF

7.创建备库

[oracle@db1 ~]$ sh create_standby.sh

8.主备库创建standby logfile

主备都创建，注意要比redo日志多一组

SQL> alter database add standby logfile '/oradata/ORCL/redo_std01.log' size 200M;

SQL> alter database add standby logfile '/oradata/ORCL/redo_std02.log' size 200M;

SQL> alter database add standby logfile '/oradata/ORCL/redo_std03.log' size 200M;

SQL> alter database add standby logfile '/oradata/ORCL/redo_std04.log' size 200M;

9.备库修改相关参数并启动应用

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCL_PRIMARY LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' sid='orcl';

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_dg,orcl)';

SQL> alter system set FAL_SERVER=ORCL_PRIMARY;

SQL> recover managed standby database disconnect from session;

SQL> recover managed standby database cancel;

SQL> alter database open;

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

0 2

Snapshot standby

1.备库开启闪回恢复区

[oracle@db2 ~]$ mkdir /oradata/ORCL/fra

SQL> alter system set db_recovery_file_dest='/oradata/ORCL/fra';

SQL> alter system set db_recovery_file_dest_size=1G;

2.停止日志应用

SQL> recover managed standby database cancel;

3.切换到snapshot standby

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select name,database_role,open_mode,switchover_status from v$database;