12.2ActiveDataguardinCDBonNonASM

Z时代
2024-01-10
分类：综合

database

12.2 Active Dataguard in CDB on Non-ASM

Creating Physical Standby Active DataGuard Configuration in 12.2.0.1 Container Database

AIM: Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command on 12.2 Database in CDB/PDB environment (No need to take backup of primary database)

Contents

___________________________________________________________________________________________________

0. Introduction
1. Environment
2. Enable Forced Logging on Primary
3. Check Password File on Primary
4. Configure a Standby Redo Log on Primary
5. Verify Archive Mode Enabled on Primary
6. Set Primary Database Initialization Parameters
7. Transfer PFILE AND PASSWORD FILE TO STANDBY SIDE
8. Prepare an Initialization Parameter File for the Standby Database on Standby
9. Crete required directories on Standby
10. Add below entry in ORATAB on Standby
11. Create spfile from pfile on Standby
12. Configure TNS Entries on Primary
13. Configure TNS Entries on Standby
14. Verify connection ‘AS SYSDBA’ from Primary
15. Create Standby Database using duplicate command
16. Enable MRP on Standby
17. Open PDB in READ-ONLY Mode
18. Verify the log shipping Is Performing Properly
19. Verify the Physical Standby Database Is Performing Properly
________________________________________________________________________________________________

0. Introduction

PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files.

Definition of Active Dataguard:

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

1. Environment

Source:

Platform : Linuxx86_64

Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101

DB Version : Oracle 12.2.0.1, File system: Normal

CDB Name : CDB1

PDB Name : PDB1

DB_UNIQUE_NAME : CDB1

Flashback : Disabled

Oracle Home Path : /u01/app/oracle/product/12.2.0.1

Target:

Platform : Linuxx86_64

Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102

DB Version : Oracle 12.2.0.1, File system: Normal

CDB Name : CDB1

PDB Name : PDB1

DB_UNIQUE_NAME : CDB1_DG

Flashback : Disabled

Oracle Home Path : /u01/app/oracle/product/12.2.0.1

2. Enable Forced Logging on Primary

SQL> select name, open_mode,cdb from v$database;

NAME OPEN_MODE CDB

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

CDB1 READ WRITE YES <----

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>

SQL> col name for a30

SQL> select name,open_mode,con_id,dbid from v$containers;

NAME OPEN_MODE CON_ID DBID

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

CDB$ROOT READ WRITE 1 931007404

PDB$SEED READ ONLY 2 302211347

PDB1 READ WRITE 3 19275046

SQL>

SQL> select force_logging from v$database;

FORCE_LOGGING

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

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES <----

SQL>

3. Check Password File on Primary

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwCDB1
-rw-r-----. 1 oracle dba 3584 Oct 13  2017 orapwCDB1
[oracle@rac1 dbs]$

4. Configure a Standby Redo Log on Primary

Note :

-- Since we have 3 online redo log file groups, we need to create 4(3+1) Standby redo log file groups

-- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>

SQL> set lines 180

SQL> col MEMBER for a60

SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

THREAD# GROUP# MEMBER BYTES

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

1 3 /u01/app/oracle/oradata/CDB1/redo03.log 209715200

1 2 /u01/app/oracle/oradata/CDB1/redo02.log 209715200

1 1 /u01/app/oracle/oradata/CDB1/redo01.log 209715200

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ("/u01/app/oracle/oradata/CDB1/redo04.log") SIZE 200M; <---

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ("/u01/app/oracle/oradata/CDB1/redo05.log") SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ("/u01/app/oracle/oradata/CDB1/redo06.log") SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ("/u01/app/oracle/oradata/CDB1/redo07.log") SIZE 200M;

Database altered.

SQL>

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

3 ONLINE /u01/app/oracle/oradata/CDB1/redo03.log NO 0

2 ONLINE /u01/app/oracle/oradata/CDB1/redo02.log NO 0

1 ONLINE /u01/app/oracle/oradata/CDB1/redo01.log NO 0

4 STANDBY /u01/app/oracle/oradata/CDB1/redo04.log NO 0

5 STANDBY /u01/app/oracle/oradata/CDB1/redo05.log NO 0

6 STANDBY /u01/app/oracle/oradata/CDB1/redo06.log NO 0

7 STANDBY /u01/app/oracle/oradata/CDB1/redo07.log NO 0

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

GROUP# MEMBER BYTES

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

4 /u01/app/oracle/oradata/CDB1/redo04.log 209715200

5 /u01/app/oracle/oradata/CDB1/redo05.log 209715200

6 /u01/app/oracle/oradata/CDB1/redo06.log 209715200

7 /u01/app/oracle/oradata/CDB1/redo07.log 209715200

SQL>

5. Verify Archive Mode Enabled on Primary

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u02/arch/CDB1

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL>

6. Set Primary Database Initialization Parameters

SQL> show parameter pfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileapac.ora
SQL> create pfile="/home/oracle/initCDB1_before.ora" from spfile;
File created.
SQL> alter system set db_unique_name="CDB1" scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(CDB1,CDB1_DG)" scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1="LOCATION=/u02/arch/CDB1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1" scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2="SERVICE=CDB1_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DG" scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT="%t_%s_%r.dbf" SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET fal_client=CDB1 scope=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=CDB1_DG scope=both;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT="/u01/app/oracle/oradata/CDB1_DG","/u01/app/oracle/oradata/CDB1","/u01/app/oracle/oradata/CDB1_DG/PDB1","/u01/app/oracle/oradata/CDB1/PDB1" SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT="/u01/app/oracle/oradata/CDB1_DG","/u01/app/oracle/oradata/CDB1" SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> create pfile="/home/oracle/initCDB1.ora" from spfile;
File created.
SQL>
[oracle@rac1 ~]$ cat initCDB1.ora
CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=1291845632
CDB1.__inmemory_ext_roarea=0
CDB1.__inmemory_ext_rwarea=0
CDB1.__java_pool_size=16777216
CDB1.__large_pool_size=33554432
CDB1.__oracle_base="/u01/app/oracle"#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=620756992
CDB1.__sga_target=1845493760
CDB1.__shared_io_pool_size=100663296
CDB1.__shared_pool_size=385875968
CDB1.__streams_pool_size=0
*.audit_file_dest="/u01/app/oracle/admin/CDB1/adump"
*.audit_trail="db"
*.compatible="12.2.0"
*.control_files="/u01/app/oracle/oradata/CDB1/control01.ctl","/u01/app/oracle/oradata/CDB1/control02.ctl"
*.db_block_size=8192
*.db_file_name_convert="/u01/app/oracle/oradata/CDB1_DG","/u01/app/oracle/oradata/CDB1","/u01/app/oracle/oradata/CDB1_DG/PDB1","/u01/app/oracle/oradata/CDB1/PDB1"
*.db_name="CDB1"
*.db_unique_name="CDB1"
*.diagnostic_dest="/u01/app/oracle"
*.dispatchers="(PROTOCOL=TCP) (SERVICE=CDB1XDB)"
*.enable_pluggable_database=true
*.fal_client="CDB1"
*.fal_server="CDB1_DG"
*.local_listener="LISTENER_CDB1"
*.log_archive_config="DG_CONFIG=(CDB1,CDB1_DG)"
*.log_archive_dest_1="LOCATION=/u02/arch/CDB1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1"
*.log_archive_dest_2="SERVICE=CDB1_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DG"
*.log_archive_dest_state_1="ENABLE"
*.log_archive_dest_state_2="ENABLE"
*.log_archive_format="%t_%s_%r.dbf"
*.log_archive_max_processes=30
*.log_file_name_convert="/u01/app/oracle/oradata/CDB1_DG","/u01/app/oracle/oradata/CDB1"
*.nls_language="AMERICAN"
*.nls_territory="AMERICA"
*.open_cursors=300
*.pga_aggregate_target=583m
*.processes=300
*.remote_login_passwordfile="EXCLUSIVE"
*.sga_target=1746m
*.standby_file_management="AUTO"
*.undo_tablespace="UNDOTBS1"
[oracle@rac1 ~]$

7. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE

Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 ~]$ scp initCDB1.ora oracle@rac2:/home/oracle/initCDB1_DG.ora

The authenticity of host "rac2 (192.168.2.102)" can"t be established.

RSA key fingerprint is f3:d9:d8:f4:f0:bb:8b:15:34:86:cf:b5:85:fe:06:48.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added "rac2,192.168.2.102" (RSA) to the list of known hosts.

oracle@rac2"s password:

initCDB1.ora 100% 1769 1.7KB/s 00:00

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

[oracle@rac1 dbs]$ scp orapwCDB1 oracle@rac2:/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1_DG

oracle@rac2"s password:

orapwCDB1 100% 3584 3.5KB/s 00:00

[oracle@rac1 dbs]$

8. Prepare an Initialization Parameter File for the Standby Database on Standby

Edit shown highlighted to set it up for the standby role

[oracle@rac2 ~]$ cat initCDB1_DG_DG.ora
CDB1_DG.__data_transfer_cache_size=0
CDB1_DG.__db_cache_size=1291845632
CDB1_DG.__inmemory_ext_roarea=0
CDB1_DG.__inmemory_ext_rwarea=0
CDB1_DG.__java_pool_size=16777216
CDB1_DG.__large_pool_size=33554432
CDB1_DG.__oracle_base="/u01/app/oracle"#ORACLE_BASE set from environment
CDB1_DG.__pga_aggregate_target=620756992
CDB1_DG.__sga_target=1845493760
CDB1_DG.__shared_io_pool_size=100663296
CDB1_DG.__shared_pool_size=385875968
CDB1_DG.__streams_pool_size=0
*.audit_file_dest="/u01/app/oracle/admin/CDB1_DG/adump"
*.audit_trail="db"
*.compatible="12.2.0"
*.control_files="/u01/app/oracle/oradata/CDB1_DG/control01.ctl","/u01/app/oracle/oradata/CDB1_DG/control02.ctl"
*.db_block_size=8192
*.db_file_name_convert="/u01/app/oracle/oradata/CDB1","/u01/app/oracle/oradata/CDB1_DG","/u01/app/oracle/oradata/CDB1/PDB1","/u01/app/oracle/oradata/CDB1_DG/PDB1"
*.db_name="CDB1"
*.db_unique_name="CDB1_DG"
*.diagnostic_dest="/u01/app/oracle"
*.dispatchers="(PROTOCOL=TCP) (SERVICE=CDB1_DGXDB)"
*.enable_pluggable_database=true
*.fal_client="CDB1_DG"
*.fal_server="CDB1"
*.local_listener="LISTENER_CDB1_DG"
*.log_archive_config="DG_CONFIG=(CDB1,CDB1_DG)"
*.log_archive_dest_1="LOCATION=/u02/arch/CDB1_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1_DG"
*.log_archive_dest_2="SERVICE=CDB1_DG_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DG_DG"
*.log_archive_dest_state_1="ENABLE"
*.log_archive_dest_state_2="ENABLE"
*.log_archive_format="%t_%s_%r.dbf"
*.log_archive_max_processes=30
*.log_file_name_convert="/u01/app/oracle/oradata/CDB1","/u01/app/oracle/oradata/CDB1_DG"
*.nls_language="AMERICAN"
*.nls_territory="AMERICA"
*.open_cursors=300
*.pga_aggregate_target=583m
*.processes=300
*.remote_login_passwordfile="EXCLUSIVE"
*.sga_target=1746m
*.standby_file_management="AUTO"
*.undo_tablespace="UNDOTBS1"
[oracle@rac2 ~]$

9. Crete required directories on Standby

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/CDB1_DG/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/CDB1_DG
[oracle@rac2 ~]$ mkdir -p /u02/arch/CDB1_DG
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/CDB1_DG/PDB1

10. Add below entry in ORATAB on Standby

CDB1_DG:/u01/app/oracle/product/12.2.0.1:N

11. Create spfile from pfile on Standby

[oracle@rac2 ~]$ . oraenv

ORACLE_SID = [CDB1] ? CDB1_DG

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 20:00:53 2018

Connected to an idle instance.

SQL> startup nomount pfile="/home/oracle/initCDB1_DG.ora";

ORACLE instance started.

Total System Global Area 1845493760 bytes

Fixed Size 8793976 bytes

Variable Size 553648264 bytes

Database Buffers 1275068416 bytes

Redo Buffers 7983104 bytes

SQL>

SQL> create spfile from pfile="/home/oracle/initCDB1_DG.ora";

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1845493760 bytes

Fixed Size 8793976 bytes

Variable Size 553648264 bytes

Database Buffers 1275068416 bytes

Redo Buffers 7983104 bytes

SQL>

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/12.2.0

.1/dbs/spfileCDB1_DG.ora

SQL>

12. Configure TNS Entries on Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
CDB1_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1_DG)(UR = A)
    )
  )
[oracle@rac1 admin]$
[oracle@rac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:16:40
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-JUL-2018 20:43:58
Uptime                    0 days 0 hr. 32 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1.rajasekhar.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "5b70bb17b6741d68e055000000000001" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping CDB1
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:22:20
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1)))
OK (0 msec)
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping CDB1_DG
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:28:23
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
OK (0 msec)
[oracle@rac1 admin]$

13. Configure TNS Entries on Standby

[oracle@rac2 ~]$ cd /u01/app/oracle/product/12.2.0.1/network/admin
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
[oracle@rac2 admin]$
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1_DG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )
CDB1_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1_DG)(UR = A)
    )
  )
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )
[oracle@rac2 admin]$
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1845493760 bytes
Fixed Size                  8793976 bytes
Variable Size             553648264 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7983104 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:37:31
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-JUL-2018 21:36:02
Uptime                    0 days 0 hr. 1 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "CDB1_DG" has 1 instance(s).
  Instance "CDB1_DG", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$
[oracle@rac2 ~]$ tnsping CDB1
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:38:14
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping CDB1_DG
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:38:22
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
OK (0 msec)
[oracle@rac2 ~]$

14. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus sys/sys@CDB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:38:59 2018

Connected to:

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

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus sys/sys@CDB1_DG as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:39:21 2018

Connected to:

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

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$

15. Create Standby Database using duplicate command

On Primary:

[oracle@rac1 ~]$ rman target sys/sys@CDB1 auxiliary sys/sys@CDB1_DG

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jul 17 21:49:31 2018

connected to target database: CDB1 (DBID=931007404)

connected to auxiliary database: CDB1 (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 17-JUL-18

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile "/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1" auxiliary format

"/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1_DG" ;

}

executing Memory Script

Starting backup at 17-JUL-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=297 device type=DISK

Finished backup at 17-JUL-18

contents of Memory Script:

{

restore clone from service "CDB1" standby controlfile;

}

executing Memory Script

Starting restore at 17-JUL-18

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 CDB1

channel ORA_AUX_DISK_1: restoring control file

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

output file name=/u01/app/oracle/oradata/CDB1_DG/control01.ctl

output file name=/u01/app/oracle/oradata/CDB1_DG/control02.ctl

Finished restore at 17-JUL-18

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

"/u01/app/oracle/oradata/CDB1_DG/temp01.dbf";

set newname for tempfile 2 to

"/u01/app/oracle/oradata/CDB1_DG/pdbseed/temp012017-10-13_17-53-30-404-PM.dbf";

set newname for tempfile 3 to

"/u01/app/oracle/oradata/CDB1_DG/PDB1/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/CDB1_DG/system01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/CDB1_DG/pdbseed/system01.dbf";

set newname for datafile 6 to

"/u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/CDB1_DG/users01.dbf";

set newname for datafile 8 to

"/u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf";

set newname for datafile 9 to

"/u01/app/oracle/oradata/CDB1_DG/PDB1/system01.dbf";

set newname for datafile 10 to

"/u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.dbf";

set newname for datafile 11 to

"/u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.dbf";

set newname for datafile 12 to

"/u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf";

restore

from nonsparse from service

"CDB1" clone database

;

sql "alter system archive log current";

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/CDB1_DG/temp01.dbf in control file

renamed tempfile 2 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/temp012017-10-13_17-53-30-404-PM.dbf in control file

renamed tempfile 3 to /u01/app/oracle/oradata/CDB1_DG/PDB1/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 17-JUL-18

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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CDB1_DG/system01.dbf

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

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf

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

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf

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

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CDB1_DG/users01.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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf

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

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1_DG/PDB1/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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.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 CDB1

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

channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf

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

Finished restore at 17-JUL-18

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=4 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/system01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=8 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=9 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/users01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=10 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=11 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/system01.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=12 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=13 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=14 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf

Finished Duplicate Db at 17-JUL-18

RMAN> exit

Recovery Manager complete.

[oracle@rac1 ~]$

16. Enable MRP on Standby

[oracle@rac2 CDB1_DG]$ . oraenv

ORACLE_SID = [CDB1_DG] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac2 CDB1_DG]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:53:31 2018

Connected to:

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

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

NAME OPEN_MODE DATABASE_ROLE CDB

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

CDB1 MOUNTED PHYSICAL STANDBY YES

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> !ps -ef | grep mrp

oracle 15074 14602 0 21:56 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp

oracle 15076 15074 0 21:56 pts/0 00:00:00 grep mrp

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

NAME OPEN_MODE DATABASE_ROLE CDB

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

CDB1 READ ONLY WITH APPLYPHYSICAL STANDBY YES

SQL> col name for a30

SQL> select name,open_mode,con_id,dbid from v$containers;

NAME OPEN_MODE CON_ID DBID

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

CDB$ROOT READ ONLY 1 931007404

PDB$SEED READ ONLY 2 302211347

PDB1 MOUNTED 3 19275046

SQL>

SQL> !ps -ef | grep mrp

oracle 15080 1 0 21:56 ? 00:00:00 ora_mrp0_CDB1_DG <----

oracle 15091 14602 0 21:57 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp

oracle 15093 15091 0 21:57 pts/0 00:00:00 grep mrp

SQL>

17. Open PDB in READ-ONLY Mode

SQL> alter pluggable database all open read only;

Pluggable database altered.

SQL>

SQL> select name,open_mode,con_id,dbid from v$containers;

NAME OPEN_MODE CON_ID DBID

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

CDB$ROOT READ ONLY 1 931007404

PDB$SEED READ ONLY 2 302211347

PDB1 READ ONLY 3 19275046

SQL>

18. Verify the log shipping Is Performing Properly

On Primary

Connected to:

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

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>

SQL> show parameters db_unique_name;

NAME TYPE VALUE

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

db_unique_name string CDB1

SQL>

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

9 12-JUL-18 17-JUL-18

10 17-JUL-18 17-JUL-18

11 17-JUL-18 17-JUL-18

12 17-JUL-18 17-JUL-18

13 17-JUL-18 17-JUL-18

14 17-JUL-18 17-JUL-18

15 17-JUL-18 17-JUL-18

11 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

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

MAX(SEQUENCE#)

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

18 <-----

SQL>

On Standby

SQL> set lines 180

SQL> col MEMBER for a60

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

3 ONLINE /u01/app/oracle/oradata/CDB1_DG/redo03.log NO 0

2 ONLINE /u01/app/oracle/oradata/CDB1_DG/redo02.log NO 0

1 ONLINE /u01/app/oracle/oradata/CDB1_DG/redo01.log NO 0

4 STANDBY /u01/app/oracle/oradata/CDB1_DG/redo04.log NO 0

5 STANDBY /u01/app/oracle/oradata/CDB1_DG/redo05.log NO 0

6 STANDBY /u01/app/oracle/oradata/CDB1_DG/redo06.log NO 0

7 STANDBY /u01/app/oracle/oradata/CDB1_DG/redo07.log NO 0

7 rows selected.

SQL>

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

12 17-JUL-18 17-JUL-18

13 17-JUL-18 17-JUL-18

14 17-JUL-18 17-JUL-18

15 17-JUL-18 17-JUL-18

16 17-JUL-18 17-JUL-18

17 17-JUL-18 17-JUL-18

18 17-JUL-18 17-JUL-18

7 rows selected.

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

MAX(SEQUENCE#)

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

18 <----

SQL>

19. Verify the Physical Standby Database Is Performing Properly

The Pluggable database PDB1 has been opened in READ ONLY mode, but the Container Database is running as an Active Standby database and applying changes as they are received from the primary even when the Standby Container database and all the associated pluggable databases have been opened in read only mode. This means Active Dataguard is working..

On Primary

SQL> alter session set container=pdb1;
Session altered.
SQL> create user raj identified by raj default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to raj;
Grant succeeded.
SQL> alter user raj quota unlimited on users;
User altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$ sqlplus raj/raj@pdb1  <----
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 22:19:10 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show user
USER is "RAJ"
SQL> create table sugi(Name varchar2(10), Role varchar2(10));
Table created.
SQL> insert into sugi values ("&a","&b");
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into sugi values ("&a","&b")
new   1: insert into sugi values ("RAJ","DBA")
1 row created.
SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into sugi values ("&a","&b")
new   1: insert into sugi values ("SUGI","DBA")
1 row created.
SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into sugi values ("&a","&b")
new   1: insert into sugi values ("TEJU","DBA")
1 row created.
SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into sugi values ("&a","&b")
new   1: insert into sugi values ("VIJAY","DBA")
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from sugi;
NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn sys/sys@pdb1 as sysdba
Connected.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> conn sys/sys@cdb1 as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
SQL> select max(sequence#) from v$archived_log where archived="YES";
MAX(SEQUENCE#)
--------------
21 <----
SQL>
SQL> select dest_id,destination,status from v$archive_dest_status where dest_id < 3; DEST_ID DESTINATION STATUS ---------- ------------------------- --------- 1 /u02/arch/CDB1 VALID 2 CDB1_DG VALID SQL>

On Standby

[oracle@rac2 admin]$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-JUL-2018 10:10:06
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =rac2.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1)))
OK (10 msec)
[oracle@rac2 admin]$
[oracle@rac2 ~]$ sqlplus raj/raj@pdb1 <----
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 22:27:42 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Tue Jul 17 2018 22:19:10 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from sugi;
NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SQL>
SQL> conn sys/sys@cdb1_dg as sysdba
Connected.
SQL> select process,status,thread#,sequence#,blocks from v$managed_standby where process like "%MRP%";
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCKS
--------- ------------ ---------- ---------- ----------
MRP0      WAIT_FOR_LOG          1         22          0
SQL>
SQL> select max(sequence#) from v$archived_log where applied="YES";
MAX(SEQUENCE#)
--------------
21 <---
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

以上是 12.2ActiveDataguardinCDBonNonASM 的全部内容，来源链接： utcz.com/z/534839.html