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

This article focuses on "what are the new features of Oracle data version 12.2.0.1.0", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the new features of Oracle data version 12.2.0.1.0.

1. Rename and relocate active data files online

Data files can be renamed and moved online using SQL statements such as ALTER DATABASE MOVE DATAFILE

two。 Online migration of table partitions or subpartitions

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, and any local or global indexes on the table can be maintained

3. Invisible field

You can create invisible fields in a 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.

The invisible field name must be explicitly mentioned 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

Set colinvisible on

Alter table emp modify (sal visible)

4. Multiple indexes on the same field

As long as the form of the index type is different, a field can be included in a B-tree index, as well as in a Bitmap index.

5. DDL log

You can write DDL operations to xml and log files

ALTER SYSTEM | SESSION SET ENABLE_DDL_LOGGING=TRUE

The DDL operation will be recorded under the $ORACLE_BASE/diag/rdbms/DBNAME/log/ddl path

/ u01/app/oracle/diag/rdbms/prod/PROD/log/ddl

6. Temporary undo

In the temporary undo function, 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

Show parameter undo

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

Create global temporary table temp_objects as select * from dba_objects where 1: 2

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

Insert into temp_objects select * from dba_objects

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

Drop table temp_objects

ALTER SYSTEM SET TEMP_UNDO_ENABLED=TRUE

Show parameter undo

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

Create global temporary table temp_objects as select * from dba_objects where 1: 2

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

Insert into temp_objects select * from dba_objects

Select name,value from v$mystat natural join v$statname where name in ('redo entries','redo size')

7. New backup user privileges

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 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"

Backup user permissions

Grant sysbackup to wangkai

Rman target'"wangkai/oracle@prod as sysbackup"'

8. You can execute SQL statements in RMAN

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

9. Table-level recovery in RMAN

You can restore a specific table or partition to a point in time or SCN from a RMAN backup when a drop or truncate occurs

Drop table emp1 purge

RMAN > recover table wk.emp1 until time "to_date ('2018-11-06 14 to_date 0015 0015 00mm hh34:mi:ss')"

2 > auxiliary destination'/ u01qqappActionBackupUniplex tmp'

3 > datapump destination'/ u01qqappActionBackupUniplex tmp'

For implementation details, please see session.log.

Select * from emp1

10. Size limit of PGA

You can set a hard limit on PGA by turning on automatic PGA management, which requires setting the PGA_AGGREGATE_LIMIT parameter

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

Create or replace package demo_pkg

As

Type array is table of char (2000) index by binary_integer

G_data array

End

/

Select a.name mbytes toggchar (b.value199999999') bytes,to_char (round (b.valuepedigree 1024company1024red1), '99999.9') mbytes

From v$statname a dint vault mystat b

Where a.statistic#=b.statistic#

And a.name like'% ga memory%'

Begin

For i in 1..1000000

Loop

Demo_pkg.g_data (I): ='x'

End loop

End

/

11. Enhancements to table partition maintenance

12c you can add multiple new partitions using a single ALTER TABLE ADD PARTITION command

Select TABLE_NAME,PARTITION_NAME,tablespace_name,PARTITION_POSITION,SEGMENT_CREATED from user_tab_partitions

ALTER TABLE sales_history drop PARTITION p5

ALTER TABLE sales_history ADD

Partition p5 VALUES LESS THAN (TO_DATE (01murJANMYY')) tablespace study3

Partition p6 VALUES LESS THAN (TO_DATE (01murJANMYY')) tablespace study3

Select TABLE_NAME,PARTITION_NAME,tablespace_name,PARTITION_POSITION,SEGMENT_CREATED from user_tab_partitions

twelve。 Database upgrade improvement

Pre-upgrade script, pre-upgrade check verification, this script can also solve various problems before and after the upgrade process in the form of repair script

Parallel upgrade function

SQL > @ / u01/app/oracle/product/12.1.0/db_1/rdbms/admin/preupgrd.sql

Graphical interface:

13. Recover data files over the network

You can retrieve or recover data files, control files, parameter files, tablespaces, or the entire database with a service name between the primary database and the standby database

Scenario 1, 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 uses the primary_db_tns connection string defined on the standby database to connect to the primary database, then performs an incremental backup, and then transfers these incremental backups 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

Scenario 2, on the primary database:

. / rman target'"username/password@primary_db_tns as sysbackup"'

RMAN > restore datafile'+ DG_DISKGROUP/DBNAME/DATAFILE/filename' from service standby_db_tns

My own experiment: the standby database deletes several data files and recovers the missing data files by obtaining the data files from the main database.

14. Enhancement to Data Pump

You can convert the view to a table on export and turn off logging on import

Export the view like a table

The views_as_tables parameter allows the view to be exported as a table. The table structure matches the attempt column, and the view query results correspond to the table data.

Views_as tables=

[schema_name.] view_name [: table_name],...

Example:

Conn scott/tiger@prod2

Create view emp_v as select * from emp

Export the view with the views_as_tables parameter.

$expdp scott/tiger views_as_tables=scott.emp_v directory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log

By default, expdp creates a temporary table as a copy of the view, but does not contain data, but exports to provide a meta data source. In addition, you can determine a table with the right structure to replace it, but this may only make sense on a read-only library.

Disable logging option (disable_archive_logging)

The transform parameter of impdp has been expanded to include the disable_archive_logging option, with a default value of "N", which does not affect log behavior; setting this option to "Y" will cause the table and index to set the log property to nologging before import, thus reducing the generation of related logs during import, and then reset the log property to logging after import.

Transform= disable_archive_logging:Y:table

Transform= disable_archive_logging:Y:index

$impdp wk/oracle directory=test_dir dumpfile=emp_v.dmp logfile=impdp_emp_v.log remap_schema=scott:wk transform= disable_archive_logging:Y

(if the database is running in force logging mode, the disable_archive_logging option will have no impact.)

15. Real-time automatic data diagnosis Monitor (ADDM) Analysis

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 you can also give a solution to this problem.

16. Collect statistics on multiple tables at the same time

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

17. Enhancement of ASM

Enhancements in automatic Storage Management (ASM)

Flex ASM

If the ASM instance on the node fails, all databases and instances running on that node are affected.

To avoid a single point of failure of ASM instances, Oracle 12c provides a feature called Flex ASM.

Flex ASM is a different concept and architecture, and only a small number of ASM instances need to run on some servers in the cluster.

When an ASM instance on one node fails, the Oracle cluster automatically starts an alternative ASM instance on a different node to enhance availability.

In addition, this setting provides ASM instance load balancing capabilities for instances running on this node. Another advantage of Flex ASM is that it can be configured on a separate node.

Relaxation of ASM storage restrictions

ASM supports 511 ASM disk groups, compared with 63 in 11g R2.

The disk size of 20 PB in 11g R2 has now been increased to 32 PB.

Optimization of ASM equalization operation

The new EXPLAIN WORK FOR statement in 12c measures the amount of work required for a given ASM equalization operation and enters the result in the V$ASM_ESTIMATE dynamic view

Using this dynamic view, you can adjust the POWER LIMIT statement to improve the rebalance operation

ASM disk cleanup

In an ASM disk group, the new ASM disk cleanup operation is divided into normal or highly redundant levels, which can verify logical data corruption on all disks in the ASM disk group.

And the logical corruption can be repaired automatically, and if logical data corruption is detected, ASM will be used to mirror the disk.

Disk cleanup can be performed on a disk group, a specific disk, or a file so that its impact can be minimized

Active session History of ASM (ASH)

The V$ACTIVE_SESSION_HISOTRY dynamic view can now also provide active session sampling of ASM instances, however, the use of diagnostic packages is subject to license restrictions.

18. Enhancements to grid (Grid) infrastructure

Flex cluster

Oracle 12c supports two types of configurations during cluster installation: traditional standard clusters and Flex clusters.

In a traditional standard cluster, all the nodes in the cluster are closely integrated with each other, interact through the private network, and have direct access to storage.

On the other hand, Flex clusters introduce two types of nodes between Hub and Leaf node structures.

The nodes assigned in Hub are similar to traditional standard clusters. They are interconnected to each other through private networks and have direct read and write access to storage.

Unlike Hub nodes, Leaf nodes do not need to access the underlying storage directly; instead, they access storage and data through Hub nodes

OCR backups in ASM disk groups

For 12c Magi OCR can now be backed up in the ASM disk group.

This simplifies access to OCR backup files through all nodes. To prevent OCR recovery, you don't have to worry about which node the latest backup of OCR is on.

The latest backup stored in ASM can be easily identified from any node and the restore can be easily performed

Support for IPv6 protocol

Oracle supports IPv6 network protocol configuration for 12c Oracle.

You can now configure public or private network interfaces on IPv4 or IPv6, however, you need to make sure that the same IP protocol is used on all nodes in the cluster

19. Enhancements to RAC database

What-if command evaluation

With srvctl using the new What-if command to evaluate options, you can now determine the impact of running this command.

This newly added command to srvctl can be simulated without actually executing it or making any changes to the current system.

This is especially useful when you want to make changes to an existing system but the results are uncertain.

In this way, this command provides the effect of making changes. The-eval option can also be used through the crsctl command

Improvement of srvctl

There are also some new features for the srvctl command. The following demonstrates how to use these new features to stop or start a database or instance resource on a cluster.

Srvctl start database | instance-startoption NOMOUNT | MOUNT | OPEN

Srvctl stop database | instance-stopoption NOMOUNT | MOUNT | OPEN

At this point, I believe you have a deeper understanding of "what are the new features of Oracle data version 12.2.0.1.0". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.