Summary of the new features of Oracle 12c on business impact (1)

Summary of the new features of Oracle 12c on business impact (1)

1. invisible fields

In Oracle 11g R1, Oracle introduces some good enhancement features in the form of invisible indexes and virtual fields. Inherit and develop the former, and introduce the invisible field idea in Oracle 12c R1. In earlier versions, to hide important data fields to avoid displaying them in common queries, we often 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, this field will not appear in a common query, unless explicitly mentioned in an SQL statement or condition, or DESCRIBED is included 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 fields and partition fields can also be defined as invisible types. However, temporary tables, external tables, and cluster tables do not support invisible fields.

2. DDL logs

In earlier versions, there are no optional methods for logging DDL operations. In 12c R1, you can write DDL operations into xml and log files. This is useful for understanding who has executed the create or drop command at what time. To enable this function, you must configure the initial parameters of 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 in the $ ORACLE_BASE/diag/rdbms/DBNAME/log | ddl path. An xml file contains DDL commands, IP addresses, timestamps, and other information. This helps 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 xml or log files:

• CREATE | ALTER | DROP | TRUNCATE TABLE

• DROP USER

• CREATE | ALTER | drop package | FUNCTION | VIEW | SYNONYM | SEQUENC

3. 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 directly execute any SQL and PL/SQL commands from RMAN. The following is an example of SQL statement execution in RMAN:

 
 

  
  
RMAN> SELECT username,machine FROM v$session;  
  
  
RMAN> ALTER TABLESPACE users ADD DATAFILE SIZE 121m; 
 
 

4. Table recovery and partition recovery in RMAN

Oracle Database Backup is divided into two types: logical and physical backup. Each Backup Type has its own advantages and disadvantages. In earlier versions, using existing physical backup to restore tables or partitions is not feasible. Logical backup is required to restore a specific object. For 12c R1, you can recover a specific table or partition from RMAN backup to a certain time point or SCN in the case of drop or truncate.

When a table or partition is restored through RMAN, the general process is as follows:

• Are you sure you want to restore the backup set required for the table or partition?

• When a table or partition is restored, a secondary database is temporarily set to a certain time point.

• Use the Data Pump to export the required table or partition to a dumpfile

• You can import tables or partitions from the source database (optional)

• Rename during recovery

The following example shows how to restore a table by using RMAN (Make sure that you have backed up the database earlier ):

 
 

  
  
RMAN> connect target "username/password as SYSBACKUP ";
  
  
RMAN> recover table username. tablename until time 'timestamp... '
  
  
Auxiliary destination '/u01/tablerecovery'
  
  
Datapump destination '/u01/dpump'
  
  
Dump file 'tablename. dmp'
  
  
NOTABLEIMPORT -- this option avoids importing the table automatically. (this option avoids automatic table import)
  
  
Remap table 'username. tablename': 'username. new_table_name '; -- can rename table with this option. (this option can be renamed)
 
 

Important:

• Make sure that the auxiliary database has sufficient available space in the/u01 file system, and the Data Pump files are also guaranteed.

• A complete database backup or at least a SYSTEM-related tablespace backup must exist.

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

• SYS User table or partition cannot be recovered

• Tables and partitions stored in the SYSAUX and SYSTEM tablespaces cannot be recovered.

• When the REMAP option is used to restore a table that contains the not null constraint, it is NOT feasible to restore the table.

5. Limit the PGA size

Before Oracle 12c R1, there is no option to limit and control the PGA size. Although you set an initial parameter of PGA_AGGREGATE_TARGET, Oracle dynamically increases or decreases the PGA size based on the workload and requirements. In 12c, you can enable automatic PGA management to set Hard Limits for PGA. You need to set the PGA_AGGREGATE_LIMIT parameter. Therefore, you can set a new parameter to set a rigid limit on the PGA to avoid excessive use of the PGA.

 
 

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

Important:

When the current PGA limit is exceeded, Oracle will automatically terminate/abort sessions or processes to maintain the most appropriate PGA memory.

6. Enhanced table partition Maintenance

In the first part, I explained how to migrate a table partition or subpartition to a different tablespace online or offline. This article describes other improvements to Table Partitioning.

Add multiple new partitions

Before Oracle 12c R1, only one new partition can be added to an existing partition table at a time. To ADD more than one new PARTITION, run the alter table add partition statement separately for each new PARTITION. Oracle 12c only needs to use a separate alter table add partition command to ADD multiple new partitions, which increases the flexibility of the database. 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 intercept multiple partitions/subpartitions

As part of data maintenance, DBA usually deletes or truncates partitions in a partition table. Before 12c R1, an existing Partition Table can only be deleted or truncated once. For Oracle 12c, you can use the single alter table table_name {DROP | TRUNCATE} PARTITIONS command to undo or merge multiple PARTITIONS and subpartitions.

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

 
 

  
  
SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;  
  
  
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5; 
 
 

To keep the index updated, use the update indexes or update global indexes statement 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 in the USER_INDEXES or USER_IND_PARTITIONS dictionary view to check whether any index contains any expired entries.

Separate a single partition into multiple new partitions

The new split partition statement in 12c allows you to separate a specific PARTITION or subpartition into multiple new partitions using only one separate command. The following example shows how to split 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 one 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 range forms a sequence, you can use the following example:

 
 

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

7. Database Upgrade and Improvement

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

Pre-upgrade script

In 12c R1, the original utlu [121] s. SQL script is replaced by a greatly improved pre-upgrade information script preupgrd. SQL. In addition to pre-upgrade check and verification, this script can also fix various problems before and after the upgrade process in the form of a script.

You can execute the generated repair script to solve different levels of problems, such as pre-upgrade and post-upgrade problems. When you manually upgrade the database, the script must be manually executed before the actual upgrade process is initialized. However, when you use the DBUA tool for Database Upgrade, it will automatically execute the pre-upgrade script as part of the upgrade process, and will prompt you to execute the repair script to prevent errors.

How to execute the script:

 
 

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

The preceding script generates a log file and a [pre/post] upgrade_fixup. SQL script. All these files are located in the $ ORACLE_BASE/export toollogs directory. Before you continue the real upgrade process, you should browse the suggestions mentioned in the log file and execute the script to fix the problem.

Note: Make sure that you 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 duration of Database Upgrade depends on the number of components configured on the database, rather than the size of the database. In earlier versions, we were unable to run the Upgrade Program in parallel to quickly complete the upgrade process.

In 12c R1, the original catupgrd. SQL script is replaced by catctl. pl script (parallel upgrade function). Now we can run the Upgrade Program in parallel mode.

The following describes how to initialize the parallel upgrade function (three processes). 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 must be run when you manually upgrade the database. DBUA inherits these two new changes.

8. Restore data files through the network

Another important enhancement in 12c R1 is that, you can use a service name between the primary database and the standby database to obtain or restore data files, control files, parameter files, tablespace, or the entire database. This is extremely useful for synchronizing the primary and standby databases.

When there is a big difference between the primary database and the standby database, you no longer need a complicated roll-forward process to fill in the difference between them. RMAN can perform backup recovery over the network for Incremental backup and apply them to the physical backup database. You can use the service name to directly copy the required data files from the backup point to the master site. This is to prevent the loss of data files and tablespaces on the master database, or to prevent the data files from being actually recovered from the backup set.

The following process demonstrates how to use this new function to execute a forward rolling to synchronize the standby database and the primary database:

On the physical backup 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 backup database to connect to the master database, then executes an incremental backup, and then transmits the Incremental backup to the backup destination, then apply these files to the standby database for synchronization. However, you need to ensure that primary_db_tns has been configured, that is, it is directed to the primary database on the backup database side.

In the following example, we will demonstrate how to restore lost data files in the primary database by obtaining 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;