OracleWorld-OracleTechies

Online rename and relocation of an active data file

Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action. In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.

C:\APP\ORA12C\ORADATA\ORCL\USERS01.DBF

Alter database move datafile ‘C:\APP\ORA12C\ORADATA\ORCL\USERS01.DBF’

To ‘C:\APP\ORA12C\ORADATA\Testdir\USERS_01.DBF’ ;

Alter database move datafile 'C:\APP\ORA12C\ORADATA\Testdir\USERS_01.DBF'

To 'C:\APP\ORA12C\ORADATA\newdir\USERS_01.DBF' reuse;

(The old datafile gets cleaned up automatically)

(Old datafile is retained while creating new datafile)

You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.

Invisible columns

In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions.In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:

Invisible Column

Multiple indexes on the same column

Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.

DDL logging

There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature. The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.

To enable DDL logging

How to execute SQL statement in RMAN

In 12c, you can now execute any SQL and PL/SQL commands in RMAN without the need of a SQL prefix: you can execute any SQL and PLS/SQL commands directly from RMAN. How you can execute SQL statements in RMAN

Restricting PGA size

Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size toPGA_AGGREGATE_TARGET initialization parameter, Oracle could increase/reduce the size of the PGA dynamically based on the workload and requirements. In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage

Important notes:

When the current PGA limits exceeds, Oracle will automatically terminates/abort the session/process that holds the most untenable PGA memory

Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:

In the same way, you can add multiple new partitions to a list and system partitioned table, provided that theMAXVALUE partition doesn’t exist.

How to drop and truncate multiple partitions/sub-partitions

As part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table. Pre 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table. With Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS command.

The following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;

SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;

Gathering statistics concurrently on multiple tables

In previous Oracle database editions, whenever you execute a DBMS_STATS procedure to gather table, index, schema or database level statistics, Oracle used to collect stats one table at a time. If the table is big enough, then increasing the parallelism was recommended. With 12c R1, you can now collect stats on multiple tables, partitions and sub partitions concurrently. Before you start using it, you must set the following at the database level to enable the feature:

Transport view as table

This is another improvement in the data pumps. With the new VIEWS_AS_TABLES option, you can unload the view data into a table. The following example describes how to unload views data into a table during export.

expdp directory=expdp_dir dumpfile=emp_part.dmp logfile=emp_part.log views_as_tables=system.emp_part1

RMAN

Start container AND pluggable databases .

But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

From CDB$ROOT we can manage any PDB.

we can move down to a PDB container to stop and start them individually.

To open ALL PDBS

SQL> alter pluggable database all open;

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

PDB3 READ WRITE

Startup of PDBs can be automated using a startup trigger.

SQL> create or replace trigger Sys.After_Startup
after startup on database
begin
execute immediate 'alter pluggable database all open';
end;
/

User creations

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root.

RMAN Features

Complete recovery of CDB, PDB and root

You should have SYSBACKUP or SYSDBA privilege to restore any of the databases.

Restoring Container Database (CDB) is similar to non-container database.

You can restore the whole CDB using below script:

RMAN> RUN {

STARTUP MOUNT;

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN;

}

Note that restoring CDB database will restore all the pluggable databases.

You can restore only ROOT Database using below script: