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

This article introduces what the new features of oracle12c refer to. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

I. the first part

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

II. Part II

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

3. The third part includes:

1. Enhancements in ASM

two。 Enhancements to grid infrastructure

3. Enhancement of Oracle RAC

1. Enhancements in automatic Storage Management (ASM)

Flex ASM

In a typical grid infrastructure installation environment, each node runs its own instance of ASM as a storage container for the database running on this node. However, there is a corresponding risk of single point of failure in this setting. For example, if the ASM instance on this node fails, all databases and instances running on this 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.

When the Flex Cluster option is selected as the first part of the cluster installation environment, the Flex ASM configuration is automatically selected due to the requirements of Flex Cluster. Traditional clusters are also applicable to Flex ASM. When you decide to use Flex ASM, you must make sure that the required network is available. You can choose the Flex ASM storage option as part of the cluster installation environment, or use ASMCA to enable Flex ASM in a standard cluster environment.

The following command shows the current ASM mode:

$. / asmcmd showclustermode

$. / srvctl config asm

Or connect to the ASM instance and query the INSTANCE_TYPE parameter. If the output value is ASMPROX, then Flex ASM is already configured.

Relaxation of ASM storage restrictions

The ASM storage hard limit has increased significantly in terms of the maximum ASM disk group and disk size. ASM supports 511 ASM disk groups in 12c R1 and 63 in 11g R2. Similarly, this number has now been increased to 32 PB compared to the disk size of 20 PB in 11g R2.

Optimization of ASM equalization operation

The new EXPLAIN WORK FOR statement in 12c is used to measure the amount of work required for a given ASM equalization operation and to enter 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. For example, if you want to measure the amount of work required to add a new ASM disk, you can use the following command before actually performing a manual equalization operation:

SQL > EXPLAIN WORK FOR ALTER DISKGROUP DG_DATA ADD DISK data_005

SQL > SELECT est_work FROM V$ASM_ESTIMATE

SQL > EXPLAIN WORK SET STATEMENT_ID='ADD_DISK' FOR ALTER DISKGROUP DG_DATA AD DISK data_005

SQL > SELECT est_work FROM V$ASM_ESTIMATE WHERE STATEMENT_ID = 'ADD_DISK'

You can adjust the POWER limits according to the output from the dynamic view to improve the equalization 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 detect logical data corruption on all disks in the ASM disk group, and can automatically repair logical corruption. If logical data corruption is detected, ASM mirrored disks are used. Disk cleanup can be performed on a disk group, a specific disk, or a file so that its impact can be minimized. The following demonstrates the disk cleanup scenario:

SQL > ALTER DISKGROUP dg_data SCRUB POWER LOW:HIGH:AUTO:MAX

SQL > ALTER DISKGROUP dg_data SCRUB FILE'+ DG_DATA/MYDB/DATAFILE/filename.xxxx.xxxx'

REPAIR POWER AUTO

Active session History of ASM (ASH)

The V$ACTIVE_SESSION_HISOTRY dynamic view can now also provide sampling of active sessions for ASM instances. However, the use of diagnostic packages is restricted by licensing.

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

You can configure up to 64 Hub nodes, and Leaf nodes can have more. In an Oracle Flex cluster, there is no need to configure a Leaf node to have a Hub node, while without a Hub node, a Leaf node would not exist. For a single Hub node, you can configure multiple Leaf nodes. In an Oracle Flex cluster, only Hub nodes directly access OCR and Voting disks. This is a great feature when you are planning a large-scale clustered environment. This series of settings will greatly reduce interconnection congestion and provide space for traditional standard clusters to expand the cluster.

There are two ways to deploy Flex clusters:

1. Deploy when configuring a new cluster

two。 Upgrade a standard cluster mode to a Flex cluster

If you are configuring a completely new cluster, you need to select the type of cluster configuration in step 3, select the configure a Flex cluster option, and then you need to classify the Hub and Leaf nodes in step 6. For each node, select whether the corresponding role is Hub or Leaf, and the virtual hostname is optional.

Converting a standard cluster mode to Flex cluster mode requires the following steps:

1. Use the following command to get the current state of the cluster:

$. / crsctl get cluster mode status

two。 Execute the following command as the root user:

$. / crsctl set cluster mode flex

$. / crsctl stop crs

. / crsctl start crs-wait

3. Change the node role according to the design:

$. / crsctl get node role config

$. / crsctl set node role hub | leaf

$. / crsctl stop crs

. / crsctl start crs-wait

Note:

You cannot revert to standard cluster mode from Flex

Changing the cluster node mode requires the cluster stack to stop.

Ensure that GNS is configured with a fixed VIP

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.

The following demonstrates how to set the ASM disk group to the OCR backup location:

$. / ocrconfig-backuploc + DG_OCR

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.

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

For example, if you want to know what happens when you stop a particular database, you can use the following example:

. / srvctl stop database-d MYDB-eval

. / crsctl eval modify resource-attr "value"

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

IV. The fourth part includes:

How to truncate a master table when a child table contains data

Restrict the recording of Top-N query results

Various enhancements to SQL*Plus

Session level sequence

Improvement of WITH statement

Extended data type

Truncation table CASCADE

In previous versions, truncation of a master table was not provided when the child table referenced a master table and where records existed in the child table. On the other hand, the TRUNCATE TABLE with CASCADE operation in 12c can truncate the records in the main table, recursively truncate the child tables automatically, and refer to them as DELETE ON CASCADE obeying foreign keys. Because this applies to all child tables, there is no CAP for the number of recursive levels, which can be grandchild tables, great-grandchild tables, and so on.

This enhancement rejects the premise of truncating all child table records before truncating a master table. The new CASCADE statement can also be applied to table partitions and child table partitions.

SQL > TRUNCATE TABLE CASCADE

SQL > TRUNCATE TABLE PARTITION CASCADE

If the ON DELETE CASCADE option is not defined for the foreign key of the child table, an ORA-14705 error is thrown.

Restrict records for Top-N query results

In previous versions, there were a variety of indirect means to get Top-N query results for top or bottom records. In 12c, the process is simplified and made more straightforward with a new FETCH FIRST | NEXT | PERCENT statement. To retrieve the top 10 payroll records from the EMP table, you can use the following new SQL statement:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 ROWS ONLY

The following example gets all similar records for the top N. For example, if the salary value in row 10 is 5000 and there are other employees whose salaries meet the top N criteria, they will also be obtained by the WITH TIES statement.

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 ROWS ONLY WITH TIES

The following example restricts getting the top 10% records from the EMP table:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

FETCH FIRST 10 PERCENT ROWS ONLY

The following example ignores the first five records and displays the next five records of the table:

SQL > SELECT eno,ename,sal FROM emp ORDER BY SAL DESC

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

All of these limitations can also be applied to PL/SQL blocks.

BEGIN

SELECT sal BULK COLLECT INTO sal_v FROM EMP

FETCH FIRST 100 ROWS ONLY

END

Various enhancements to SQL*Plus

The implicit result of SQL*Plus: in 12c, SQL*Plus returns the result from an implicit cursor of a PL/SQL block without actually binding a RefCursor. This new dbms_sql.return_result procedure will return and format the results specified by the SELECT statement query in the PL/SQL block. The following code describes this usage:

SQL > CREATE PROCEDURE mp1

Res1 sys_refcursor

BEGIN

Open res1 for SELECT eno,ename,sal FROM emp

Dbms_sql.return_result (res1)

END

SQL > execute mp1

When this process is performed, the formatted record is returned on the SQL*Plus.

Show invisible fields: in the first part of this series, I have explained the new features of invisible fields. When fields are defined as invisible, they will not be displayed when describing the table structure. However, you can display information about invisible fields by making the following settings at the SQL*Plus prompt:

SQL > SET COLINVISIBLE ON | OFF

The above settings are valid only for the DESCRIBE command. It currently does not have an effect on the results of SELECT statements on invisible fields.

Session level sequence

New session-level database sequences can now be created in 12c to support session-level sequence values. The types of these sequences are most suitable for global temporary tables with session levels.

The session-level sequence produces a unique range of values that are limited within, rather than beyond, the session. Once the session is terminated, the state of the session sequence also disappears. The following example explains the creation of a session-level sequence:

SQL > CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION

SQL > ALTER SEQUENCE my_seq GLOBAL | SESSION

For session-level sequences, CACHE, NOCACHE, ORDER, or NOORDER statements are ignored.

Improvement of WITH statement

In 12c, you can use SQL to run PL/SQL functions or procedures faster, which are defined and declared by the WITH statement of the SQL statement. The following example shows how to define and declare a procedure or function in a WITH statement:

WITH

PROCEDURE | FUNCTION test1 (…)

BEGIN

END

SELECT FROM table_name

/

Although you cannot use the with statement directly in the PL/SQL unit, it can be referenced by a dynamic SQL in the PL/SQL unit.

Extended data type

In 12c, the size of data types such as VARCHAR2, NAVARCHAR2, and RAW expands from 4K and 2K bytes to 32K bytes compared to previous versions. Whenever possible, the size of extended characters reduces the use of the LOB data type. To enable extended character size, you must set the initial database parameter of MAX_STRING_SIZE to EXTENDED.

To use extended character types, you need to perform the following procedures:

1. Shut down the database

two。 Restart the database in upgrade mode

3. Change parameter: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED

4. Execute utl32k.sql as sysdba: SQL > @? / rdbms/admin/utl32k.sql

5. Shut down the database

6. Restart the database in read-write mode

Compared to LOB data types, in ASSM tablespace management, fields of extended data types are stored in SecureFiles LOB, while in non-ASSM tablespace management, they are stored in BasciFiles LOB.

Note: once changed, you can no longer change the settings back to STANDARD.

On the new features of oracle12c which are shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can 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.