Oracle 18c-Online MERGE PARTITION and MERGE SUBPARTITION

 

Description:-

  • In Oracle 18c Database  use ONLINE option with merge partition and sub partition sql statements to enable the online merge partition for heap tables.
  • So it can be provide concurrent DML operation without any interruption, while doing  merge partition operation.
    This option will help us to increase the availability of applications.

Let’s Start the Demo:-

Step1:-Create a sample partitioned table with a three partitions.

CREATE TABLE sample (
id NUMBER,
name VARCHAR2(10),
created_date DATE,
CONSTRAINT pl_1 PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION p_2016 VALUES LESS THAN (TO_DATE(’01-JAN-2017 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’)),
PARTITION p_2017 VALUES LESS THAN (TO_DATE(’01-JAN-2018 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’)),
PARTITION p_2018 VALUES LESS THAN (MAXVALUE)
);

Step2:-To check the table partitions and index information.

CREATE INDEX sample_created_date_index ON sample (created_date) LOCAL;
CREATE INDEX sample_name_idx ON sample (name);

INSERT INTO sample VALUES ( 1, ‘JOHN’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 2, ‘MARK’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 3, ‘TYE’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 4, ‘NIK’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 5, ‘JACK’, TO_DATE(’01-FEB-2017′,’DD-MON-YYYY’));
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, ‘sample’, cascade => TRUE);

Step3:-Display the current table partitions and index information:

COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,partition_name,num_rows FROM user_tab_partitions where table_name=’SAMPLE’ ORDER BY 1, 2;

COLUMN index_name FORMAT A30
COLUMN partitioned FORMAT A11
SELECT index_name,partitioned,status FROM user_indexes where table_name=’SAMPLE’ ORDER BY 1;

SELECT index_name,partition_name,status FROM user_ind_partitions ORDER BY 1, 2;

Step4:-Perform an online operation to merge the P_2016 partition into the P_2017 partition.

ALTER TABLE sample MERGE PARTITIONS p_2016, p_2017 INTO PARTITION p_2017  update indexes ONLINE;

EXEC DBMS_STATS.gather_table_stats(USER, ‘sample’, cascade => TRUE);

Step5:-Finally merge the two partitions using online feature.

COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,partition_name,num_rows FROM user_tab_partitions ORDER BY 1, 2;

Creating an Oracle 18c Database Cloud Service Instance Here

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

Oracle 18c-PDB Snapshot Carousel

Oracle 18c-PDB Snapshot Carousel

Description:-

  • In this article we are going to see the oracle 18 new feature “PDB Snapshot Carousel”
  • PDB Snapshot Carousel is a new feature of Oracle Database 18c
  • We will configure the PDB snapshot carousel for a specified PDB,
  • Create snapshots manually or automatically, and set the maximum number of snapshots.
  • A PDB snapshot carousel is a useful way of maintaining a library of recent PDB copies for point-in-time recovery and cloning.
  • This version allows us to maintain up to a maximum of 8 snapshots per PDB, once this number is reached,these will be overwritten as new instant copies are required. Snapshots can be configured automatically (executed from time to time) as a manual.
  • A PDB snapshot carousel is a useful way of maintaining a library of recent PDB copies for point-in-time recovery and cloning.
  • For example, while the production PDB named pdb1_prod is open and in use, you create a refreshable clone named pdb1_test_master.You then configure pdb1_test_master to create automatic snapshots every day. When you need new PDBs for testing, create a full clone of any snapshot, and then create sparse clones using CREATE PLUGGABLE DATABASE … SNAPSHOT COPY.

Oracle 18c-Creating a Point-in-Time Clone of a PDB Snapshot Here

  • Setting the Maximum Number of Snapshots in a PDB Snapshot Carousel

Prerequisites
The PDB must be open in read/write mode.

SQL> show pdbs
CON_ID      CON_NAME    OPEN MODE RESTRICTED
———- ————- ———- ———-
2                PDB$SEED            READ ONLY         NO
4                PDB1                       READ WRITE        NO

SET LINESIZE 150
COL CON_ID FORMAT 99999
COL PROPERTY_NAME FORMAT a17
COL PDB_NAME FORMAT a9
COL VALUE FORMAT a3
COL DESCRIPTION FORMAT a43
SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,
PROPERTY_VALUE AS value, DESCRIPTION
FROM CDB_PROPERTIES r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID
AND PROPERTY_NAME LIKE ‘MAX_PDB%’
ORDER BY PROPERTY_NAME;

  • SQL statement sets the maximum number of PDB snapshots for the current PDB to 7

SQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=7;
Pluggable database altered.

  • Dropping All Snapshots in a PDB Snapshot Carousel

ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=0;

  • Configuring an Automatic Snapshot Every Day for an Existing PDB

Step:1

SQL> SELECT SNAPSHOT_MODE “S_MODE”, SNAPSHOT_INTERVAL/60 “SNAP_INT_HRS” FROM DBA_PDBS;

S_MODE SNAP_INT_HRS
—— ————
MANUAL

Step:2

SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS;

Pluggable database altered.

Step:3

SQL> SELECT SNAPSHOT_MODE “S_MODE”, SNAPSHOT_INTERVAL/60 “SNAP_INT_HRS” FROM DBA_PDBS;

S_MODE           SNAP_INT_HRS
——               ————
AUTO                            24

  • Creating PDB Snapshots Manually

Creating a Snapshot with a System-Specified Name

ALTER PLUGGABLE DATABASE SNAPSHOT;
SET LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
SELECT CON_ID, CON_NAME, SNAPSHOT_NAME,SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH
FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;

Creating a Snapshot with a User-Specified Name

ALTER PLUGGABLE DATABASE SNAPSHOT test_pdb2_wedload;

  • Dropping a PDB Snapshot

ALTER PLUGGABLE DATABASE DROP SNAPSHOT test_pdb2_wedload;

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-pdb-snapshots.html#GUID-FF6DF540-0C22-451C-80B3-1ACA8C8CB7D2

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba