Hi all in 12.1.0.2.0 we use trigger and for 12.2 we use savepoint .
See link: 12.2.0.1.0 Savepoint
We tested in both standalone or RAC grid database when start again CDB get started but PDB remains mounted. So in order to avoid this we user trigger in 12.1 and in 12.2 we use save point.
Below example we execute trigger so on next time when we start CDB automatically all pdb will be in read write. We can modify triggers as per requirement just like save point.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NEWSMRUPGR10 READ WRITE NO
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NEWSMRUPGR10 READ WRITE NO
SQL> CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/ 2 3 4 5 6
Trigger created.
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRCDB SMRCDB SMRCDB READ WRITE PRIMARY NO 0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@TEST:~> tnsping SMRCDB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2021 08:56:06
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SMRCDB)))
OK (0 msec)
oracle@TEST:~> sqlplus sys/----@SMRCDB as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:16 2021
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> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRCDB SMRCDB SMRCDB READ WRITE PRIMARY NO 0
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Warning: You are no longer connected to ORACLE.
SQL> exit
oracle@TEST:~> . oraenv
ORACLE_SID = [SMRCDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
oracle@TEST:~> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:56 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 3712904 bytes
Variable Size 654313592 bytes
Database Buffers 1476395008 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NEWSMRUPGR10 READ WRITE NO
SQL>
