Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the new features of Oracle database 12c. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. Rename and relocate active data files online

Unlike previous versions, migrating or renaming data files in Oracle database 12c R1 version no longer requires too many tedious steps, that is, putting the tablespace into read-only mode, followed by offline operations on the data files. In 12c R1, data files can be renamed and moved online using SQL statements such as ALTER DATABASE MOVE DATAFILE. While this data file is being transferred, the end user can perform query, DML and DDL tasks. In addition, data files can be migrated between storage devices, such as from non-ASM to ASM, and vice versa.

Rename the data file:

SQL > ALTER DATABASE MOVE DATAFILE'/ u00 _

Migrate data files from non-ASM to ASM:

SQL > ALTER DATABASE MOVE DATAFILE'/ u00 _

Migrate 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'

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

SQL > ALTER DATABASE MOVE DATAFILE'/ u00 _

Copy the file to a new path while keeping a copy of it under the original path:

SQL > ALTER DATABASE MOVE DATAFILE'/ u00 _

You can monitor this process when you move files by querying the v$session_longops dynamic View. In addition, you can also refer to alert.log,Oracle to record the specific behavior.

two。 Online migration of table partitions or subpartitions

Migrating table partitions or subpartitions to different table spaces in Oracle 12c R1 no longer requires a complex process. Similar to the online migration of unpartitioned tables in previous versions, table partitions or subpartitions can be migrated online or offline to a different tablespace. When the ONLINE statement is specified, all DML operations can be performed on partitions or subpartitions that participate in the process without any interruption. In contrast, DML operations are not allowed if partition or subpartition migration is done offline.

Example:

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 to migrate a table partition or subpartition to a new tablespace when offline. The second example is to migrate table partitions or subpartitions 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 tips:

The UPDATE INDEXES statement avoids situations in which any local or global indexes in the table cannot be used.

The online migration restrictions for tables also apply here.

Introducing a locking mechanism to accomplish this process, of course, can also lead to performance degradation and a large amount of redo, depending on the size of partitions and subpartitions.

3. Invisible field

In Oracle 11g R1, Oracle introduced some nice enhancements in the form of invisible indexes and virtual fields. Inheriting the former and carrying forward, the idea of invisible fields is introduced in Oracle 12c R1. In previous versions, in order to hide important data fields to avoid displaying in generic queries, we used to create a view to hide the required information or apply certain security conditions.

In 12c R1, you can create invisible fields in the table. When a field is defined as invisible, it does not appear in a generic query unless the field is explicitly mentioned in the SQL statement or 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 (40), 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 and partition fields can also be defined as invisible types. However, temporary, external, and cluster tables do not support invisible fields.

4. Multiple indexes on the same field

Prior to Oracle 12c R1, a field could not have multiple indexes in any form. One may wonder why a field usually requires multiple indexes. In fact, there are many fields or sets of fields that require multiple indexes. In 12c R1, as long as the type of index is different, a field can be included in a B-tree index, as well as in an Bitmap index. Note that only one type of index is available at a given time.

5. DDL log

In previous versions, there was no optional method to log DDL operations. 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 feature, you must configure the initial parameters of the ENABLE_DDL_LOGGING. 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. A xml contains DDL commands, IP addresses, timestamps, and other information. This can help determine when a user or table is deleted or when a DDL statement is triggered.

Enable the DDL log function

SQL > ALTER SYSTEM | SESSION SET ENABLE_DDL_LOGGING=TRUE

The following DDL statements may be recorded in the 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 tablespaces, such as SYSTEM,SYSAUX,UNDO & TEMP, and each of them is used for a different purpose in the Oracle database. Before Oracle 12c R1, the undo records generated by temporary tables were stored in the undo tablespace, and the undo records of general and persistent tables were similar. In the temporary undo function of 12c R12, temporary undo records can be stored in a temporary table instead of in the undo tablespace. The main benefit of this is that the undo tablespace is reduced, and because the information is not recorded in the redo log, the generation of redo data is reduced. You can enable the temporary undo option at the session level or the database level.

Enable the temporary undo feature

To use this new feature, you need to make the following settings:

Compatibility parameter must be set to 12.0.0 or higher

Enable TEMP_UNDO_ENABLED initialization parameters

Since temporary undo records are now stored in a temporary tablespace, you need enough space to create this temporary tablespace

For session level, you can use: ALTER SYSTEM SET TEMP_UNDO_ENABLE=TRUE

Query temporary undo information

The dictionary views listed below are used to view or query statistics related to 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. Back up specific user privileges

In 11g R2, SYSASM privileges were introduced to perform specific operations of ASM. Similarly, SYSBACKUP privileges were introduced in 12c to execute backup and restore commands in RMAN. Therefore, you can create a local user in the database and enable him to perform backup and restore related tasks in RMAN by granting SYSBACKUP permission without granting it SYSDBA permission.

. / rman target "username/password as SYSBACKUP"

8. How to execute SQL statements in RMAN

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

RMAN > SELECT username,machine FROM v$session

RMAN > ALTER TABLESPACE users ADD DATAFILE SIZE 121m

9. Table recovery and partition recovery in RMAN

There are two main types of Oracle database backups: logical and physical. Each backup type has its own advantages and disadvantages. In previous versions, it was not feasible to restore tables or partitions with existing physical backups. A logical backup is required to restore a specific object. For 12c R1, you can restore a specific table or partition to a point in time or SCN from a RMAN backup if drop or truncate occurs.

When initiating a table or partition restore through RMAN, the process goes something like this:

Determine the backup set required to restore the table or partition

During the process of restoring 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 operation during restore

The following is an example of a point-in-time recovery of a table through RMAN (make sure you have made a full backup of the earlier database):

RMAN > connect target "username/password as SYSBACKUP"

RMAN > RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP...'

AUXILIARY DESTINATION'/ u01Compact tablerecovery'

DATAPUMP DESTINATION'/ u01amp dpump'

DUMP FILE 'tablename.dmp'

NOTABLEIMPORT-- this option avoids importing the table automatically. (this option avoids automatic import of tables)

REMAP TABLE 'username.tablename':' username.new_table_name';-- can rename table with this option. (this option renames the table)

Important tips:

Ensure that there is enough free space for the secondary database under the / U01 file system, as well as for the data pump file

There must be a full database backup, or at least an SYSTEM-related tablespace backup

The following are the restrictions and constraints for applying table or partition recovery in RMAN:

The SYS user table or partition cannot be restored

Tables and partitions stored under SYSAUX and SYSTEM tablespaces cannot be restored

Restoring a table is not feasible when the REMAP option is used to restore a table that contains NOT NULL constraints

10. Limit the size of PGA

Prior to Oracle 12c R1, there was no option to limit and control the size of PGA. Although you set a size to the initial parameter of PGA_AGGREGATE_TARGET, Oracle will dynamically increase or decrease the size of PGA according to workload and demand. In 12c, you can set a hard limit on PGA by turning on automatic PGA management, which requires setting the PGA_AGGREGATE_LIMIT parameter. Therefore, you can now set hard limits on PGA by setting new parameters to avoid overusing PGA.

SQL > ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G

SQL > ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0;-- disables the hard limit

Important tips:

When the current PGA limit is exceeded, Oracle automatically terminates / aborts the session or process to maintain the most appropriate PGA memory.

This is the end of this article on "what are the new features of Oracle Database 12c?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.