Ajustando o bash_profile do ambiente Standby:
[oracle@fornix2 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
ORACLE_SID=CORTEXDR; export ORACLE_SID
JAVA_HOME=/usr/bin/java; export JAVA_HOME
ORACLE_BASE=/oracle/19.3.0/base; export ORACLE_BASE
ORACLE_HOME=/oracle/19.3.0/product; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
export TEMP=/tmp
export TMPDIR=/tmp
umask 022
[oracle@fornix2 ~]$
Criando diretórios dentro do ASM (diskgroup de dados):
[grid@fornix2 ~]$ asmcmd
ASMCMD> ls
DG_DATA/
DG_FRA/
DG_GRID/
DG_RECO/
ASMCMD> cd DG_DATA/
ASMCMD> mkdir CORTEX
ASMCMD> mkdir CORTEXDR
ASMCMD> ls
CORTEX/
CORTEXDR/
ASMCMD>
Criando um arquivo pfile com o usuário oracle, utilizando apenas o parâmetro DB_NAME:
[oracle@fornix2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@fornix2 dbs]$ cat initCORTEXDR.ora
DB_NAME=CORTEX
[oracle@fornix2 dbs]$
Logando no ambiente primary, e copiando o password file para o ambiente standby:
[oracle@fornix1 dbs]$ scp orapwcortex oracle@fornix2:/oracle/19.3.0/product/dbs/orapwCORTEXDR
oracle@fornix2's password:
orapwcortex 100% 2048 1.3MB/s 00:00
[oracle@fornix1 dbs]$
Configurando o arquivo tnsnames.ora do standby usando como referência o do primary:
[oracle@fornix2 admin]$ cp tnsnames.ora tnsnames.ora.old
[oracle@fornix2 admin]$
[oracle@fornix1 dbs]$ cd $ORACLE_HOME/network/admin
[oracle@fornix1 admin]$ scp tnsnames.ora oracle@fornix2:/oracle/19.3.0/product/network/admin
oracle@fornix2's password:
tnsnames.ora 100% 825 326.2KB/s 00:00
[oracle@fornix1 admin]$
[oracle@fornix2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/19.3.0/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CORTEX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fornix1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cortex.localdomain)
)
)
LISTENER_LISTENER =
(ADDRESS = (PROTOCOL = TCP)(HOST = fornix1.localdomain)(PORT = 1521))
CORTEXDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fornix2.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cortexDR.localdomain)
)(UR=A)
)
RMANDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMANDB)
)
)
[oracle@fornix2 admin]$
Checando se o arquivo sqlnet.ora está devidamente configurado:
[oracle@fornix2 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/19.3.0/product/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@fornix2 admin]$
Para evitar possíveis problemas no momento de realizar o restore do banco, é indicado registrar de maneira estática o database no listener:
[grid@fornix2 admin]$ cat listener.ora
#Backup file is /grid/19.3.0/base/crsdata/fornix2/output/listener.ora.bak.fornix2.grid line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent
LISTENERDR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = fornix2.localdomain) (PORT = 1522))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))
)
)
SID_LIST_LISTENERDR=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=CORTEXDR.localdomain)
(SID_NAME=CORTEXDR)
(ORACLE_HOME=/oracle/19.3.0/product)
)
)
[grid@fornix2 admin]$ lsnrctl start LISTENERDR
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-MAR-2021 11:20:02
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /grid/19.3.0/product/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /grid/19.3.0/product/network/admin/listener.ora
Log messages written to /grid/19.3.0/base/diag/tnslsnr/fornix2/listenerdr/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fornix2.localdomain)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fornix2.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENERDR
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 06-MAR-2021 11:20:02
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid/19.3.0/product/network/admin/listener.ora
Listener Log File /grid/19.3.0/base/diag/tnslsnr/fornix2/listenerdr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fornix2.localdomain)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "CORTEXDR.localdomain" has 1 instance(s).
Instance "CORTEXDR", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[grid@fornix2 admin]$ lsnrctl service LISTENERDR
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-MAR-2021 11:20:51
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fornix2.localdomain)(PORT=1522)))
Services Summary...
Service "CORTEXDR.localdomain" has 1 instance(s).
Instance "CORTEXDR", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[grid@fornix2 admin]$
Criando os diretórios de dump no standby:
[oracle@fornix2 admin]$ cd $ORACLE_BASE/admin
[oracle@fornix2 admin]$ mkdir CORTEXDR
[oracle@fornix2 admin]$ cd CORTEXDR/
[oracle@fornix2 CORTEXDR]$ mkdir adump
[oracle@fornix2 CORTEXDR]$ mkdir cdump
Subindo a instância no standby:
[oracle@fornix2 CORTEXDR]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 6 11:23:49 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 243268216 bytes
Fixed Size 8895096 bytes
Variable Size 180355072 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
CORTEXDR STARTED
SQL>
Chamando o RMAN:
[oracle@fornix2 dbs]$ rman target sys/oracle@CORTEX AUXILIARY sys/oracle@CORTEXDR;
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 6 12:07:32 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CORTEX (DBID=548968087)
connected to auxiliary database: CORTEX (not mounted)
RMAN>
O bloco abaixo permitirá o duplicate do banco para o standby. Note que o parâmetro nofilenamecheck se encontra nele pois o nome dos diskgroups que abrigarão os arquivos são os mesmos da origem:
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE
set 'db_unique_name'='cortexDR'
set control_files='+DG_DATA/CORTEXDR/control.ctl'
set db_create_file_dest='+DG_DATA'
set db_create_online_log_dest_1='+DG_FRA'
set db_create_online_log_dest_2='+DG_DATA'
set db_recovery_file_dest='+DG_FRA'
set DB_RECOVERY_FILE_DEST_SIZE='1G'
set audit_file_dest='/oracle/19.3.0/base/admin/CORTEXDR/adump'
set core_dump_dest='/oracle/19.3.0/base/admin/CORTEXDR/cdump' nofilenamecheck; }
Execução:
RMAN> run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
2> 3> 4> 5> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE
set 'db_unique_name'='cortexDR'
set control_files='+DG_DATA/CORTEXDR/control.ctl'
set db_create_file_dest='+DG_DATA'
set db_create_online_log_dest_1='+DG_FRA'
set db_create_online_log_dest_2='+DG_DATA'
set db_recovery_file_dest='+DG_FRA'
set DB_RECOVERY_FILE_DEST_SIZE='1G'
set audit_file_dest='/oracle/19.3.0/base/admin/CORTEXDR/adump'
set core_dump_dest='/oracle/19.3.0/base/admin/CORTEXDR/cdump' nofilenamecheck; }6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=84 device type=DISK
allocated channel: ch2
channel ch2: SID=119 device type=DISK
allocated channel: ch3
channel ch3: SID=38 device type=DISK
Starting Duplicate Db at 06-MAR-21
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/oracle/19.3.0/product/dbs/orapwCORTEXDR' targetfile
'+DG_DATA/CORTEX/PARAMETERFILE/spfile.265.1039034301' auxiliary format
'/oracle/19.3.0/product/dbs/spfileCORTEXDR.ora' ;
sql clone "alter system set spfile= ''/oracle/19.3.0/product/dbs/spfileCORTEXDR.ora''";
}
executing Memory Script
Starting backup at 06-MAR-21
Finished backup at 06-MAR-21
sql statement: alter system set spfile= ''/oracle/19.3.0/product/dbs/spfileCORTEXDR.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''cortexDR'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DG_DATA/CORTEXDR/control.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''+DG_DATA'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_1 =
''+DG_FRA'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_2 =
''+DG_DATA'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''+DG_FRA'' comment=
'''' scope=spfile";
sql clone "alter system set DB_RECOVERY_FILE_DEST_SIZE =
1G comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/oracle/19.3.0/base/admin/CORTEXDR/adump'' comment=
'''' scope=spfile";
sql clone "alter system set core_dump_dest =
''/oracle/19.3.0/base/admin/CORTEXDR/cdump'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''cortexDR'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DG_DATA/CORTEXDR/control.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DG_DATA'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_1 = ''+DG_FRA'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_2 = ''+DG_DATA'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+DG_FRA'' comment= '''' scope=spfile
sql statement: alter system set DB_RECOVERY_FILE_DEST_SIZE = 1G comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/oracle/19.3.0/base/admin/CORTEXDR/adump'' comment= '''' scope=spfile
sql statement: alter system set core_dump_dest = ''/oracle/19.3.0/base/admin/CORTEXDR/cdump'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2583690520 bytes
Fixed Size 8899864 bytes
Variable Size 553648128 bytes
Database Buffers 2013265920 bytes
Redo Buffers 7876608 bytes
allocated channel: ch3
channel ch3: SID=45 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DG_DATA/CORTEXDR/control.ctl';
}
executing Memory Script
Starting backup at 06-MAR-21
channel ch1: starting datafile copy
copying standby control file
output file name=/oracle/19.3.0/product/dbs/snapcf_cortex.f tag=TAG20210306T122236
channel ch1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 06-MAR-21
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 clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 7 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DG_DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 06-MAR-21
channel ch1: starting datafile copy
input datafile file number=00001 name=+DG_DATA/CORTEX/DATAFILE/system.256.1039033445
channel ch2: starting datafile copy
input datafile file number=00005 name=+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
output file name=+DG_DATA/CORTEXDR/DATAFILE/system.259.1066479769 tag=TAG20210306T122248
channel ch1: datafile copy complete, elapsed time: 00:00:55
channel ch1: starting datafile copy
input datafile file number=00003 name=+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
output file name=+DG_DATA/CORTEXDR/DATAFILE/soe.258.1066479771 tag=TAG20210306T122248
channel ch2: datafile copy complete, elapsed time: 00:00:56
channel ch2: starting datafile copy
input datafile file number=00004 name=+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
output file name=+DG_DATA/CORTEXDR/DATAFILE/undotbs1.256.1066479825 tag=TAG20210306T122248
channel ch2: datafile copy complete, elapsed time: 00:00:25
channel ch2: starting datafile copy
input datafile file number=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
output file name=+DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853 tag=TAG20210306T122248
channel ch2: datafile copy complete, elapsed time: 00:00:03
output file name=+DG_DATA/CORTEXDR/DATAFILE/sysaux.257.1066479825 tag=TAG20210306T122248
channel ch1: datafile copy complete, elapsed time: 00:00:34
Finished backup at 06-MAR-21
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=1066479860 file name=+DG_DATA/CORTEXDR/DATAFILE/system.259.1066479769
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1066479860 file name=+DG_DATA/CORTEXDR/DATAFILE/sysaux.257.1066479825
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1066479860 file name=+DG_DATA/CORTEXDR/DATAFILE/undotbs1.256.1066479825
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1066479860 file name=+DG_DATA/CORTEXDR/DATAFILE/soe.258.1066479771
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1066479860 file name=+DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
Finished Duplicate Db at 06-MAR-21
released channel: ch1
released channel: ch2
released channel: ch3
RMAN>
Validando o standby:
[oracle@fornix2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 6 12:38:19 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
+DG_DATA/CORTEXDR/DATAFILE/system.259.1066479769
+DG_DATA/CORTEXDR/DATAFILE/sysaux.257.1066479825
+DG_DATA/CORTEXDR/DATAFILE/undotbs1.256.1066479825
+DG_DATA/CORTEXDR/DATAFILE/soe.258.1066479771
+DG_DATA/CORTEXDR/DATAFILE/users.260.1066479853
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
CORTEXDR MOUNTED
SQL>
Nota-se que o Standby Redologs foram multiplexados, e como isso não é necessário em nosso ambiente, vou dropar os arquivos possíveis:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 1 37 YES UNASSIGNED
5 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
SQL> SELECT TYPE, count(*) FROM V$LOGFILE GROUP BY TYPE;
TYPE COUNT(*)
------- ----------
ONLINE 6
STANDBY 8
SQL> COLUMN MEMBER FORMAT A50
SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;
GROUP# MEMBER
---------- --------------------------------------------------
4 +DG_DATA/CORTEXDR/ONLINELOG/group_4.264.1066479893
4 +DG_FRA/CORTEXDR/ONLINELOG/group_4.264.1066479889
5 +DG_DATA/CORTEXDR/ONLINELOG/group_5.265.1066479905
5 +DG_FRA/CORTEXDR/ONLINELOG/group_5.265.1066479901
6 +DG_DATA/CORTEXDR/ONLINELOG/group_6.267.1066479917
6 +DG_FRA/CORTEXDR/ONLINELOG/group_6.266.1066479911
7 +DG_DATA/CORTEXDR/ONLINELOG/group_7.268.1066479929
7 +DG_FRA/CORTEXDR/ONLINELOG/group_7.267.1066479923
8 rows selected.
SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DG_DATA/CORTEXDR/ONLINELOG/group_4.264.1066479893';
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DG_DATA/CORTEXDR/ONLINELOG/group_5.265.1066479905';
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DG_DATA/CORTEXDR/ONLINELOG/group_6.267.1066479917';
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DG_DATA/CORTEXDR/ONLINELOG/group_7.268.1066479929';
Database altered.
SQL>
Setando os parâmetros no Standby:
SQL> ALTER SYSTEM SET FAL_SERVER=CORTEX;
ALTER SYSTEM SET FAL_CLIENT=CORTEXDR;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CORTEX,CORTEXDR)';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=CORTEX ASYNC DB_UNIQUE_NAME=cortex VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
Iniciando o processo:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
Checando configuração no Standby:
SQL> SET LIN 300
COL FILENAME FORMAT A40
SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME, replace( NAME ,'DG_RECO/CORTEXDR/ARCHIVELOG/2021_03_06/','') FILENAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SQL> SQL>
SEQUENCE# APPLIED FIRST_TIME NEXT_TIME FILENAME
---------- --------- ------------------- ------------------- ----------------------------------------
36 NO 2021-03-06:11:56:00 2021-03-06:12:24:19 +thread_1_seq_36.281.1066480013
37 NO 2021-03-06:12:24:19 2021-03-06:12:54:19 +thread_1_seq_37.282.1066481661
38 NO 2021-03-06:12:54:19 2021-03-06:12:59:52 +thread_1_seq_38.283.1066481993
39 NO 2021-03-06:12:59:52 2021-03-06:13:29:50 +thread_1_seq_39.284.1066483791
SQL>
Criando um novo archive no primary:
[oracle@fornix1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 6 14:00:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
Validando se o standby recebeu a nova sequence:
SQL> SET LIN 300
COL FILENAME FORMAT A40
SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME, replace( NAME ,'DG_RECO/CORTEXDR/ARCHIVELOG/2021_03_06/','') FILENAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SQL> SQL>
SEQUENCE# APPLIED FIRST_TIME NEXT_TIME FILENAME
---------- --------- ------------------- ------------------- ----------------------------------------
36 YES 2021-03-06:11:56:00 2021-03-06:12:24:19 +thread_1_seq_36.281.1066480013
37 YES 2021-03-06:12:24:19 2021-03-06:12:54:19 +thread_1_seq_37.282.1066481661
38 YES 2021-03-06:12:54:19 2021-03-06:12:59:52 +thread_1_seq_38.283.1066481993
39 YES 2021-03-06:12:59:52 2021-03-06:13:29:50 +thread_1_seq_39.284.1066483791
40 YES 2021-03-06:13:29:50 2021-03-06:13:59:49 +thread_1_seq_40.285.1066485589
41 IN-MEMORY 2021-03-06:13:59:49 2021-03-06:14:00:32 +thread_1_seq_41.286.1066485633
6 rows selected.
Outra maneira de monitorar a condição do ambiente é a consulta abaixo (feita no standby):
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 40 2048 16
ARCH CLOSING 1 39 8192 1816
ARCH CLOSING 1 41 1 45
RFS IDLE 1 0 0 0
RFS IDLE 1 42 6922 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 42 6922 409600
10 rows selected.
Após ver que a comunicação entre os ambientes está coesa, podemos configurar a política de archives no RMAN (tanto primary quanto standby):
[oracle@fornix1 dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 6 14:06:20 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CORTEX (DBID=548968087)
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN>
[oracle@fornix2 dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 6 14:07:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CORTEX (DBID=548968087, not open)
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN>
De forma opcional, podemos habilitar o recurso de Flashback no Standby também:
[oracle@fornix2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 6 14:09:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880;
System altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL>
Por último, podemos incluir o standby no Oracle Restart (se atentar que o startupoption precisa estar em MOUNT):
[oracle@fornix2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 6 14:13:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shu immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@fornix2 dbs]$ srvctl add database -db CORTEXDR -instance CORTEXDR -oraclehome /oracle/19.3.0/product -startoption MOUNT -stopoption IMMEDIATE -policy AUTOMATIC -role physical_standby -spfile /oracle/19.3.0/product/dbs/spfileCORTEXDR.ora
[oracle@fornix2 dbs]$ srvctl start database -d CORTEXDR
[oracle@fornix2 dbs]$ srvctl status database -d CORTEXDR
Database is running.
[oracle@fornix2 dbs]$
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish
Pingback: Creating a Logical Standby Database – Bruno Santos da Silva
Pingback: Creating a Logical Standby Database – SWIV