Showing posts with label 12c. Show all posts
Dataguard Monitoring Scripts
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);
================================================================================
SQL> select status,instance_name,database_role fromv$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
——————————————–
OPEN prim PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
40
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.
SQL> select status,instance_name,database_role fromv$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
——————————————
MOUNTED stnd PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
————–
40
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——————————-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————————————————
OPEN stnd PHYSICAL STANDBY READ ONLY
Step 6:
Now start the MRP on the physical standby database.
SQL> alter database recover managed standby database disconnectfrom session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——————————-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9rows selected.
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Managing dataguard – monitoring scripts
LOGS TIME—————- ——————
Last applied : 16-JUL-09:09:24:16
Last received : 16-JUL-09:09:28:36
select ‘Last applied : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
NAME VALUE UNIT———————- ———————- ———————————–
apply finish time +00 00:02:07.2 day(2) to second(1) interval
apply lag +00 00:01:59 day(2) to second(0) interval
estimated startup time 16 second
standby has been open N
transport lag +00 00:00:00 day(2) to second(0) interval
Time Computed: 16-JUL-2009 09:33:16
selectNAME Name,
VALUE Value,
UNIT Unit
from v$dataguard_stats
union
select null,null,’ ‘ from dual
union
select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED)
from v$dataguard_stats;
Redo onsite
——————–
16-JUL-2009 09:42:44
select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);
================================================================================
SQL> select status,instance_name,database_role fromv$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
——————————————–
OPEN prim PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
40
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.
SQL> select status,instance_name,database_role fromv$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
——————————————
MOUNTED stnd PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
————–
40
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——————————-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————————————————
OPEN stnd PHYSICAL STANDBY READ ONLY
Step 6:
Now start the MRP on the physical standby database.
SQL> alter database recover managed standby database disconnectfrom session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——————————-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9rows selected.
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Managing dataguard – monitoring scripts
LOGS TIME—————- ——————
Last applied : 16-JUL-09:09:24:16
Last received : 16-JUL-09:09:28:36
select ‘Last applied : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
NAME VALUE UNIT———————- ———————- ———————————–
apply finish time +00 00:02:07.2 day(2) to second(1) interval
apply lag +00 00:01:59 day(2) to second(0) interval
estimated startup time 16 second
standby has been open N
transport lag +00 00:00:00 day(2) to second(0) interval
Time Computed: 16-JUL-2009 09:33:16
selectNAME Name,
VALUE Value,
UNIT Unit
from v$dataguard_stats
union
select null,null,’ ‘ from dual
union
select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED)
from v$dataguard_stats;
Redo onsite
——————–
16-JUL-2009 09:42:44
select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log
INVALID Oracle Database Packages and Type in dba_registry
The timestamp query object.
SQL> select do.obj# d_obj,do.name d_name, do.type# d_type,
2 po.obj# p_obj,po.name p_name,
3 to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
4 to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
5 decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
6 from sys.obj$ do, sys.dependency$ d, sys.obj$ po
7 where P_OBJ#=po.obj#(+)
8 and D_OBJ#=do.obj#
9 and do.status=1 /*dependent is valid*/
10 and po.status=1 /*parent is valid*/
11 and po.stime!=p_timestamp /*parent timestamp not match*/
12 order by 2,1;
D_OBJ D_NAME D_TYPE P_OBJ P_NAME
---------- -------------------- ---------- ---------- --------------------
P_Timestamp STIME X
Solution :
————————-
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql
This will resolve your issue.
or
SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;
COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
8 rows selected
repair and validate “Oracle Database Packages and Types”.
1. log as SYSDBA
$ sqlplus ” / as sysdba”
2. shutdown running database and startup database in upgrade mode
SQL> shutdown immediate;
SQL>startup upgrade;
For startup migrate;
3. run this two scripts
SQL> spool /tmp/cat_repair_db.txt
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
catalog.sql will recreate Oracle database Catalog Views component and catproc.sql will recreate Oracle database Packages and Types component
4. Recompile all invalid objects
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
5. Shutdown database and startup in open mode
SQL> shutdown immediate;
SQL> startup;
SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;
COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
8 rows selected
SQL> select do.obj# d_obj,do.name d_name, do.type# d_type,
2 po.obj# p_obj,po.name p_name,
3 to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
4 to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
5 decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
6 from sys.obj$ do, sys.dependency$ d, sys.obj$ po
7 where P_OBJ#=po.obj#(+)
8 and D_OBJ#=do.obj#
9 and do.status=1 /*dependent is valid*/
10 and po.status=1 /*parent is valid*/
11 and po.stime!=p_timestamp /*parent timestamp not match*/
12 order by 2,1;
D_OBJ D_NAME D_TYPE P_OBJ P_NAME
---------- -------------------- ---------- ---------- --------------------
P_Timestamp STIME X
Solution :
————————-
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql
This will resolve your issue.
or
SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;
COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
8 rows selected
repair and validate “Oracle Database Packages and Types”.
1. log as SYSDBA
$ sqlplus ” / as sysdba”
2. shutdown running database and startup database in upgrade mode
SQL> shutdown immediate;
SQL>startup upgrade;
For startup migrate;
3. run this two scripts
SQL> spool /tmp/cat_repair_db.txt
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
catalog.sql will recreate Oracle database Catalog Views component and catproc.sql will recreate Oracle database Packages and Types component
4. Recompile all invalid objects
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
5. Shutdown database and startup in open mode
SQL> shutdown immediate;
SQL> startup;
SQL> SELECT r.comp_name, r.version, r.status FROM dba_registry r;
COMP_NAME VERSION STATUS
—————————————- —————————— ———————————
Oracle interMedia 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
8 rows selected
New features in Oracle Database 12c Release 1
SQL:
Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K (from 4K).
We can make a column invisible.
SQL> create table test (column-name column-type invisible);
SQL> alter table table-name modify column-name invisible;
SQL> alter table table-name modify column-name visible;
We can drop an index online.
SQL> DROP INDEX ONLINE.
We can drop an constraint online.
SQL> DROP CONSTRAINT ONLINE .
We can set unused columns online
SQL> SET UNUSED COLUMN ONLINE.
We can make an index visible of invisible to Optimizer.
SQL> ALTER INDEX VISIBLE / INVISIBLE .
Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL).
SQL> create table dept (dept_id number generated as identity, dept_name varchar);
SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);
Temporary undo (for global temporary tables) will not generate undo. We can manage this by using init parameter temp_undo_enabled (=false|true).
Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)
The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc...
Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records.
SQL> select ... fetch first n rows only;
SQL> select ... offset m rows fetch next n rows only;
SQL> select ... fetch first n percent rows only;
SQL> select ... fetch first n percent rows with ties;
Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';
The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
dbms_sql.return_result returns formatted results in SQLPLUS.
Reduces contents of regular UNDO, allowing better flashback operations.
PL/SQL:
PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
SQL WITH clause enhancement - In Oracle 12c, we can declare PL/SQL functions in the WITH clause of a select statement.
Implicit Result Sets - create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).
MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
We can use Booleans values in dynamic PL/SQL. Still no Booleans as database type.
Database:
New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.
RESOURCE role does not include UNLIMITED TABLESPACE anymore.
No need to shutdown database for changing archive log mode.
Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for Backup operations
sysdg for Data Guard operations
syskm for Key management
Like sys, system, we have new default SYS users, in Oracle 12c Release1.
sysbackup for Backup operations
sysdg for Data Guard operations
syskm for Key management
Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y
expdp has transport view, view_as_tables options.
$ expdp ... VIEWS_AS_TABLES=test_view
New ENCRYPTION_PWD_PROMPT parameter allows you to specify whether Data Pump should prompt you for the encryption password, rather than you entering it on the command line.
Enhanced statistics --- frequency histograms can be created upto 2048 distinct values (increased from 254 distinct values), dynamic sampling up to eleven, and stats automatically gathered during load.
Two new types of histograms introduced --- top-frequency histograms and hybrid histograms.
Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
Adaptive execution plans (change of the plan at runtime).
Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
Dynamic sampling has been renamed to Dynamic Statistics in Oracle 12c release 1.
Multi threaded database with parameter threaded_executions. Multiple processes and multiple threads within each process, provides improved performance and manageability through more efficient sharing of system and processor resources.
Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit.
UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace. Queried through v$tempundostat.
Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c version), replaces the Oracle Database console and, is installed automatically.
enable_ddl_logging
Oracle Caching mode: It enables to force caching of all segments into buffer cache automatically(when the segments are accessed). ALTER DATABASE FORCE FULL DATABASE CACHING;
New type of sequences - session sequences, for global temporary tables
Materialized View out-of-place refresh.
DBMS_MVIEW.REFRESH('TEST_MV', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
CSSCAN (Character Set Scanner) and CSALTER utilities replaced with DMU (Database Migration Assistant for Unicode).
Monitor the privilege assignments easy in 12c Oracle with DBMS_PRIVILEGE_CAPTURE.
SELECT ANY DICTIONARY no longer provides access to tables with password hashes (USER$ etc).
Lateral Views - a lateral view is a view that references columns from a table that is not inside the view.
Reduces the size of redo associated with recovering the regular UNDO tablespace.
ASM: (Oracle Grid Infrastructure new features)
Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.
Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.
RMAN:
Table (or table partition) Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required). The table is recovered into an auxiliary instance and there is the option to import the recovered table into a new table or partition using REMAP option or create the expdp dump of the recovered table only, for import at a later time of your choosing.
RMAN> recover table table_name until scn scn_number auxiliary destination on 'path';
RMAN> recover table sh:sales:P1 remap table sh.sales:P1:new_tab
Running SQL commands and executing PLSQL procedures in RMAN without SQL keyword.
RMAN> select * from v$session;
Recover or copy files from Standby databases.
Refresh a single datafile on the primary from the standby (or standby from primary).
Multi-sectional backup functionality, to improve backup and restore operation of large size data files, extended further in 12c to supports image copies and incremental backups along with the full database backupsets.
Table level restoration i.e object level.
Incremental recovery more faster, many of the tasks removed.
In Oracle 12c, ACTIVE DUPLICATE DATABASE doesn’t not required any pre backup existence to clone the database, RMAN first takes backup of datafiles into backupsets, and transmit to the auxiliary location and will be restored/recovered subsequently.
New NOOPEN clause in 12c ACTIVE DUPLICATE DATABASE, will prevent opening database immediately after the cloning process completion and the database will remain in MOUNT state and we have to manually open the database.
Rolling forward/Synchronizing a standby database.
Partitioning:
Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, ...)
SQL> ALTER TABLE ... MOVE PARTITION ... ONLINE...
Multiple partition operations in a single DDL.
Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
Cascade for TRUNCATE and EXCHANGE partition.
Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
Online move of a partition(without DBMS_REDEFINTIION).
Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
We can test patches on database copies, rolling patches out centrally once testing is complete.
Compression:
Automated compression with heat map.
Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
Advanced Row compression (for Hot Data).
Columnar Query compression (for Warm Data).
Columnar Archive compression (for Archive Data).
New parameters, SQLNET_COMPRESSION and SQLNET.COMPRESSION_SCHEME_LIST, allow the compression of data transitioning over Oracle Net Services between client and server. Compression can be enabled at the: Connection level (connect string, URL), Service level (tnsnames.ora, ldap.ora) and Database level (sqlnet.ora).
Data Guard:
Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.
Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.
In 12c it is possible to cascade a Standby Database in Real Time, that is, the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database.
Data Guard Broker now supports cascaded standby.
In 12c Data Guard, if you want to start MRP in RTA (real-time apply), use
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
In 12c Data Guard, if you want to start MRP in non RTA (real-time apply), use ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
Global data services, transparent to failover / switchover no client reconfiguration required.
Data Guard supports Physical Standby Database for a Multitenant Database.
Global Temporary Tables can now be used on an standby databases/Active Data Guard (ADG) standby databases.
OEM (Oracle Enterprise Manager):
Oracle Enterprise Manager Database Express (DB Express), also referred to as EM Express, is a web-based tool for managing Oracle Database 12c, to support basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.
EM Express is similar to DB Control (Oracle Enterprise Manager DB Console) in 10g & 11g, but not (full) replacement because EM Express has very less features.
To use EM Express, a database user needs to have EM_EXPRESS_BASIC or EM_EXPRESS_ALL role. DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles.
SQL> grant EM_EXPRESS_BASIC to user;
SQL> grant EM_EXPRESS_ALL to user;
Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.
Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.
A new admin role "CDB Administrator" has been introduced in Oracle 12.1.0 release databases.
Multiple LGWR processes for each PDB’s and multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.
All Oracle database options/features are available on the PDB level.
RMAN backup at CDB level.
We can unplug a PDB from a CDB to another CDB.
PDB's can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”.
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as whole.
RMAN point-in-time recovery at PDB level (while other PDB's remains open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
Flashback of a PDB should be available for Oracle 12c Release 2.
Entire containers can be backed up in single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.
New Commands:
create pluggable database ...
alter pluggable database ...
drop pluggable database ...
New Views/Packages in Oracle 12c Release1:
dba_pdbs
v$pdbs
cdb_data_files
dbms_pdb
dbms_qopatch
UTL_CALLSTACK
dbms_redact
=================================================================
1. 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.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
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.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.
When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
Table online migration restriction applies here too.
There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. 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:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. 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.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. 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
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
CREATE|ALTER|DROP|TRUNCATE TABLE
DROP USER
CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K (from 4K).
We can make a column invisible.
SQL> create table test (column-name column-type invisible);
SQL> alter table table-name modify column-name invisible;
SQL> alter table table-name modify column-name visible;
We can drop an index online.
SQL> DROP INDEX ONLINE.
We can drop an constraint online.
SQL> DROP CONSTRAINT ONLINE .
We can set unused columns online
SQL> SET UNUSED COLUMN ONLINE.
We can make an index visible of invisible to Optimizer.
SQL> ALTER INDEX VISIBLE / INVISIBLE .
Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL).
SQL> create table dept (dept_id number generated as identity, dept_name varchar);
SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);
Temporary undo (for global temporary tables) will not generate undo. We can manage this by using init parameter temp_undo_enabled (=false|true).
Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)
The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc...
Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records.
SQL> select ... fetch first n rows only;
SQL> select ... offset m rows fetch next n rows only;
SQL> select ... fetch first n percent rows only;
SQL> select ... fetch first n percent rows with ties;
Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';
The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
dbms_sql.return_result returns formatted results in SQLPLUS.
Reduces contents of regular UNDO, allowing better flashback operations.
PL/SQL:
PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
SQL WITH clause enhancement - In Oracle 12c, we can declare PL/SQL functions in the WITH clause of a select statement.
Implicit Result Sets - create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).
MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
We can use Booleans values in dynamic PL/SQL. Still no Booleans as database type.
Database:
New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.
RESOURCE role does not include UNLIMITED TABLESPACE anymore.
No need to shutdown database for changing archive log mode.
Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for Backup operations
sysdg for Data Guard operations
syskm for Key management
Like sys, system, we have new default SYS users, in Oracle 12c Release1.
sysbackup for Backup operations
sysdg for Data Guard operations
syskm for Key management
Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y
expdp has transport view, view_as_tables options.
$ expdp ... VIEWS_AS_TABLES=test_view
New ENCRYPTION_PWD_PROMPT parameter allows you to specify whether Data Pump should prompt you for the encryption password, rather than you entering it on the command line.
Enhanced statistics --- frequency histograms can be created upto 2048 distinct values (increased from 254 distinct values), dynamic sampling up to eleven, and stats automatically gathered during load.
Two new types of histograms introduced --- top-frequency histograms and hybrid histograms.
Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
Adaptive execution plans (change of the plan at runtime).
Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
Dynamic sampling has been renamed to Dynamic Statistics in Oracle 12c release 1.
Multi threaded database with parameter threaded_executions. Multiple processes and multiple threads within each process, provides improved performance and manageability through more efficient sharing of system and processor resources.
Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit.
UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace. Queried through v$tempundostat.
Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c version), replaces the Oracle Database console and, is installed automatically.
enable_ddl_logging
Oracle Caching mode: It enables to force caching of all segments into buffer cache automatically(when the segments are accessed). ALTER DATABASE FORCE FULL DATABASE CACHING;
New type of sequences - session sequences, for global temporary tables
Materialized View out-of-place refresh.
DBMS_MVIEW.REFRESH('TEST_MV', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
CSSCAN (Character Set Scanner) and CSALTER utilities replaced with DMU (Database Migration Assistant for Unicode).
Monitor the privilege assignments easy in 12c Oracle with DBMS_PRIVILEGE_CAPTURE.
SELECT ANY DICTIONARY no longer provides access to tables with password hashes (USER$ etc).
Lateral Views - a lateral view is a view that references columns from a table that is not inside the view.
Reduces the size of redo associated with recovering the regular UNDO tablespace.
ASM: (Oracle Grid Infrastructure new features)
Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.
Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.
RMAN:
Table (or table partition) Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required). The table is recovered into an auxiliary instance and there is the option to import the recovered table into a new table or partition using REMAP option or create the expdp dump of the recovered table only, for import at a later time of your choosing.
RMAN> recover table table_name until scn scn_number auxiliary destination on 'path';
RMAN> recover table sh:sales:P1 remap table sh.sales:P1:new_tab
Running SQL commands and executing PLSQL procedures in RMAN without SQL keyword.
RMAN> select * from v$session;
Recover or copy files from Standby databases.
Refresh a single datafile on the primary from the standby (or standby from primary).
Multi-sectional backup functionality, to improve backup and restore operation of large size data files, extended further in 12c to supports image copies and incremental backups along with the full database backupsets.
Table level restoration i.e object level.
Incremental recovery more faster, many of the tasks removed.
In Oracle 12c, ACTIVE DUPLICATE DATABASE doesn’t not required any pre backup existence to clone the database, RMAN first takes backup of datafiles into backupsets, and transmit to the auxiliary location and will be restored/recovered subsequently.
New NOOPEN clause in 12c ACTIVE DUPLICATE DATABASE, will prevent opening database immediately after the cloning process completion and the database will remain in MOUNT state and we have to manually open the database.
Rolling forward/Synchronizing a standby database.
Partitioning:
Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, ...)
SQL> ALTER TABLE ... MOVE PARTITION ... ONLINE...
Multiple partition operations in a single DDL.
Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
Cascade for TRUNCATE and EXCHANGE partition.
Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
Online move of a partition(without DBMS_REDEFINTIION).
Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
We can test patches on database copies, rolling patches out centrally once testing is complete.
Compression:
Automated compression with heat map.
Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
Advanced Row compression (for Hot Data).
Columnar Query compression (for Warm Data).
Columnar Archive compression (for Archive Data).
New parameters, SQLNET_COMPRESSION and SQLNET.COMPRESSION_SCHEME_LIST, allow the compression of data transitioning over Oracle Net Services between client and server. Compression can be enabled at the: Connection level (connect string, URL), Service level (tnsnames.ora, ldap.ora) and Database level (sqlnet.ora).
Data Guard:
Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.
Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.
In 12c it is possible to cascade a Standby Database in Real Time, that is, the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database.
Data Guard Broker now supports cascaded standby.
In 12c Data Guard, if you want to start MRP in RTA (real-time apply), use
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
In 12c Data Guard, if you want to start MRP in non RTA (real-time apply), use ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
Global data services, transparent to failover / switchover no client reconfiguration required.
Data Guard supports Physical Standby Database for a Multitenant Database.
Global Temporary Tables can now be used on an standby databases/Active Data Guard (ADG) standby databases.
OEM (Oracle Enterprise Manager):
Oracle Enterprise Manager Database Express (DB Express), also referred to as EM Express, is a web-based tool for managing Oracle Database 12c, to support basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.
EM Express is similar to DB Control (Oracle Enterprise Manager DB Console) in 10g & 11g, but not (full) replacement because EM Express has very less features.
To use EM Express, a database user needs to have EM_EXPRESS_BASIC or EM_EXPRESS_ALL role. DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles.
SQL> grant EM_EXPRESS_BASIC to user;
SQL> grant EM_EXPRESS_ALL to user;
Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.
Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.
A new admin role "CDB Administrator" has been introduced in Oracle 12.1.0 release databases.
Multiple LGWR processes for each PDB’s and multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.
All Oracle database options/features are available on the PDB level.
RMAN backup at CDB level.
We can unplug a PDB from a CDB to another CDB.
PDB's can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”.
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as whole.
RMAN point-in-time recovery at PDB level (while other PDB's remains open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
Flashback of a PDB should be available for Oracle 12c Release 2.
Entire containers can be backed up in single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.
New Commands:
create pluggable database ...
alter pluggable database ...
drop pluggable database ...
New Views/Packages in Oracle 12c Release1:
dba_pdbs
v$pdbs
cdb_data_files
dbms_pdb
dbms_qopatch
UTL_CALLSTACK
dbms_redact
=================================================================
1. 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.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
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.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.
When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
Table online migration restriction applies here too.
There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. 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:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. 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.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. 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
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
CREATE|ALTER|DROP|TRUNCATE TABLE
DROP USER
CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
RMAN Commands Summary
Summary
of RMAN Commands
|
Allocate a channel
in preparation for issuing maintenance commands such as DELETE.
|
|
|
A subclause used to
specify which items will be displayed by the LIST command.
|
|
|
Release a channel
that was allocated with an ALLOCATE CHANNEL command.
|
|
|
Release a channel
allocated with an ALLOCATE CHANNEL FOR MAINTENANCE command.
|
|
Subscribe to:
Posts
(
Atom
)