ORACLE - EXADATA - GoldenGate - MySQL - MSSQL

Saturday, December 26, 2020

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.

Tuesday, December 22, 2020

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)

Tuesday, December 1, 2020

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