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:
RMAN> RUN {
STARTUP MOUNT;
RESTORE DATABASE
ROOT;
RECOVER DATABASE
ROOT;
ALTER DATABASE
OPEN;
}
