Oracle Database 12c 16 Features Summary

Oracle 12c has been released for a long time, has been trying to find a way to learn a bit, after all, the subsequent 12c will gradually replace the existing database version, become the mainstream database version. Now 12c some of the most commonly used

Characteristics for everyone to study together.

1. Online renaming and repositioning of active data files

Unlike previous versions, migrating or renaming data files in the Oracle database 12c R1 version no longer requires too many tedious steps to empty the table
is placed in read-only mode, followed by offline operation of the data file. In 12c R1, you can use the Alter DATABASE MOVE datafile this
-like SQL statements to rename and move data files online. When this data file is being transmitted, the end user can execute queries, DML, and DDL
Aspects of the task. In addition, data files can be migrated between storage devices, such as migrating from non-ASM to ASM, and vice versa.

renaming data files:
          sql> ALTER DATABASE MOVE datafile '/u00/data/users01.dbf ' to '/u00/data/users_01.dbf ';
migrating data files from non-ASM to ASM:
         sql > ALTER DATABASE MOVE datafile '/u00/data/users_01.dbf ' to ' +dg_data ';
migrating data files from one ASM disk group to another ASM disk group :
         SQL> ALTER DATABASE MOVE DataFile ' +dg_data/dbname/datafile/users_01.dbf ' to ' +dg_data_02 ';

overwrites the data file with the same name if it already exists in the new path:
         SQL> ALTER DATABASE MOVE DataFile '/u00/data/users_01.dbf ' to '/u00/data_new/users_01.dbf ' reuse;
copies the file to a new path while preserving its copy under the original path:
         SQL> ALTER DATABASE MOVE DataFile '/u00/data/users_01.dbf ' to '/u00/data_new/users_01.dbf ' KEEP;
You can monitor this process when you move a file by querying the v$session_longops dynamic view. Alternatively, you can refer to the alert.log,oracle where the specific behavior is recorded.

2. Online migration of table partitions or sub-partitions
migrating table partitions or sub-partitions to different table spaces in Oracle 12c R1 no longer requires complex procedures. Similar to an online migration of a non-partitioned table in a previous version
, table partitions or sub-partitions can be migrated online or offline to a different table space. When the online statement is specified, all DML operations can be performed on the partition or sub-partition that participates in the process without
any interruption. In contrast, a DML operation is not allowed if the partition or sub-partition is migrated offline for
.
example:
sql> ALTER TABLE TBCS. subs_privilege   MOVE subpartition p_532_subp01   tablespace ggusr;
sql> ALTER TABLE TBCS. Subs_privilege   MOVE subpartition p_532_subp01   tablespace ggusr UPDATE INDEXES ONLINE; The first example of
is to migrate a table partition or sub-partition to a new tablespace under offline conditions. The second example is to migrate table partitions or sub-
partitions online and maintain any local or global indexes on the table. In addition, DML operations are not interrupted when the online statement is used.

Important NOTES:
1. The UPDATE indexes statement prevents any local or global indexes in the table from being used.
2. The online migration restrictions for the table also apply to this.
3, the introduction of locking mechanism to complete this process, of course, it will also cause performance degradation and will produce a lot of redo, depending on the size of partitions and sub-partitions.

3. Invisible fields

　　In Oracle 11g R1, Oracle introduces some nice enhancements in the form of invisible indexes and virtual fields. Inherit the former and flourish in Oracle 12c R1

The idea of invisible fields is introduced. In previous releases, in order to hide important data fields to avoid being displayed in generic queries, we often created a view to hide the required letters

or apply certain security conditions.
　　In 12c R1, you can create an invisible field in a table. When a field is defined as not visible, this field does not appear in the generic query unless the SQL statement or

There is an explicit reference to this field in the condition, or there is a described in the table definition. It is very easy to add or modify an invisible field, and vice versa.

sql> CREATE TABLE EMP (ENO number (6), ename name VARCHAR2 (+), Sal number (9) INVISIBLE);
sql> ALTER TABLE emp MODIFY (sal visible);
　　You must explicitly mention the Invisible field name in the INSERT statement to insert the invisible field into the database. Virtual fields and partition fields can also be defined as invisible types.

However, temporary tables, external tables, and cluster tables do not support invisible fields.

4. Multiple indexes on the same field
Before Oracle 12c R1, a field cannot have multiple indexes in any form. Maybe someone would like to know why a field usually needs to have multiple indexes, in fact

There are a number of fields or field collections that require multiple indexes. In 12c R1, a field can be contained in a B-tree index, as long as the type of the index is different, and the same can be

Included in the bitmap index. Note that only one type of index is available at a given time.

Sql> CREATE index XULQ.IDX_XIAOXU_TABNAME1 on XULQ.XIAOXU_01 (table_name) tablespace users; ---indexes can be created
Sql> Create bitmap index xulq.bitidx_xiaoxu_tabname on XULQ.XIAOXU_01 (table_name) tablespace users;

---ORA-01408: This column list is indexed and the reason is being analyzed.

5. DDL logs
There is no alternative method for logging DDL operations in previous versions. In 12c R1, you can now write DDL operations to XML and log files. This is for understanding

It is very useful who executed the Create or drop command at what time. To enable this function, the enable_ddl_logging initial parameters must be configured. This parameter can be

Set at the database or session level. When this parameter is enabled, all DDL commands are logged in the

$ORACLE the XML and log files under the _BASE/DIAG/RDBMS/DBNAME/LOG|DDL path. An XML contains information such as DDL commands, IP addresses, timestamps, and so on. This can help

Determines when a user or table is deleted or when a DDL statement is triggered.

Turn on the DDL log feature
Sql> ALTER system|  SESSION SET enable_ddl_logging=true; ----SYSTEM-level modifications can take effect without restarting the database.

The following DDL statements may be recorded in an XML or log file:
CREATE | ALTER | DROP | TRUNCATE TABLE
DROP USER
CREATE | ALTER | DROP Package | FUNCTION | VIEW | Synonym | SEQUENCE.

Example:

Sql> CREATE TABLE XULQ. xiaoxu_ddl_t as SELECT * from DUAL;

DDL Log logs:

[[email protected] ddl]$ cat log.xml
<msg time= ' 2016-05-26t22:21:10.333+08:00 ' org_id= ' Oracle ' comp_id= ' RDBMS '
 msg_id= ' kpdblogddl:18370:2946163730 ' type= ' UNKNOWN ' group= ' diag_adl '
 level= ' Host_id= ' ora12c ' host_addr= ' 127.0.0.1 '
 version= ' 1 ';
 <txt>create TABLE xulq. xiaoxu_ddl_t as SELECT * from DUAL
 </txt>

6. Temporary undo
　　Each Oracle database contains a set of system-related table spaces, such as System,sysaux,undo & TEMP, and their number in Oracle
Each of the libraries is used for different purposes. Prior to Oracle 12c R1, the undo record was generated by a temporary table and stored in the Undo table space, which is more similar
To a common or persistent table revocation record. However, because of the temporary undo feature introduced in 12c R1, those temporary undo records can now be stored in
Temporary tables, instead of being stored in the undo table space. The main benefit of temporary undo is that the undo table space costs because the information is not written to the undo log
Reduce and generate less undo data. You can choose to turn on the temporary undo feature at the session level or at the database level.

To enable the temporary undo feature to use this new feature, you need to make the following settings:
1. The compatibility parameter must be set to 12.0.0 or higher
2. Enable temp_undo_enabled initialization parameters
3. Since the temporary undo record is now stored in a temporary table space, you need to have enough space to create this temporary table space
4, for the session level, you can use: ALTER SYSTEM SET temp_undo_enable=true;

Querying for temporary undo information
The dictionary views listed below are used to view or query statistics about temporary undo data:
V$tempundostat
Dba_hist_undostat
V$undostat

To disable this feature, you only need to make the following settings:
Sql> ALTER system| SESSION SET Temp_undo_enabled=false;

7. Backing up specific user privileges
In 11g R2, sysasm privileges were introduced to perform specific operations of ASM. Similarly, the Sysbackup privilege was introduced in 12c to
Execute backup and restore commands in Rman. Therefore, you can create a local user in the database and, without granting its SYSDBA permissions,
Granting Sysbackup permissions allows it to perform backup and recovery related tasks in Rman.
$./rman Target "Username/password as Sysbackup"

8. How to execute SQL statements in Rman

In 12c, you can execute any SQL and PL/e commands in Rman without requiring a SQL prefix, that is, you can execute directly from Rman
Any SQL and PL/SQL commands. The following is an example of executing an SQL statement in Rman:
Rman> SELECT username,machine from V$session;
Rman> ALTER tablespace users ADD datafile SIZE 121 m;

9. Table recovery and partition recovery in Rman

Oracle database backup is divided into two main categories: logical and physical backups. Each type of backup has its own advantages and disadvantages. In previous versions, use the current
It is not feasible to have a physical backup to recover a table or partition. In order to recover a specific object, a logical backup is required. For 12c R1, you can take a drop or
Truncate a specific table or partition from an Rman backup to a point in time or SCN.
When you initiate a table or partition recovery through Rman, the approximate process is this:
1. Determine the backup set required to restore the table or partition
2, in the process of recovering a table or partition, a secondary database is temporarily set to a point in time
3, using the data pump to export the required table or partition to a dumpfile
4. You can import tables or partitions from the source database (optional)
5. Rename operation during recovery process
　　
The following is an example of a point-in-time recovery of a table via Rman (make sure you have a full backup of the earlier database):
Rman> Connect Target "Username/password as Sysbackup";
rman> RECOVER TABLE username.tablename UNTIL time ' TIMESTAMP ... '
Auxiliary DESTINATION '/u01/tablerecovery '
DataPump DESTINATION '/u01/dpump '
DUMP FILE ' tablename.dmp '
Notableimport--This option avoids importing the table automatically. (this option avoids importing tables automatically)
REMAP TABLE ' username.tablename ': ' Username.new_table_name '; --Can rename table with this option.
(This option can be renamed on the table)
Important NOTES:
1. Ensure that there is enough space available for the secondary database under the/u01 file system, as well as the data pump files
2. There must be a full database backup, or at least system-related tablespace backup

The following are the restrictions and constraints for applying table or partition recovery in Rman:
1. sys user table or partition cannot be restored
2. Tables and partitions stored in the Sysaux and system tablespace cannot be restored
3. Restoring this table is not feasible when the remap option is used to restore a table containing a NOT NULL constraint

10. Limit the size of the PGA
Prior to Oracle 12c R1, there was no option to limit and control the size of the PGA. Although you set a size of
Pga_aggregate_target's initial parameters, Oracle dynamically increases or decreases the PGA size based on workload and demand. And in 12c
, you can set a hard limit on the PGA by turning on automatic PGA management, which requires setting the Pga_aggregate_limit parameter. So
You can now set a hard limit on the PGA by setting a new parameter to avoid over-using the PGA.
sql> ALTER SYSTEM SET pga_aggregate_limit=2g;
sql> ALTER SYSTEM SET pga_aggregate_limit=0; --disables the hard limit
Important NOTES:
When the current PGA limit is exceeded, Oracle automatically terminates/aborts the session or process to maintain the most appropriate PGA memory.

11. Enhancements to table partitioning maintenance

In the first part, I explained how to migrate a table partition or sub-partition to another table space, either online or offline. In this article, the master
To introduce improvements to other aspects of table partitioning.
11.1. Add multiple new partitions
Before Oracle 12c R1, it is possible to add only one new partition to an existing partition table at a time. To add more than one new partition, you need to
The new partition executes the ALTER TABLE ADD partition statement individually. Oracle 12c only needs to use a separate: ALTER TABLE ADD
The PARTITION command adds multiple new partitions, which adds flexibility to the database. The following example shows how to add multiple new partitions to an existing partition

To create a test table:
sql> CREATE TABLE Emp_part
(Eno number (8), ename varchar2 (+), Sal number (6))
PARTITION by RANGE (SAL)
(PARTITION p1 VALUES less THAN (10000),
PARTITION P2 VALUES less THAN (20000),
PARTITION P3 VALUES less THAN (30000)
);

Add two new partitions:
sql> ALTER TABLE emp_part ADD PARTITION P4 values less THAN (35000), PARTITION P5 values less THAN (40000);
Similarly, as long as the MaxValue partition does not exist, you can add multiple new partitions to a list and system partition table.
How to delete and truncate multiple partitions/sub-partitions
As part of data maintenance, DBAs typically delete or truncate partition maintenance tasks on a partitioned table. Before 12c R1, for a
An existing partitioned table can only delete or truncate one partition at a time. For Oracle 12c, you can use a single:

ALTER TABLE table_name{drop| TRUNCATE} partitions command to undo or merge multiple partitions and sub-partitions.

The following example shows how to delete or truncate multiple partitions on an existing partitioned table:
sql> ALTER TABLE emp_part TRUNCATE PARTITION p4,p5;
sql> ALTER TABLE emp_part DROP partitions p4,p5;

To keep the index updated, use the update indexes or the update GLOBAL indexes statement as follows:
sql> ALTER TABLE emp_part DROP partitions p4,p5 UPDATE GLOBAL INDEXES;
sql> ALTER TABLE emp_part TRUNCATE partitions p4,p5 UPDATE GLOBAL INDEXES;
If you delete or truncate a partition without using the update GLOBAL INDEXES statement, you can use the user_indexes or
Query the Orphaned_entries field under the User_ind_partitions dictionary view to find out if any of the indexes contain any expired entries.
Splitting a single partition into multiple new partitions
The newly enhanced split PARTITION statement in 12c allows you to split a specific partition or sub-partition into multiple new points using only a single command
Area. The following example shows how to split a partition into several new partitions:

sql> ALTER TABLE emp_part SPLIT PARTITION P_max into (PARTITION P3 VALUES less THAN (25000),
PARTITION P4 VALUES less THAN (30000), PARTITION P_max);
Merging multiple partitions into one partition

You can use a single alter Tbale merge partitions statement to merge multiple partitions into a separate partition:
Sql>

CREATE TABLE Emp_part
(Eno number (8), ename varchar2 (+), Sal number (6))
PARTITION by RANGE (SAL)
(PARTITION p1 VALUES less THAN (10000),
PARTITION P2 VALUES less THAN (20000),
PARTITION P3 VALUES less THAN (30000),
PARTITION P4 VALUES less THAN (40000),
PARTITION P5 VALUES less THAN (50000),
PARTITION P_max (MAXVALUE)
);

sql> ALTER TABLE emp_part MERGE partitions p3,p4,p5 into PARTITION p_merge;
If the partition range forms a sequence, you can use the following example:
sql> ALTER TABLE emp_part MERGE partitions p3 to P5 into PARTITION p_merge;

12. Database Upgrade Improvements

Whenever a new version of Oracle is released, the challenge for DBAs is the upgrade process. In this section I will introduce the two changes introduced in 12c for the upgrade
Into.
　　Pre-upgrade Scripts
In 12c R1, the original Utlu[121]s.sql script was replaced by a significantly improved pre-upgrade information script Preupgrd.sql. In addition to the pre-upgrade check
This script can also address various issues that occur before and after the upgrade process in the form of a repair script.
The resulting repair scripts can be executed to address different levels of issues, such as pre-upgrade and post-upgrade issues. When you manually upgrade the database, the foot
This must be done manually before the actual upgrade process is initialized. However, when using the Dbua tool for database upgrades, it will use the pre-upgrade script as
Part of the upgrade process is automatically executed, and you are prompted to execute a repair script to prevent an error.
　　How to execute the script:
sql> @ $ORACLE _12ghome/rdbms/admin/preupgrd.sql
The above script will produce a log file and a [Pre/post]upgrade_fixup.sql script. All of these files are located in
$ORACLE the _base/cfgtoollogs directory. Before you continue with the real upgrade process, you should browse through the recommendations mentioned in the log file and execute the script to fix the problem.
　　Attention:

Make sure you copy the Preupgrd.sql and Utluppkg.sql scripts from the 12c Oracle directory home/rdbms/admin directory to the current Oracle Database/rdbms/admin path.

Parallel upgrade Feature

The duration of the database upgrade depends on the number of components configured on the database, not the size of the database. In previous releases, we were unable to run the upgrade program in parallel to quickly complete the upgrade process.
In 12c R1, the original Catupgrd.sql script was replaced by the catctl.pl script (parallel upgrade feature) and now we can run the upgrade in parallel mode.
The following procedure describes how to initialize the parallel upgrade feature (3 procedures); You need to run this script in upgrade mode after you start the database:
CD $ORACLE _12_home/perl/bin
$./perl Catctl.pl–n 3-catupgrd.sql
The above two steps need to be run when you manually upgrade the database. And Dbua also inherited these two new changes.

13. Recovering data files over the network
Another important enhancement in 12c R1 is that You can now re-obtain or recover data from the primary and standby databases using a service name
, control files, parameter files, tablespaces, or entire databases. This is extremely useful for synchronizing the primary and standby databases.
When there are significant differences between the primary and standby databases, you no longer need complex roll-forward processes to fill in the differences between them. Rman can
perform alternate recoveries over the network for incremental backups, and they can be applied to a physical standby database. You can use the service name to directly copy the required text
from the backup point to the master, in order to prevent the loss of data files, tablespaces, or the actual recovery of the data files from the backup set on the primary database.
The following process demonstrates how to perform a roll forward with this new feature to synchronize the standby and primary databases:
on the physical standby database:
./rman Target "Username/[email protected]_db_tns　　As Sysbackup "
rman> RECOVER DATABASE from SERVICE primary_db_tns USING compressed BACKUPSET;
The example above uses the PRIMARY_DB_TNS connection string defined on the standby database to connect to the primary database, performs an incremental backup, transfers the
incremental backups to the alternate destination, and then applies the files to the standby database for synchronization. However, you need to ensure that Primary_db_tns
has been configured to point to the primary database on the backup database side.
In the following example, I will demonstrate a scenario that recovers a missing data file on the primary database by getting a data file from the standby database:
on the primary:
./rman Target "username/[email protected]_ Db_tns as Sysbackup "
rman> RESTORE datafile ' +dg_diskgroup/dbanme/datafile/filename ' from SERVICE standby_db_tns ;

14. Enhancements to the data pump
The Data pump version has a number of useful improvements, such as converting a view to a table when exporting, and turning off logging on import.
The new transform option is introduced in the Generate Data pump for closing the redo log, which provides the flexibility to turn off redo generation during an object import. When the
The transform option specifies the disable_archive_logging value, and the redo build is turned off during the entire import. This one
The feature relieves stress when importing large tables and reduces excessive redo generation, which speeds up the import. This property can also be applied to tables and indexes. To
The following example demonstrates this feature:
$./IMPDP directory=dpump dumpfile=abcd.dmp Logfile=abcd.log transform=disable_archive_logging:y
Convert a view to a table
This is another improvement in data pump. With the Views_as_tables option, you can load the view data into the table. The following example demonstrates
How to load view data into a table during the export process:
$./EXPDP directory=dpump dumpfile=abcd.dmp Logfile=abcd.log views_as_tables=my_view:my_table

15. Real-time Automated Data Diagnostic monitor (ADDM) analysis

By using automated diagnostic tools such as AWR, Ash, and ADDM to analyze the health of your database, it is one of every DBA's schedule work
Score of Although each tool can measure the overall health and performance of a database at multiple levels, there is no tool that can slow down or end up in the database
Used when all hangs up.
When the database is unresponsive or hangs, and you have configured the cloud control of Oracle Enterprise Manager 12c, you can ask for serious performance
Diagnose the problem. This can help you understand what's going on in the current database, and it also gives you a solution to the problem.
The following steps demonstrate how to analyze the database state on Oracle Enterprise Manager 12c:
1. Select the Emergency monitoring option from the Performance menu in the Access database Access Main page. This will show the top of the pending analysis table
Blocking sessions.
2. Select the real-time ADDM option in the Performance menu to perform a real-time ADDM analysis.
3. After collecting the performance data, click the Findings tab to get an interactive summary of all the results.

16. Collect statistical data on multiple tables at the same time
In previous versions of Oracle database, when you executed a dbms_stats program to collect statistics at the table, index, schema, or database level
Oracle is accustomed to collecting statistics for one table at a time. If the table is large, it is recommended that you take a parallel approach. In 12c R1, you can now
Collects statistics on multiple tables, partitions, and sub-partitions. Before you start using it, you must set the following settings on the database to turn on this feature:
sql> ALTER SYSTEM SET resource_manager_plan= ' Default_main ';
sql> ALTER SYSTEM SET job_queue_processes=4;
Sql> EXEC dbms_stats. Set_global_prefs (' CONCURRENT ', ' all ');
Sql> EXEC dbms_stats. Gather_schema_stats (' SCOTT ');

Oracle Database 12c 16 Features Summary