In Database Archiving

• In Database Archiving is a new feature of Oracle 12c meant to solve management of historical data inside the database.
• Like its name says, this functionnality leaves data into the database, so it remains accessible if you need it.
• With In Database Archiving, historical data remains in the database but is invisible to the application.
• Hybrid Columnar Compression (HCC) is used to compress the historical data into the database.

Temporal Validity

• Temporal Validity works  in concomitence with In Database Archiving
• It adds the “valid time” dimension to the data by adding columns to table indicating if the row is still valid or can be archived.
• This helps performance by reducing the quantity of data accessible to the application.
• The Valid-Time of data is defined at table creation :

SQL> CREATE TABLE ORDER_HISTORY (ORDER_ID NUMBER, ORDER_DATE DATE, COMMENTS VARCHARC2(100), USER_TIME_START DATE, USER_TIME_END DATE, PERIOD FOR user_time (USER_TIME_START , USER_TIME_END));

•  When you insert a row in the table you must insert the validity period too :

INSERT INTO ORDER_HISTORY (ORDER_ID , ORDER_DATE, COMMENTS, USER_TIME_START, USER_TIME_END) VALUES (1,SYSDATE,'ORDER 1',SYSDATE, SYSDATE+30);

Continue reading OCP 12C – In Database Archiving and Temporal Validity →

Information Lifecycle Management

Before reading this flashcard I recommend you to read my article on ILM.

Automatic Data Optimization (ADO)

• Policy based data management
• Let you define policies at :
  • Tablespace level
  • Segment level
  • Row level
• Policies can either compress data or move data to a different tablespace
• Policies let you define when, what and where to move data
• Heat Map collect statistics used to trigger ADO actions
• Heat Map collects statistics on data utilization like:
  • When does this row was last updated
  • When does this table was last updated
  • …
• Policies let you define what to do with :
  • Active data
  • Frequently accessed data
  • Infrequently accessed data
  • Dormant data
• Policies let you also define what to do with data when you encounter space pressure in a tablespace.

Continue reading OCP 12C – Information Lifecycle Management and Storage Enhancements →

Backup a CDB/PDB

• To make a database backup you need the SYSBACKUP or SYSDBA privilege.
• You can backup the CDB and all the PDBs independantly, all together, or by specifying a list.
• You can backup a PDB by connecting directly to it and use:
  • RMAN> BACKUP DATABASE:
• You can backup a PDB by connecting to the CDB and use:
  • RMAN> BACKUP PLUGGABLE DATABASE MY_PDB;
• To backup only the root (CDB$ROOT) connect to the CDB and use:
  • RMAN> BACKUP DATABASE ROOT;
• You can backup everything by connecting to the CDB and use:
  • RMAN> BACKUP DATABASE;
• You can backup only some PDBs by using:
  • RMAN > BACKUP PLUGGABLE DATABASE PDB1, PDB2, PDB3;

Backup tablespaces and datafiles in CDBs/PDBs

• You can backup tablespaces from several PDBs at the same time when connected to the root using:
  • RMAN> BACKUP TABLESPACE PDB1:USERS, PDB2:TOOLS, PDB3:SYSTEM;
• You can also backup tablespaces using the standard command when you connect directly to the PDB:
  • RMAN> BACKUP TABLESPACE SYSTEM;
• To backup datafiles for a PDB you can :
  • Connect to the root and use : BACKUP DATAFILE 12,13,14;
  • Connect to the PDB and use : BACKUP DATAFILE 12,13,14;

Continue reading OCP 12C – Backup, Recovery and Flashback for a CDB/PDB →