Auto start pluggable database during container database startup

• Below are the steps to start pluggable DB automatically during startup of container database.

1.      Pre validation

-        Check the current status of pluggable database if it no-mount status then open it in read write mode.

[oracle@19cnode1 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:17:28 2020

Version 19.3.0.0.0

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 1593832624 bytes

Fixed Size                  9135280 bytes

Variable Size             956301312 bytes

Database Buffers          620756992 bytes

Redo Buffers                7639040 bytes

Database mounted.

Database opened.

-        Check status of pluggable database(sale).

    CON_ID NAME     OPEN_MODE

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

         1 CDB$ROOT READ WRITE

         2 PDB$SEED READ ONLY

         3 SALE     NOMOUNT

-        Startup pluggable database in read write mode.

SQL> alter pluggable database sale open;

Pluggable database altered.

   CON_ID NAME     OPEN_MODE

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

         1 CDB$ROOT READ WRITE

         2 PDB$SEED READ ONLY

         3 SALE     READ WRITE

2.      Change setting to enable Auto start pluggable database.

SQL> select CON_ID, CON_NAME, STATE from dba_pdb_saved_states;

no rows selected

SQL>

SQL> alter pluggable database sale save state;

Pluggable database altered.

SQL> alter pluggable database sale close;

Pluggable database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

3.      Validation.

-        Start container database and check pluggable database status it should be read write.  

SQL> startup;

ORACLE instance started.

Total System Global Area 1593832624 bytes

Fixed Size                  9135280 bytes

Variable Size             956301312 bytes

Database Buffers          620756992 bytes

Redo Buffers                7639040 bytes

Database mounted.

Database opened.

SQL> select CON_ID, NAME, OPEN_MODE from V$CONTAINERS;

    CON_ID NAME     OPEN_MODE

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

         1 CDB$ROOT READ WRITE

         2 PDB$SEED READ ONLY

         3 SALE     READ WRITE

SQL>

WARNING OGG-01742 Command sent to MGR MGR returned with an ERROR response.

Error:
GGSCI (localhost.localdomain) 2> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ...

ERROR: Access denied

2020-03-11 22:41:30  WARNING OGG-01742  Command sent to MGR MGR returned with an ERROR response.


GGSCI (localhost.localdomain) 3> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

In GG error log:

[oracle@localhost 18.1.0.0.0]$ tail -f ggserr.log
2020-03-09T20:54:51.270-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle, oby.oby:  GGSCI command (oracle): CREATE SUBDIRS.
2020-03-09T20:54:52.500-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle, oby.oby:  GGSCI command (oracle): START MGR.
2020-03-09T20:54:52.801-0400  WARNING OGG-01877  Oracle GoldenGate Manager for Oracle, mgr.prm:  Missing explicit accessrule for server collector.
2020-03-09T20:54:52.803-0400  INFO    OGG-00983  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7809).
2020-03-09T20:54:53.585-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle, oby.oby:  GGSCI command (oracle): CREATE DATASTORE.
2020-03-11T22:41:28.754-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): stop mgr.
2020-03-11T22:41:30.535-0400  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [192.168.50.176]:20313 (STOP).
2020-03-11T22:41:30.559-0400  WARNING OGG-00936  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access denied (request from 192.168.50.176, rule #0).
2020-03-11T22:41:30.559-0400  WARNING OGG-01742  Oracle GoldenGate Command Interpreter for Oracle:  Command sent to MGR MGR returned with an ERROR response.
2020-03-11T22:42:06.704-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit param mgr.

solution:
   It happen only if the manager process hung or mgr running from other node..
 
GGSCI (localhost.localdomain) 5> kill mgr
Killed process (29033) for MGR

GGSCI (localhost.localdomain) 6> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (localhost.localdomain) 7> start mgr
Manager started.


GGSCI (localhost.localdomain) 8> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (localhost.localdomain) 9> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?yes

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (localhost.localdomain) 10> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (localhost.localdomain) 11> start mgr
Manager started.


GGSCI (localhost.localdomain) 12> status all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

ORA-65048 and ORA-00959: tablespace '******' does not exist

error Details :

SQL> alter user C##GGATE default tablespace GGUSER;
alter user C##GGATE default tablespace GGUSER
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ******
ORA-00959: tablespace '******' does not exist

Solution:

we are creating common user and also assign on specific tablespace to it. So we need to create same tablespace on all pluggable database and  then try to create user.

SQL> create tablespace GGUSER
  2  datafile '/u01/app/oracle/oradata/orcl/gguser01.dbf' size 1g;

Tablespace created.

SQL> show con_id;

CON_ID
------------------------------
1
SQL> alter user C##GGATE default tablespace GGUSER;
alter user C##GGATE default tablespace GGUSER
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database SALE
ORA-00959: tablespace 'GGUSER' does not exist



SQL> alter session set container=sale;

Session altered.


SQL> create tablespace GGUSER
  2  datafile '/u01/app/oracle/oradata/orcl/sale/gguser01.dbf' size 1g;

Tablespace created.

==> connect to container database

SQL> alter user C##GGATE default tablespace GGUSER;

User altered.

SQL>