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

What are the new features of Oracle database 12c? most people do not understand the knowledge points of this article, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what are the new features of Oracle database 12c" article.

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

two。 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.

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

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

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

6. 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'); the above is about "what are the new features of Oracle database 12c?". I believe you all have some understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please pay attention to the industry information channel.

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.