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

This article introduces the relevant knowledge of "Summary of the New Features of Oracle Database 12c". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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. Prior to Oracle 12c R1, undo records were generated by temporary tables and stored in the undo tablespace, which was more similar to a generic or persistent table revocation record. However, due to the introduction of temporary undo functionality in 12c R1, those temporary undo records can now be stored in temporary tables instead of undo tablespaces. The main benefit of temporary undo is that because the information is not written to the undo log, the overhead of the undo tablespace is reduced and less undo data is generated. You have the flexibility to turn on the temporary undo function 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:

? 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.

11. Enhancements to table partition maintenance

In the first part, I explained how to migrate a table partition or subpartition to a different tablespace online or offline. In this article, we mainly introduce other improvements in table partitioning.

Add multiple new partitions

Prior to Oracle 12c R1, only one new partition could be added to an existing partition table at a time. To add more than one new partition, you need to execute a separate ALTER TABLE ADD PARTITION statement for each new partition. Oracle 12c only needs a single ALTER TABLE ADD PARTITION command to add multiple new partitions, which increases database flexibility. The following example shows how to add multiple new partitions to an existing partition table:

SQL > CREATE TABLE emp_part

(eno number (8), ename varchar2 (40), 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

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 / subzones

As part of data maintenance, DBA usually performs maintenance tasks to delete or truncate partitions on a partition table. Prior to 12c R1, only one partition could be deleted or truncated at a time for an existing partition table. For Oracle 12c, you can undo or merge multiple partitions and subpartitions with a single ALTER TABLE table_name {DROP | TRUNCATE} PARTITIONS command.

The following example shows how to delete or truncate multiple partitions on an existing partition table:

SQL > ALTER TABLE emp_part DROP PARTITIONS p4 and P5

SQL > ALTER TABLE emp_part TRUNCATE PARTITONS p4 and P5

To keep the index up-to-date, use UPDATE INDEXES or UPDATE GLOBAL INDEXES statements, 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 query the ORPHANED_ENTRIES field under the USER_INDEXES or USER_IND_PARTITIONS dictionary view to find out if any index contains any expired entries.

Divide a single partition into multiple new partitions

The newly enhanced SPLIT PARTITION statement in 12c allows you to split a particular partition or subpartition into multiple new partitions with a single command. The following example shows how to divide a partition into multiple new partitions:

SQL > CREATE TABLE emp_part

(eno number (8), ename varchar2 (40), sal number (6))

PARTITION BY RANGE (sal)

(PARTITION p1 VALUES LESS THAN (10000)

PARTITION p2 VALUES LESS THAN (20000)

PARTITION p_max (MAXVALUE)

);

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)

Merge multiple partitions into a single partition

You can use a single ALTER TBALE MERGE PARTITIONS statement to merge multiple partitions into a single partition:

SQL > CREATE TABLE emp_part

(eno number (8), ename varchar2 (40), 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 ranges form a sequence, you can use the following example:

SQL > ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge

twelve。 Database upgrade improvement

Every time a new version of Oracle is released, the challenge for DBA is the upgrade process. In this section, I will introduce two improvements for upgrades introduced in 12c.

Pre-upgrade script

In 12c R1, the original utlu [121] s.sql script has been replaced by a much improved pre-upgrade information script, preupgrd.sql. In addition to pre-upgrade check verification, this script can also solve various problems that occur before and after the upgrade process in the form of a repair script.

The resulting repair scripts can be executed to resolve different levels of problems, such as pre-upgrade and post-upgrade problems. When upgrading a database manually, the script must be executed manually before the actual upgrade process is initialized. However, when using the DBUA tool to upgrade a database, it automatically executes the pre-upgrade script as part of the upgrade process, and prompts you to execute the repair script to prevent errors.

How to execute a script:

SQL > @ $ORACLE_12GHOME/rdbms/admin/preupgrd.sql

The above script produces a log file and a [pre/post] upgrade_fixup.sql script. All of these files are located in the $ORACLE_BASE/cfgtoollogs directory. Before you proceed with the real upgrade process, you should browse the suggestions mentioned in the log file and execute the script to fix the problem.

Note: be sure to 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 function

The length of the database upgrade time depends on the number of components configured on the database, not on the size of the database. In previous versions, we could not run the upgrade program in parallel to quickly complete the entire upgrade process.

In 12c R1, the original catupgrd.sql script was replaced by the catctl.pl script (parallel upgrade function), and now we can run the upgrade program in parallel mode.

The following process shows how to initialize the parallel upgrade function (3 procedures); you need to run this script after starting the database in upgrade mode:

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 inherits these two new changes.

13. Recover data files over the network

Another important enhancement in 12c R1 is that you can now retrieve or recover data files, control files, parameter files, tablespaces, or entire databases with a service name between the primary and standby databases. This is extremely useful for synchronizing primary and standby databases.

When there is a considerable difference between the primary database and the standby database, you no longer need a complex rollforward process to fill the difference. RMAN can perform standby restores over the network for incremental backups and can apply them to physical standby databases. You can use the service name to copy the required data files directly from the standby point to the primary station, in order to prevent the loss of data files and tablespaces on the primary database, or do not actually recover the data files from the backup set.

The following process demonstrates how to perform a roll forward with this new feature to synchronize the standby database with the primary database:

On the physical standby database:

. / rman target "username/password@standby_db_tns as SYSBACKUP"

RMAN > RECOVER DATABASE FROM SERVICE primary_db_tns USING COMPRESSED BACKUPSET

The above example connects to the primary database using the primary_db_tns connection string defined on the standby database, then performs an incremental backup, transfers these incremental backups to an alternate destination, and then applies the files to the standby database for synchronization. However, you need to make sure that primary_db_tns is configured to point it to the primary database on the backup database side.

In the following example, I will demonstrate a scenario that recovers missing data files on the primary database by getting data files from the standby database:

On the primary database:

. / rman target "username/password@primary_db_tns as SYSBACKUP"

RMAN > RESTORE DATAFILE'+ DG_DISKGROUP/DBANME/DATAFILE/filename' FROM SERVICE standby_db_tns

14. Enhancement to Data Pump

The Data Pump version has a number of useful improvements, such as converting views to tables on export and turning off logging on import.

Turn off the generation of redo logs

A new TRANSFORM option has been introduced in Data Pump, which provides flexibility for objects to turn off redo generation during import. When the DISABLE_ARCHIVE_LOGGING value is specified for the TRANSFORM option, the redo build is turned off throughout the import. This feature relieves stress when importing large tables and reduces excessive redo generation, thus speeding up import. This attribute can also be applied to tables and indexes. 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 shows 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 automatic data diagnosis Monitor (ADDM) Analysis

It is part of each DBA schedule to analyze the health of the database by using automated diagnostic tools such as AWR, ASH, and ADDM. Although each tool can measure the overall health and performance of the database at multiple levels, no tool can be used when the database is unresponsive or completely suspended.

When the database is unresponsive or suspended, and you have configured the cloud control of Oracle Enterprise Manager 12c, you can diagnose serious performance problems. This is very helpful for you to understand what is going on in the current database, and it can also provide a solution to the problem.

The following steps demonstrate how to analyze the database state on Oracle Enterprise Manager 12c:

? In the access database access main page, select the Emergency Monitoring option from the Performance menu. This displays the top blocked sessions in the pending analysis table.

? Select the Real-Time ADDM option from the Performance menu to perform real-time ADDM analysis.

? After collecting the performance data, click the Findings tab to get an interactive summary of all the results.

16. Collect statistics on multiple tables at the same time

In previous Oracle database versions, Oracle was used to collecting statistics one table at a time when you executed a DBMS_STATS program to collect statistics at the table, index, schema, or database level. If the table is large, it is recommended that you use parallel mode. In 12c R1, you can now collect statistics on multiple tables, partitions, and subpartitions at the same time. Before you start using it, you must make the following settings to the database to enable 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')

This is the end of "Summary of the New Features of Oracle Database 12c". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.