GeneralCommands

Chapter 6

rman TARGET /

• DATABASE keyword operates on all PDBs and root:

• PDB operates on individual PDBs:

• Backup, restore, recover the root using CDB$ROOT keyword:

• Qualify tablespace of PDB with PDB name:

RMAN> BACKUP PLUGGABLE DATABASE hr_pdb, sales_pdb;

RMAN> RECOVER PLUGGABLE DATABASE hr_pdb;

RMAN> BACKUP TABLESPACE sales_pdb:tbs2;

RMAN> RESTORE TABLESPACE system;

RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT";

RMAN> BACKUP DATABASE;

RMAN> RECOVER DATABASE;

RMAN> CONNECT TARGET

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

RMAN> CONNECT TARGET

RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb;

RMAN> BACKUP PLUGGABLE DATABASE hr_pdb PLUS ARCHIVELOG;

RMAN> CONNECT TARGET

RMAN> BACKUP PLUGGABLE DATABASE sales_pdb;

RMAN> BACKUP PLUGGABLE DATABASE sales_pdb, hr_pdb;

SQL> CONNECT / AS SYSDBA

SQL> ALTER DATABASE BEGIN BACKUP;

SQL> !cp datafiles /backup_dir

SQL> ALTER DATABASE END BACKUP;

SQL> ALTER PLUGGABLE DATABASE sales_pdb BEGIN BACKUP;

SQL> !cp pdb_datafiles /backup_dir

SQL> ALTER PLUGGABLE DATABASE sales_pdb END BACKUP;

RMAN> CONNECT TARGET

RMAN> REPORT SCHEMA;

RMAN> BACKUP TABLESPACE sales_pdb:tbs2;

RMAN> BACKUP TABLESPACE hr_pdb:system, sales_pdb:sysaux;

RMAN> BACKUP TABLESPACE sysaux, hr_pdb:sysaux;

SQL> STARTUP;

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

RMAN> ALTER DATABASE OPEN RESETLOGS;

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Or

SQL> ALTER DATABASE OPEN RESETLOGS;

RMAN> CONNECT TARGET

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

RMAN> STARTUP MOUNT;

RMAN> RESTORE TABLESPACE undo1;

RMAN> RECOVER TABLESPACE undo1;

RMAN> ALTER DATABASE OPEN;

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

RMAN> ALTER TABLESPACE sysaux OFFLINE IMMEDIATE;

RMAN> RESTORE TABLESPACE sysaux;

RMAN> RECOVER TABLESPACE sysaux;

RMAN> ALTER TABLESPACE sysaux ONLINE;

RMAN> STARTUP MOUNT; — useless on closed PDB —

RMAN> RESTORE PLUGGABLE DATABASE sales_pdb;

RMAN> RECOVER PLUGGABLE DATABASE sales_pdb;

RMAN> ALTER DATABASE OPEN; — useless on closed PDB —

RMAN> ALTER PLUGGABLE DATABASE sales_pdb OPEN;

RMAN> RESTORE TABLESPACE sales_pdb:system;

RMAN> RECOVER TABLESPACE sales_pdb:system;

SQL> CONNECT system@sales_pdb

SQL> ALTER TABLESPACE tbs2 OFFLINE IMMEDIATE;

RMAN> CONNECT TARGET

RMAN> RESTORE TABLESPACE sales_pdb:tbs2;

RMAN> RECOVER TABLESPACE sales_pdb:tbs2;

SQL> ALTER TABLESPACE tbs2 ONLINE;

• PDB PITR -

RMAN> ALTER PLUGGABLE DATABASE PDB1 CLOSE;

RMAN> RUN {

SET UNTIL SCN = 1851648 ;

RESTORE pluggable DATABASE pdb1;

RECOVER pluggable DATABASE pdb1

AUXILIARY DESTINATION='/u01/app/oracle/oradata';

ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

}

• PDB Tablespace PITR

RMAN> RECOVER TABLESPACE PDB1:TEST_TBS

UNTIL SCN 832972

AUXILIARY DESTINATION '/tmp/CDB1/reco';

RMAN> ALTER TABLESPACE PDB1:TEST_TBS ONLINE;

Flashback CDB

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

SQL> STARTUP MOUNT

SQL> FLASHBACK DATABASE TO SCN 53943;

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

RMAN> SHUTDOWN IMMEDIATE

RMAN> STARTUP MOUNT

RMAN> FLASHBACK DATABASE TO SCN 10;

RMAN> ALTER DATABASE OPEN RESETLOGS;

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

================================================================================================================================================================

Chapter 7

ALTER SYSTEM SET heat_map = ON;

SELECT object_name, subobject_name, track_time, segment_write WRI, full_scan FTS, lookup_scan LKP FROM DBA_HEAT_MAP_SEG_HISTOGRAM;

The columns of DBA_HEAT_MAP_SEG_HISTOGRAM view are the following:

• TRACK_TIME column: Displays the system time when the segment access was tracked

• SEGMENT_WRITE column: Indicates whether the segment has write access

• FULL_SCAN: Indicates whether segment has full scan

• LOOKUP_SCAN: Indicates whether the segment has lookup scan

SELECT object_name, segment_write_time WRITE_T, segment_read_time READ_T, full_scan FTS_T, lookup_scan LKP_T FROM DBA_HEAT_MAP_SEGMENT;

SEGMENT_WRITE_TIME column: Indicates the latest time stamp on which the segment has write access.

• SEGMENT_READ_TIME column: Indicates the latest time stamp on which the segmenthas read access.

• FULL_SCAN column: Indicates the latest time stamp on which the segment has full scan.

• LOOKUP_SCAN column: Indicates the latest time stamp on which the segment has indexscan.

SELECT segment_name, tablespace_name, block_id, writetime FROM table(dbms_heat_map.block_heat_map ('SCOTT','EMPLOYEE',NULL,8,'ASC'));

SELECT segment_name, block_id, blocks, max_writetime FROM table(dbms_heat_map.extent_heat_map ('SCOTT','EMPLOYEE'));

ALTER TABLESPACE tbs1 DEFAULT ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF LOW ACCESS;

ALTER TABLE tab1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED GROUP AFTER 90 DAYS OF NO MODIFICATION;

ALTER TABLE tab2 MODIFY PARTITION p1 ILM ADD POLICY COLUMN STORE COMPRESS FOR ARCHIVE HIGH GROUP AFTER 6 MONTHS OF NO ACCESS;

ALTER TABLE tab4 ILM ADD POLICY COLUMN STORE COMPRESS FOR QUERY HIGH SEGMENT AFTER 90 DAYS OF NO MODIFICATION;

ALTER TABLE tab5 ILM ADD POLICY COLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 6 MONTHS OF LOW ACCESS;

ALTER TABLE tab6 ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 6 MONTHS OF NO ACCESS;

ALTER TABLE tab1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION;

ALTER TABLE t1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 1 DAY OF NO MODIFICATION;

ALTER TABLE t1 ILM ADD POLICY COLUMN STORE COMPRESS FOR QUERY HIGH SEGMENT AFTER 1 MONTH OF NO MODIFICATION;

ALTER TABLE t1 ILM ADD POLICY TIER TO history ON t1_check_after_one_year;

DROP TABLE t1 PURGE;

ALTER TABLE t2 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 2 DAYS OF NO MODIFICATION;

ALTER TABLE t2 ILM ADD POLICY COLUMN STORE COMPRESS FOR QUERY HIGH SEGMENT AFTER 1 YEAR OF NO MODIFICATION;

select POLICY_NAME "POLICY", OBJECT_NAME, SUBOBJECT_NAME "SUBOBJECT", OBJECT_TYPE, INHERITED_FROM FROM DBA_ILMOBJECTS;

SELECT * FROM DBA_ILMPOLICIES;

SELECT policy_name, action_type, compression_level, tier_tablespace TBS FROM DBA_ILMDATAMOVEMENTPOLICIES;

SELECT policy_name POL, action_type, scope, condition_type, condition_days FROM DBA_ILMDATAMOVEMENTPOLICIES;

exec DBMS_ILM_ADMIN.DISABLE_ILM;

exec DBMS_ILM_ADMIN.ENABLE_ILM;

select * from DBA_ILMPARAMETERS where name='ENABLED';

ALTER SESSION SET HEAT_MAP = OFF;

ALTER SYSTEM SET HEAT_MAP = OFF;

exec DBMS_ILM_ADMIN.CLEAR_HEAT_MAP_ALL;

====================================================================================================================================================

Chapter 8 -

In-Database Archiving: In-Database Archiving solves problems regarding non-active data. It gives users the ability to keep both active operational data and non-active data in the same

database tables. While achieving operational efficiency of archiving, applications can easily be limited to only access data that is in an operationally active state although archived data is kept

in the same table.

Temporal Validity: Temporal Validity is a new temporal database capability of Oracle Database 12c. Temporal Validity provides support for valid-time (user-defined time) semantics.

This new capability allows you to create a valid time dimension for each row of a table, and is another way of indicating operational relevance of rows.

The Total Recall database option, also known as Flashback Data Archive (FDA) is now called Temporal History and is no longer an option. In Oracle Database 12c, it is enhanced with new capabilities.