oracle12c到19c adg搭建（四）dg搭建

一、主库操作

[oracle@o12u19p ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 18 14:41:34 2024

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL> show parameter name

NAME                                 TYPE        VALUE

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

cell_offloadgroup_name               string

db_file_name_convert                 string

db_name                              string      orcl

db_unique_name                       string      orcl

global_names                         boolean     FALSE

instance_name                        string      orcl

lock_name_space                      string

log_file_name_convert                string

pdb_file_name_convert                string

processor_group_name                 string

service_names                        string      orcl

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     14

Current log sequence           17

SQL>

1.1开归档改归档路径开强记日志

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 6174015488 bytes

Fixed Size                  2938016 bytes

Variable Size            1241516896 bytes

Database Buffers         4915724288 bytes

Redo Buffers               13836288 bytes

Database mounted.

SQL> alter database archivelog;

alter database force logging;

alter database open;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     14

Next log sequence to archive   17

Current log sequence           17

SQL>

1.2主库创建日志目录

[oracle@o12u19p adump]$ cd /u01/app/oracle/oradata/orcl

[oracle@o12u19p orcl]$ mkdir -p arch

[oracle@o12u19p orcl]$ ls -ld /u01/app/oracle/oradata/orcl/arch/

drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:17 /u01/app/oracle/oradata/orcl/arch/

1.3主库修改参数

alter system set log_archive_config='dg_config=(orcl,orcldg)'   scope=spfile;

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl'  scope=spfile;

alter system set log_archive_dest_2='service=orcldg valid_for=(online_logfiles,primary_role) db_unique_name=orcldg'   scope=spfile;

alter system set standby_file_management='auto'   scope=spfile;

alter system set fal_server='orcldg'   scope=spfile;

alter system set fal_client='orcl'   scope=spfile;

alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcl/' scope=spfile ;

alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/arch/','/u01/app/oracle/oradata/orcldg/arch/' scope=spfile;

1.4主库增加standbylog

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 6174015488 bytes

Fixed Size                  2938016 bytes

Variable Size            1241516896 bytes

Database Buffers         4915724288 bytes

Redo Buffers               13836288 bytes

Database mounted.

Database opened.

SQL>

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

    GROUP# TYPE

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

MEMBER

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

         4 ONLINE

/u01/app/oracle/oradata/orcl/redo04.log

         3 ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

         2 ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

    GROUP# TYPE

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

MEMBER

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

         1 ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

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

BYTES/1024/1024     GROUP#    THREAD#

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

             50          1          1

             50          2          1

             50          3          1

             50          4          1

alter database add standby logfile

  group 5 ('/u01/app/oracle/oradata/orcl/arch/standby_redo05.log') size 50m reuse,

  group 6 ('/u01/app/oracle/oradata/orcl/arch/standby_redo06.log') size 50m reuse,

  group 7 ('/u01/app/oracle/oradata/orcl/arch/standby_redo07.log') size 50m reuse,

  group 8 ('/u01/app/oracle/oradata/orcl/arch/standby_redo08.log') size 50m reuse,

 group 9 ('/u01/app/oracle/oradata/orcl/arch/standby_redo09.log') size 50m reuse;

select group#,type,member from v$logfile;

1.5生成pfile 将pfile和密码文件拷到备库

SQL> create pfile from spfile;

File created.

[oracle@o12u19p dbs]$ scp initorcl.ora orapworcl oracle@o12u19s:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs

oracle@o12u19s's password:

initorcl.ora                                                                                                                                100% 1535     1.5KB/s   00:00

orapworcl                                                                                                                                   100% 7680     7.5KB/s   00:00

[oracle@o12u19p dbs]$

3.2备库操作

3.2.1备库修改spfile

[oracle@o12u19s ~]$ cd $ORACLE_HOME/dbs

[oracle@o12u19s dbs]$ ls

init.ora  initorcl.ora  orapworcl

[oracle@o12u19s dbs]$ vi initorcl.ora

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=4781506560

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

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

orcl.__pga_aggregate_target=2063597568

orcl.__sga_target=6174015488

orcl.__shared_io_pool_size=318767104

orcl.__shared_pool_size=1006632960

orcl.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/'

*.db_name='orcl'

*.db_unique_name='orcldg'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=10240m

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

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

*.enable_pluggable_database=true

*.fal_client='orcldg'

*.fal_server='orcl'

*.log_archive_config='dg_config=(orcl,orcldg)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcldg/arch/','/u01/app/oracle/oradata/orcl/arch/'

*.open_cursors=300

*.pga_aggregate_target=1960m

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=5880m

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

二、备库操作

2.1备库修改spfile

[oracle@o12u19s ~]$ cd $ORACLE_HOME/dbs

[oracle@o12u19s dbs]$ ls

init.ora  initorcl.ora  orapworcl

[oracle@o12u19s dbs]$ vi initorcl.ora

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=4781506560

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

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

orcl.__pga_aggregate_target=2063597568

orcl.__sga_target=6174015488

orcl.__shared_io_pool_size=318767104

orcl.__shared_pool_size=1006632960

orcl.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/'

*.db_name='orcl'

*.db_unique_name='orcldg'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=10240m

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

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

*.enable_pluggable_database=true

*.fal_client='orcldg'

*.fal_server='orcl'

*.log_archive_config='dg_config=(orcl,orcldg)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcldg/arch/','/u01/app/oracle/oradata/orcl/arch/'

*.open_cursors=300

*.pga_aggregate_target=1960m

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=5880m

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

2.2创建相关目录

cd /u01/app/oracle

mkdir -p oradata/orcldg/arch

mkdir -p fast_recovery_area

mkdir -p admin/orcl/adump

[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/fast_recovery_area

drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:43 /u01/app/oracle/fast_recovery_area

[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/oradata/orcldg/arch

drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:42 /u01/app/oracle/oradata/orcldg/arch

[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/admin/orcl/adump/

drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:43 /u01/app/oracle/admin/orcl/adump/

[oracle@o12u19s oracle]$

2.3备库配置静态监听

cd $ORACLE_HOME/network/admin

cd samples/

cp * $ORACLE_HOME/network/admin

cd ..

listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora

[oracle@o12u19s admin]$ vi listener.ora

LISTENER_ORCLDG =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

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

      (SID_NAME = orcldg)

    )

)

[oracle@o12u19s admin]$ lsnrctl start

2.4主备库均配置 tnsnames

[oracle@o12u19s admin]$ vi tnsnames.ora

[oracle@o12u19s admin]$ cat tnsnames.ora

ORCL =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

    (SERVICE_NAME = orcl)

      (SERVER = DEDICATED)

    )

 )

ORCLDG =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg)

      (SERVER = DEDICATED)

    )

 )

2.5修改参数文件名和密码文件名

[oracle@o12u19s admin]$ cd $ORACLE_HOME/dbs

[oracle@o12u19s dbs]$ ls

init.ora  initorcl.ora  orapworcl

[oracle@o12u19s dbs]$ mv initorcl.ora initorcldg.ora

[oracle@o12u19s dbs]$ mv orapworcl orapworcldg

[oracle@o12u19s dbs]$ ls

init.ora  initorcldg.ora  orapworcldg

[oracle@o12u19s dbs]$

2.6测试登录

[oracle@o12u19s dbs]$ sqlplus sys/oracle_4U@orcldg as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:00:18 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit

Disconnected

[oracle@o12u19s dbs]$ sqlplus sys/oracle_4U@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:00:26 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2.7备库启动到nomount状态下

[oracle@o12u19s dbs]$ export ORACLE_SID=orcldg

[oracle@o12u19s dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:01:20 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 6174013840 bytes

Fixed Size                  9148816 bytes

Variable Size            1056964608 bytes

Database Buffers         5100273664 bytes

Redo Buffers                7626752 bytes

[oracle@o12u19s ~]$ cd /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/

[oracle@o12u19s trace]$ tail -f alert_orcldg.log

三、主库备份

主库做备份

mkdir -p /backup

chown -R oracle:oinstall /backup/

[root@o12u19p ~]# su - oracle

Last login: Tue Jun 18 15:23:09 CST 2024 on pts/3

[oracle@o12u19p ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 18 16:06:34 2024

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

connected to target database: ORCL (DBID=1699750341)

RMAN> rman target /

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format '/backup/data_%d_%T_%s_%p' tag 'data';

sql 'alter system archive log current';

backup archivelog all format='/backup/arch_%d_%T_%s_%p' tag 'arc';

backup current controlfile for standby format='/backup/ctl_%d_%T_%s_%p' tag 'cur';

release channel c1;

release channel c2;

}

四、备库恢复

[oracle@o12u19p ~]$ cd /backup/

[oracle@o12u19p backup]$ ls

arch_ORCL_20240618_32_1  arch_ORCL_20240618_34_1  data_ORCL_20240618_25_1  data_ORCL_20240618_27_1  data_ORCL_20240618_29_1

arch_ORCL_20240618_33_1  ctl_ORCL_20240618_35_1   data_ORCL_20240618_26_1  data_ORCL_20240618_28_1  data_ORCL_20240618_30_1

[oracle@o12u19p backup]$ scp * oracle@o12u19s:/backup

[oracle@o12u19s ~]$ cd /backup/

[oracle@o12u19s backup]$ ls

arch_ORCL_20240618_32_1  arch_ORCL_20240618_34_1  data_ORCL_20240618_25_1  data_ORCL_20240618_27_1  data_ORCL_20240618_29_1

arch_ORCL_20240618_33_1  ctl_ORCL_20240618_35_1   data_ORCL_20240618_26_1  data_ORCL_20240618_28_1  data_ORCL_20240618_30_1

[oracle@o12u19s backup]$ export ORACLE_SID=orcldg

[oracle@o12u19s backup]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 18 16:13:11 2024

Version 19.3.0.0.0

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

connected to target database: ORCL (not mounted)

RMAN>

 restore standby controlfile from '/backup/ctl_ORCL_20240618_35_1';

alter database mount;

catalog start with "/backup";

Run{

allocate channel c1 type disk;

allocate channel c2 type disk;

restore database;

switch datafile all;

switch tempfile all;

recover database;

release channel c1;

release channel c2;

}

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

    GROUP# TYPE

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

MEMBER

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

         4 ONLINE

/u01/app/oracle/oradata/orcl/redo04.log

         3 ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

         2 ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

    GROUP# TYPE

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

MEMBER

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

         1 ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

         5 STANDBY

/u01/app/oracle/oradata/orcl/arch/standby_redo05.log

         6 STANDBY

/u01/app/oracle/oradata/orcl/arch/standby_redo06.log

    GROUP# TYPE

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

MEMBER

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

         7 STANDBY

/u01/app/oracle/oradata/orcl/arch/standby_redo07.log

         8 STANDBY

/u01/app/oracle/oradata/orcl/arch/standby_redo08.log

         9 STANDBY

/u01/app/oracle/oradata/orcl/arch/standby_redo09.log

9 rows selected.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 4;

Database altered.

五、开启日志应用

 alter database recover managed standby database disconnect from session;