[大数据]Oracle-ADG部署

Oracle-ADG部署

0.准备

01、环境准备

02、主库备库安装数据库软件

03、主库创建数据库

$ dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-responseFile NO_VALUE \
-gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} \
-characterSet ZHS16GBK \
-sysPassword bsoft \
-systemPassword bsoft \
-databaseType OLTP \
-totalMemory 6144 \
-storageType FS \
-datafileDestination '/data/oradata' \
-recoveryAreaDestination 'NONE' \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs

1.主库配置

创建归档日志路径

mkdir -p /data/archivelog

1.1查看数据库是否是归档模式

SQL> archive log list;

Database log mode         No Archive Mode

Automatic archival       Disabled     #未开启归档

Archive destination      USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     117

Current log sequence          119

SQL> alter system set  log_archive_dest_1='location=/data/archivelog' scope=spfile;  #设置归档路径

SQL> shutdown immediate    #关闭数据库

SQL> startup mount;    #启动到mount状态

SQL> alter database archivelog;    #开启归档

SQL> alter database open;   #open数据库

SQL> archive log list;

1.2开启强制归档

SQL>select force_logging from v$database;

FORCE_LOGGING

---------------

NO

SQL>alter database force logging;  #开启force logging

SQL>select force_logging from v$database;  #验证

FORCE_LOGGING

---------------

YES
```bash
### 1.3添加standby日志文件

在备库，当RFS进程接受到日志后，就将其写入Standby日志文件里，备库的Standby日志文件可以看做是主库在线日志文件的一个镜像，当主库做日志切换时，备库的Standby日志也做相应的切换，切换后的Standby日志由备库的ARCH进程归档。
```bash
SQL>select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;

       THREAD#     GROUP#    A.BYTES/1024/1024   MEMBER

         1          3               200      /data/oradata/TEST/redo03.log

         1          2               200      /data/oradata/TEST/redo02.log

         1          1               200      /data/oradata/TEST/redo01.log

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

no rows selected

创建standby日志组，数量为redo日志的n+1,大小和redo一样

alter database add standby logfile thread 1 group 4 ('/data/oradata/TEST/redo04_stb01.log') size 50m;

alter database add standby logfile thread 1 group 5 ('/data/oradata/TEST/redo05_stb01.log') size 50m;

alter database add standby logfile thread 1 group 6 ('/data/oradata/TEST/redo06_stb01.log') size 50m;

alter database add standby logfile thread 1 group 7 ('/data/oradata/TEST/redo07_stb01.log') size 50m;

SQL> select group#,type,member from v$logfile order by 2;

 GROUP# TYPE    MEMBER

    1   ONLINE  /data/oradata/TEST/redo01.log

    2   ONLINE  /data/oradata/TEST/redo02.log

    3   ONLINE  /data/oradata/TEST/redo03.log

    6   STANDBY /data/oradata/TEST/redo06_stb01.log

    5   STANDBY /data/oradata/TEST/redo05_stb01.log

    7   STANDBY /data/oradata/TEST/redo07_stb01.log

    4   STANDBY /data/oradata/TEST/redo04_stb01.log

1.4修改参数文件

alter system set log_archive_config='DG_CONFIG=(test,testdg)';

alter system set log_archive_dest_1='LOCATION=/data/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';

alter system set log_archive_dest_2='SERVICE=testdg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg';

alter system set log_archive_dest_state_1='enable';

alter system set log_archive_dest_state_2='enable';

alter system set db_file_name_convert='/data/oradata/TESTDG/','/data/oradata/TEST/' scope=spfile;

alter system set log_file_name_convert='/data/oradata/TESTDG/','/data/oradata/TEST/' scope=spfile;

alter system set fal_server='testdg';

alter system set fal_client='test';

alter system set standby_file_management='AUTO';

alter system set DB_UNIQUE_NAME='test' scope=spfile;

上面修改的参数有的需要重启数据库才能生效

shutdown immediate

startup

1.5配置TNS文件

cd $ORACLE_HOME/network/admin/

netmgr,配置tns,监听

vi tnsnames.ora

TEST =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )

TESTDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testdg)

    )

  )

重启监听

$ lsnrctl reload

1.6拷贝参数文件

利用spfile创建pfile

create pfile='/tmp/1.ora' from spfile;

scp /tmp/1.ora 192.168.5.102:/tmp/1.ora

1.7拷贝密码文件

cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

scp orapwtest oracle@192.168.5.102:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/

2. 备库配置

2.1修改密码文件

cd $ORACLE_BASE/product/19.3.0/dbhome_1/dbs

mv orapwtest orapwtestdg

2.2修改参数文件

cd $ORACLE_BASE/product/19.3.0/dbhome_1/dbs

cp /tmp/1.ora inittestdg.ora

vi inittestdg.ora

testdg.__data_transfer_cache_size=0

testdg.__db_cache_size=8321499136

testdg.__inmemory_ext_roarea=0

testdg.__inmemory_ext_rwarea=0

testdg.__java_pool_size=0

testdg.__large_pool_size=100663296

testdg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

testdg.__pga_aggregate_target=3355443200

testdg.__sga_target=10066329600

testdg.__shared_io_pool_size=134217728

testdg.__shared_pool_size=1476395008

testdg.__streams_pool_size=0

testdg.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/testdg/adump'

*.audit_trail='db'

*.compatible='19.0.0'

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

*.db_block_size=8192

*.db_file_name_convert='/data/oradata/TEST/','/data/oradata/TESTDG/'

*.db_name='test'

*.db_unique_name='testdg'

*.diagnostic_dest='/u01/app/oracle'

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

*.fal_client='testdg'

*.fal_server='test'

*.log_archive_config='DG_CONFIG=(testdg,test)'

*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdg'

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

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_file_name_convert='/data/oradata/TEST/','/data/oradata/TESTDG/'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=3200m

*.processes=5000

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=9599m

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

2.3创建参数文件里相对应的目录

mkdir -p /data/oradata/TESTDG/

mkdir -p /u01/app/oracle/admin/testdg/adump/

mkdir -p /data/archivelog

2.4修改监听配置文件文件,配置为静态监听

cd $ORACLE_HOME/network/admin

vi listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testdg)

      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

      (SID_NAME = testdg)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

2.5修改TNS配置文件

vi tnsnames.ora

TEST =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )

TESTDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testdg)

    )

  )

重启监听

2.6启动数据库到nomount状态

SQL> startup nomount;

3.恢复备库

3.1验证监听

主库：

sqlplus sys/Aa123456@test as sysdba

sqlplus sys/Aa123456@testdg as sysdba

备库：

sqlplus sys/Aa123456@test as sysdba

sqlplus sys/Aa123456@testdg as sysdba

3.2主库登入rman，复制数据库至备库

rman target sys/Aa123456@test auxiliary sys/Aa123456@testdg

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

执行日志如下：

[oracle@node1 dbs]$ rman target sys/Aa123456@test auxiliary sys/Aa123456@testdg

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 13 22:15:02 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2397297790)

connected to auxiliary database: TEST (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

Starting Duplicate Db at 2022-08-13 22:15:29

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=271 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   passwordfile auxiliary format  '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwtestdg'   ;

}

executing Memory Script

Starting backup at 2022-08-13 22:15:30

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=286 device type=DISK

Finished backup at 2022-08-13 22:15:31

contents of Memory Script:

{

   restore clone from service  'test' standby controlfile;

}

executing Memory Script

Starting restore at 2022-08-13 22:15:31

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service test

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/data/oradata/TESTDG/control01.ctl

output file name=/data/oradata/TESTDG/control02.ctl

Finished restore at 2022-08-13 22:15:34

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/data/oradata/TESTDG/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/data/oradata/TESTDG/system01.dbf";

   set newname for datafile  3 to

 "/data/oradata/TESTDG/sysaux01.dbf";

   set newname for datafile  4 to

 "/data/oradata/TESTDG/undotbs01.dbf";

   set newname for datafile  7 to

 "/data/oradata/TESTDG/users01.dbf";

   restore

   from  nonsparse   from service

 'test'   clone database

   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/oradata/TESTDG/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2022-08-13 22:15:39

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service test

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/oradata/TESTDG/system01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service test

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/oradata/TESTDG/sysaux01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service test

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/oradata/TESTDG/undotbs01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service test

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/oradata/TESTDG/users01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2022-08-13 22:15:51

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1112652950 file name=/data/oradata/TESTDG/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1112652950 file name=/data/oradata/TESTDG/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1112652950 file name=/data/oradata/TESTDG/undotbs01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1112652950 file name=/data/oradata/TESTDG/users01.dbf

Finished Duplicate Db at 2022-08-13 22:15:53

4.开启实时同步

备库：

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;
```bash
关闭同步：即停止 mgr 进程
```bash
SQL> alter database recover managed standby database cancel;

5.验证DG

1. 通过查看archive_log_dest_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题

SQL> set linesize 10000

SQL> set pagesize 10000

SQL>col dest_name format a30

SQL>col error format a20

SQL>select dest_name,error from v$archive_dest;

2. 查询主库最大归档序号，一致即归档同步成功。

主，备库上执行：

SQL>select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

--------------

       22

主库上执行日志切换

SQL>alter system archive log current;

SQL>select max(sequence#) from v$archived_log;

6.测试数据同步

-- 创建用户u1

create user u1 identified by Aa123456

default tablespace users

temporary tablespace temp;

grant "DBA" to u1 with admin option;

-- 使用u1登录，创建测试表

CREATE TABLE t1(id INT,name VARCHAR(10));

INSERT INTO t1(id,name) VALUES(1,'a');

INSERT INTO t1(id,name) VALUES(2,'b');

COMMIT;

SELECT * FROM t1;