Oracle 12.2 and Oracle 19.6 - Container Database START/SHUTDOWN :
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 26 20:32:30 2020
Version 19.6.0.0.0

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5368705960 bytes
Fixed Size                  9159592 bytes
Variable Size            2315255808 bytes
Database Buffers         2936012800 bytes
Redo Buffers              108277760 bytes

CON_NAME
------------------------------
CDB$ROOT

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

No CDB or PDBs info at NOMOUNT state. NOMOUNT use for re-create or restore a missing control file for the CDB instance.

SQL> alter database mount;
Database altered.

SQL> select name,open_mode,cdb from v$database;
NAME                           OPEN_MODE            CDB
------------------------------ -------------------- ---
CJTED                          MOUNTED              YES

SQL> SELECT con_id, name, open_mode FROM v$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                   MOUNTED
         3 PJTED1                         MOUNTED
         4 PJTED2                         MOUNTED

CDB’s control files opened for the instance, but both the CDB$ROOT and all PDBs are changed to the MOUNT state.

SQL> alter database open; or alter database CJTED open;
Database altered.

SQL> select name,open_mode,con_id,dbid from v$containers;
NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                  READ WRITE          1  105692156
PDB$SEED                   READ ONLY           2  657829016
PJTED1                         READ WRITE          3 1002662416         >>> Oracle 12.2   
PJTED2                         READ WRITE          4  531562146

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;   >>> Oracle 12.2
no rows selected

CDB$ROOT is OPEN, it’s available for READ and WRITE operations. 
All PDBs are still READ WRITE with the seed database PDB$SEED mounted as READ ONLY:
dba_pdb_saved_states is valid for Oracle 12.1

========================= shutdown ================================

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
Pluggable database altered.

SQL> select name,open_mode,con_id,dbid from v$containers;
NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                      READ WRITE          1  105692156      >>>> ROOT
PDB$SEED                       READ ONLY           2  657829016       >>>> SEED
PJTED1                            MOUNTED             3 1002662416
PJTED2                            MOUNTED             4  531562146
CSTG1                             MOUNTED             5 2604502831

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

It will shutdown ALL PDBs, PDB$SEED & CDB$ROOT inside CDB.

Oracle 19c:

ORA-65040: operation not allowed from within a pluggable database, indicates an operation was attempted that can only be performed in the root container.

Issue:

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc';

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc'

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

Solution:

SQL> alter session set container=cdb$root;

Session altered.

SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

DIRECTORY_NAME          DIRECTORY_PATH

DATA_PUMP_DIR /u01/app/oracle/product/19.0.0.0/dbhome_2/rdbms/log/

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/zfs/dev/expdp/abc';

Directory created.

SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES where DIRECTORY_NAME = 'DATA_PUMP_DIR';

DIRECTORY_NAME  DIRECTORY_PATH

DATA_PUMP_DIR   /zfs/backup01/dev/expdp/abc

Refer:

ORA-65040: operation not allowed from within a pluggable database" (Doc ID 2122655.1)

Sequence - Oracle 11g - 12c - 18c:

1. Oracle 11g - Sequence and Trigger for auto increment columns :

CREATE TABLE T1 ( ID NUMBER(10) NOT NULL,

DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE T1 ADD ( CONSTRAINT T1_pk PRIMARY KEY (ID));

CREATE SEQUENCE  SEQ_11g START WITH 1;

CREATE OR REPLACE TRIGGER T1_trg

BEFORE INSERT ON T1

FOR EACH ROW

BEGIN

SELECT SEQ_11g.NEXTVAL

INTO :new.id

FROM dual;

END;

/

2. IDENTITY column on Oracle 12c:

CREATE TABLE  T1 ( c1 NUMBER GENERATED by default on null as IDENTITY,

c2 VARCHAR2(10));

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

CREATE TABLE  T1 ( 

c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),

c2 VARCHAR2(10));

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE SEQ_12c START WITH 1;

CREATE TABLE T1 (

ID NUMBER(10) DEFAULT SEQ_12c.nextval NOT NULL,

DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE T1 ADD ( CONSTRAINT T1_pk PRIMARY KEY (ID));

3. Oracle 18c - scalable sequence

Now in Oracle 18c, in those cases of data ingestion workloads with high level of concurrency, the new scalable sequence by generating unordered primary or unique key values helps in significantly reducing the sequence and index block contention caused by the right-handed indexes and thus provides better throughput, data load scalability and performance as compared to the pre -Oracle 19c solution of having to configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE  or ALTER SEQUENCE statement.

The scalable sequence is made up of a sequence offset number which by default contains 6 digits. The first 3 digits is derived from the instance number with 100 added and the next 3 digits is derived from the SID of that session.

create sequence seq_test_11g

start with 1 increment by 1

maxvalue 100; 

select seq_test_11g.nextval from dual;  

NEXTVAL  :    1 

create sequence seq_test_18c

start with 1 increment by 1

maxvalue 100

scale extend; >>>> (SID nnn, Instance_Number 001)

SQL> select seq_test_18c.nextval from dual;    

NEXTVAL :  101608001

SQL> select instance_number from v$instance; 

INSTANCE_NUMBER :      1 

SQL> select sid from v$mystat where rownum = 1;  

SID :    608

select sequence_name, scale_flag, extend_flag from user_sequences  

where sequence_name='SEQ_TEST_18c';

SEQ_TEST_18c                                                  Y Y

SEQ_TEST_11g                                                  N N

Golden Gate Initial Load :

Prerequisites for the Golden Gate Initial Load

Before starting the Golden Gate initial load, ensure the following

1) You must disable any foreign-key constraints on the target tables to prevent errors/speed up performance.

2) Disable any triggers on the target tables.

3) You may want to drop indexes on your target tables to speed up performance. 

Procedure 1. Using SOURCEISTABLE and RMTTASK/SPECIALRUN

Source Database:

1. Note the SCN from source database:

SQL> select dbms_flashback.get_system_change_number() from dual;

SQL> select current_scn from v$database ;

 

2.Create EXTRACT/PUMP/REPLICATE :./ggsci

GGSCI> dblogin userid ogg, password ogg

 GGSCI> ADD EXTRACT EJAY1, INTEGRATED TRANLOG, BEGIN NOW

GGSCI> register extract EJAY1 database

GGSCI> add exttrail ./dirdat/sa, extract EJAY1

GGSCI> add trandata JAY.TBL1

 

GGSCI> edit param EJAY1

EXTRACT EJAY1

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/sa

TABLE JAY.TBL1;

 

3. Create PUMP:

GGSCI> Add extract PJAY1, EXTTRAILSOURCE ./dirdat/sa

GGSCI> Add rmttrail ./dirdat/rf, extract PJAY1

 

GGSCI> edit param Pschema1D1

EXTRACT PJAY1

USERID ogg, PASSWORD ogg

RMTHOST ggdev, MGRPORT 7809

RMTTRAIL ./dirdat/ra

TABLE JAY.TBL1;

 

4. Replicate Database:

GGSCI> dblogin userid ogg, password ogg

GGSCI> add replicat RJAY1, integrated exttrail ./dirdat/ra

 

GGSCI> edit param RJAY1

REPLICAT RJAY1

USERID ogg, PASSWORD ogg

ASSUMETARGETDEFS

MAP JAY.TBL1 TARGET JAY.TBL1;

 

5. Configure initial load extract:

GGSCI> ADD EXTRACT ELOAD, SOURCEISTABLE    >>>> for initial load extract

 

GGSCI> EDIT PARAM ELOAD

EXTRACT ELOAD

userid ogg, password ogg

RMTHOST dev.ip.com, mgrport 7840

RMTTASK REPLICAT, GROUP RLOAD             >>>> To auto start initial load replicate

TABLE JAY.TBL1;

 

6. initial Replicate:

GGSCI> ADD REPLICAT RLOAD, SPECIALRUN     >>>> for initial load Replicate

 

GGSCI> EDIT PARAM RLOAD

REPLICAT RLOAD

userid ogg, password ogg

ASSUMETARGETDEFS

MAP JAY.TBL1, TARGET JAY.TBL1;

 

7. Start Change Sync EXTRACT and PUMP but NOT Replicate:

START EJAY1

START PJAY1

[ HOLD Replicate for change sync process]

 

8. Now start the initial load extract. Remember, this will automatically start the initial load replicate on target :

mgr.prm at Target add (ACCESSRULE, PROG *, IPADDR *, ALLOW) (otherwise - ERROR OGG-01201 Error reported by MGR : Access denied.)

GGSCI> refresh mgr

START ELOAD

INFO ELOAD

 

At Target:

sqlplus / as sysdba

select * from JAY.TBL1;

 9) Start sync Replicate using SCN

GGSCI> start RJAY1, aftercsn <Initial_scn_from_step#1>

x

Procedure 2. Using expdp with SCN number :

1) Start the Extract

While your source database is running, you first need to start the extract so that the transactions are captured by Oracle Golden Gate.

GGSCI>start extract exext, begin now

 2) Check the Source DB SCN

Check the database SCN after the extract started, so that you make sure all the new transactions after this SCN are captured by Oracle Golden Gate.

SQL> select dbms_flashback.get_system_change_number() from dual; 

SQL> select current_scn from v$database ; 

 3) Export Data from the Source Database

Export the database with with the flashback_scn option.

expdp directory=pump_dir flashback_scn=xxx dumpfile=src.dmp logfile=sourcedb.log (schema=xxx)

 4) Import Data to the Target Database:

impdp directory=pump_dir dumpfile=src.dmp logfile=sourcedb_imp.log  (schemas=xxx)

 5) Start the Replicate:

Starting the replicat with aftercsn.

GGSCI> start replicat exrep, AFTERCSN XXX

 

 How to lock and unlock Linux  user ?

1. Example:

To lock the password:

# passwd -l oracle

Locking password for user oracle.

passwd: Success

Check :more /etc/shadow

Before Lock:

oracle:$1$3qMtw2cK$LJwR4.e9dY.axeCfEi9zY0:18583:1::7:::

After lock:

oracle:!!$1$3qMtw2cK$LJwR4.e9dY.axeCfEi9zY0:18583:1::7:::

Check two exclamation mark (!!) before the encrypted password which means that the password has been locked

To unlock the password:

# passwd -u oracle

2. Example:

# passwd -l oracle   >>> Lock the Oracle user

Locking password for user oracle.

passwd: Success

# passwd -S oracle   >>>> To check lock/unlock Linux user

oracle LK 2020-11-17 1 -1 7 -1 (Password locked.)

# passwd -u oracle    >>>> Unlock Linux user

Unlocking password for user oracle.

passwd: Success

# passwd -S oracle

oracle PS 2020-11-17 1 -1 7 -1 (Password set, MD5 crypt.)

 RMAN: How to modify RMAN CHANNEL ?

rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name QEMPR_NJ2 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/location/rman/dbname/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/location/rman/dbname/snapcf_dbname.f';

RMAN> SHOW CHANNEL;

RMAN configuration parameters for database with db_unique_name dbname are:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/location/rman/dbname/full_dbname_%T_%U';

old RMAN configuration parameters are successfully deleted

RMAN> SHOW CHANNEL;

RMAN configuration parameters for database with db_unique_name dbname are:

RMAN configuration has no stored or default parameters

 Reinstate standby database after activation using flashback.

Physical standby database PRI_DB has been activated using steps listed in the "Disaster recovery procedure." 

If a decision is made to convert the database back to physical standby the following steps apply.

Stop the database and mount one instance.

srvctl stop database -d PRI_DB

SQL> startup mount;

If a guaranteed restore point has been created prior to standby activation.

SQL>FLASHBACK DATABASE TO restore point grp_before_activate;

If no guaranteed restore point has been created but SCN prior to activation had been recorded

SQL>FLASHBACK DATABASE TO scn  <<scn prior to activate>>;

If none of the above applies, time of activation has to be determined from the alert log.

SQL> FLASHBACK DATABASE TO time  <<timestamp before standby activation>>;

After a successful flashback, convert to physical standby.

SQL>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL>shutdown immediate

Change cluster configuration and start the database.

srvctl modify database -d PRI_DB -r physical_standby -s "read only"

srvctl start database -d PRI_DB

srvctl start service -d dbname

Start MRP.

SQL>alter database recover managed standby database using current logfile disconnect;

On the primary enable the corresponding destination.

SQL>alter system set log_archive_dest_state_2='enable' scope=both sid='*';

Check the alert logs on both primary and standby to verify media recovery.

PRI_DB: Check log_archive_dest_state_2 and 3 for DEFER or ENABLE. Keep this parameter ENABLE for RMAN script.

show parameter log_archive_dest_state_2  (if DEFER then make it enable)

show parameter log_archive_dest_state_3  (if DEFER then make it enable)

alter system set log_archive_dest_state_2='enable' scope=both sid='*';

alter system set log_archive_dest_state_3='enable' scope=both sid='*';

PRI_DB - How to drop RESTORE POINT ?

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

srvctl stop database -d dbname_PRI_DB

Startup mount;

Drop restore point GRP_BEFORE_ACTIVATE;

shutdown immediate

srvctl start database -d dbname_PRI_DB

srvctl start service -d dbname_PRI_DB

alter database recover managed standby database using current logfile disconnect;

 Oracle 19c - expdp - UDE-31623: operation generated ORACLE error 31623

expdp system/oracle@pdb1  schemas=JAY directory=DATA_PUMP_DIR dumpfile=JAY.dmp

Export: Release 19.0.0.0.0 - Production on Tue Sep 29 15:07:33 2020

Version 19.6.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

UDE-31623: operation generated ORACLE error 31623

ORA-31623: a job is not attached to this session via the specified handle

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4747

ORA-06512: at "SYS.KUPV$FT_INT", line 2144

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT_INT", line 2081

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496

ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127

ORA-06512: at line 1

Solution:

alter system set streams_pool_size=2G scope=both sid='*';

If the STREAMS_POOL_SIZE is too small, then a Data Pump job will fail. This can also happen when using Automatic Shared Memory Management (ASMM), or Automatic Memory Management (AMM) and there is not sufficient memory to increase the STREAMS_POOL_SIZE. Manual settings for the STREAMS_POOL_SIZE of 64M, 128M or even to 256M have proven to be successful. Also increase sga_target (for ASMM) or memory_target (for AMM) to have more free memory available during automatic tuning of the SGA components. (Doc ID 1907256.1)

 Integrated Golden Gate 19c: Container database 

How to create Integrated extract/replicate  in Oracle 19c  container database ?

1) EXTRACT:

Note: Using Oracle 19c container database. Create TNS entry for cdb and pdb databases for Golden Gate.

EXTRACT EDEV1

USERID C##GGOLD_EXT@GG_CDB1, PASSWORD *****

DISCARDFILE /acfs01/goldengate/gg19c/discard/EDEV1.dsc, APPEND, megabytes 300

EXTTRAIL ./dirdat/la

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 1024)

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

SOURCECATALOG PDB1

OBEY ./dirprm/dev_tablelist_PDB1.oby  >>> list of table name

or 

TABLE jay.table_name;

2) REPLICATE:

REPLICAT RDEV1

USERID C##GGOLD_REP@GG_PDB1, PASSWORD *****

DISCARDFILE  /acfs01/goldengate/gg19c/discard/RDEV1.dsc, APPEND, megabytes 300

DBOPTIONS INTEGRATEDPARAMS(parallelism 2)

ASSUMETARGETDEFS

SOURCECATALOG PDEV1

OBEY ./dirprm/dev_tablelist_PDB1.oby

or

MAP rpt.tablename, target rptng.tablename;

or

MAP rpt.tablename, target rptng.tablename, colmap (usedefaults,timestamp = @datenow(),flag="N"); >>> Extra column in Replicate database

3. How to Register and start Extract and Replicate ?

Extract:

dblogin USERID C##GGOLD_EXT@EDEV1, PASSWORD ******

REGISTER EDEV1 DATABASE CONTAINER (PDB1)

ADD EXTRACT DEV1, INTEGRATED TRANLOG, BEGIN NOW

ADD RMTTRAIL ./dirdat/la, EXTRACT DEV1,megabytes 300 

Replicate:

USERID C##GGOLD_REP@GG_PDB1, PASSWORD *****

ADD REPLICAT RDC3_DTC INTEGRATED EXTTRAIL ./dirdat/la

--START GG PROCESS

STRAT EDEV1

START RDEV1

or

ALTER EDEV1  BEGIN 2020-09-03 12:00

ALTER RDEV1  BEGIN 2020-09-03 12:00

4.How to create manager for Oracle 19c Golden Gate ?

PORT 7900

DYNAMICPORTLIST 7901-7999

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *

AUTOSTART REPLICAT *

AUTORESTART REPLICAT *

PURGEOLDEXTRACTS /acfs01/goldengate/gg19c/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 15

--START mgr

--STOP mgr

5. Advance parameter :

Extract:

DDL INCLUDE MAPPED SOURCECATALOG PORNDV1, OBJTYPE 'TABLE', EXCLUDE OPTYPE DROP, EXCLUDE INSTR 'FOREIGN KEY'

DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 5

DDLOPTIONS REPORT

Replicate:

--DDL INCLUDE MAPPED

--DDLOPTIONS REPORT

--DBOPTIONS SUPPRESSTRIGGERS

--DDLERROR DEFAULT IGNORE

--HANDLECOLLISIONS

--MAPEXCLUDE PDB.Schema_Name.TableName;

Refer Oracle docs: https://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/12c/OGG12c_Integrated_Replicat/index.html