Oracle Database 12c New features Summary (i)

This series of articles is a summary of some of the new features of Oracle database 12c, including database management, RMAN, high availability, and performance tuning, Syed Jaffer Hussain.

1. Online renaming and repositioning of active data files
Unlike previous versions, the migration or renaming of data files in the Oracle database 12c R1 version no longer requires too many tedious steps to put the tablespace into read-only mode, followed by offline operation of the data file. In 12c R1, data files can be renamed and moved online using an SQL statement such as Alter DATABASE move datafile. When this data file is being transmitted, the end user can perform queries, DML, and DDL tasks. In addition, data files can be migrated between storage devices, such as migrating from non-ASM to ASM, and vice versa.
To rename a data file:

SQL>ALTERDATABASE'/u00/data/users01.dbf' to ' /u00/data/users_01.dbf ';

Migrating data files from non-ASM to ASM:

SQL>ALTERDATABASE'/u00/data/users_01.dbf'  to ' +dg_data ';

Migrating data files from one ASM disk group to another ASM disk group:

SQL>ALTERDATABASE'to'+dg_data_ ";

If the data file already exists in the new path, overwrite it with the same name:

SQL>ALTERDATABASE'/u00/data/users_01.dbf' to ' /u00/data_new/users_01.dbf ' reuse;

Copy the file to a new path while preserving its copy under the original path:

SQL>ALTERDATABASE'/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 a complex process. 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 participating in the process without any interruption. In contrast, a DML operation is not allowed if the partition or sub-partition is migrated offline.
Example:

SQL>ALTERTABLE table_name MOVE PARTITION|  to tablespace tablespace_name; SQL>ALTERTABLE table_name MOVE PARTITION|  to UPDATE INDEXES ONLINE;

The first example is used to migrate a table partition or sub-partition to a new table space under offline conditions. The second example is migrating table partitions or sub-partitions online and maintaining any local or global indexes on the table. In addition, DML operations are not interrupted when the online statement is used.

Important NOTES:
The update Indexes statement avoids the situation where any local or global indexes in the table are unusable.
• The online migration restrictions for tables also apply to this.
• By introducing a lock mechanism to complete this process, it can also cause performance degradation and generate a lot of redo, depending on the size of the partition and sub-partitions.

3. Invisible fields
in Oracle 11g R1, Oracle introduces some nice enhancements in the form of invisible indexes and virtual fields. Inheriting the former and carrying forward, Oracle 12c R1 introduced the idea of invisible fields. 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 information 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 it is explicitly mentioned in the SQL statement or condition, or there is described in the table definition. It is very easy to add or modify an invisible field, and vice versa.

SQL>CREATETABLENumber (6varchar2 (9) INVISIBLE); SQL>ALTERTABLE 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 typically requires multiple indexes, and in fact there are many 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 it can also be included in the bitmap index. Note that only one type of index is available at a given time.

　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 useful for knowing 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 recorded in the XML and log files under the $ORACLE_BASE/DIAG/RDBMS/DBNAME/LOG|DDL path. An XML contains information such as DDL commands, IP addresses, timestamps, and so on. This can help determine when a user or table has been deleted or when a DDL statement has been triggered.
Turn on the DDL log feature

SQL>ALTER SYSTEM| SET enable_ddl_logging=TRUE;

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

6. Temporary undo

Each Oracle database contains a set of system-related table spaces, such as System,sysaux,undo & TEMP, and they are used for different purposes in the Oracle database. Prior to Oracle 12c R1, the undo record was generated by a temporary table and stored in the undo tablespace, which is more akin 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 because the information is not written to the undo log, the cost of the undo table space is reduced and the undo data is generated less. You can choose to turn on the temporary undo feature at the session level or at the database level.

　　Enable the temporary undo feature
To use this new feature, you need to make the following settings:
• The compatibility parameter must be set to 12.0.0 or higher
• Enable temp_undo_enabled initialization parameters
• Because temporary undo records are now stored in a temporary table space, you need to have enough space to create this temporary table space
• For 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| 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 perform backup and restore commands in Rman. Therefore, you can create a local user in the database and, without granting its SYSDBA permissions, Grant Sysbackup permissions to perform backup and recovery related tasks in Rman.

　8. How to execute SQL statements in Rman

$ . /Rman target "username/ as Sysbackup"

In 12c, you can execute any SQL and PL/e commands in Rman without the need for a SQL prefix, that is, you can execute any SQL and PL + commands directly from Rman. The following is an example of executing an SQL statement in Rman:

RMAN>SELECT from v$session; RMAN>ALTERADD datafile SIZE 121m;

　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 releases, it was not feasible to use an existing 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 restore a specific table or partition to a point-in-time or SCN from Rman Backup in the event of a drop or truncate.
When you initiate a table or partition recovery through Rman, the approximate process is this:
• Determine the backup set required to restore the table or partition
• During the recovery of a table or partition, a secondary database is temporarily set to a point in time
• Use the data pump to export the required tables or partitions to a dumpfile
• You can import tables or partitions from the source database (optional)
• Rename operations during recovery
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 asSysbackup "; RMAN>RECOVERTABLEUsername.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)REMAPTABLE 'Username.tablename':'Username.new_table_name';--can rename table with this option. (This option can be renamed on the table)

Important NOTES:
• Ensure that there is sufficient space available for the secondary database under the/u01 file system, as well as for data pump files
• There must be a full database backup, or at least system-related tablespace backups
The following are the restrictions and constraints for applying table or partition recovery in Rman:
sys user table or partition cannot be restored
• Tables and partitions stored in the Sysaux and system tablespace cannot be recovered
• Restoring this table is not feasible when the remap option is used to restore a table that contains 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 an initial parameter of size Pga_aggregate_target, Oracle dynamically increases or decreases the PGA size based on workload and demand. 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. Therefore, you can now set a hard limit on the PGA by setting new parameters to avoid over-using the PGA.

SQL>ALTERSET pga_aggregate_limit=2G; SQL>ALTERSET 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.
Original link: http://www.searchdatabase.com.cn/showcontent_74721.htm

Oracle Database 12c New features Summary (i)