How to enable ddl logging in Oracle Container database 19c ?

Solution:

At CDB:

SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

-- ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=BOTH; -- Turn off

Any PDB:

create table tbl_a (num number);

alter table tbl_a add num2 number;

Check ddl logging with new log file:

$DUMP_LOCATION/log/ddl_dbname.log  and

$DUMP_LOCATION/log/ddl/log.xml

ddl_dbname.log or log.xml:

2021-01-28T13:46:41.640037-05:00

diag_adl:create table tbl_a (num number)

2021-01-28T13:50:20.725803-05:00

diag_adl:alter table tbl_a add num2 number

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