Oracle new features 4-maintenance, upgrade, recovery and data pump, and oracle New Features

Oracle new features 4-maintenance, upgrade, recovery and data pump, and oracle New Features

1. Contents

1) enhanced table partition maintenance.

2) Database Upgrade and improvement.

3) Restore/restore data files across networks.

4) Data Pump enhancement.

5) Real-time ADDM.

6) collect concurrent statistics.

2. Enhanced table partition Maintenance

In other articles, I have explained how to move a table partition or subpartition to a different tablespace online or offline. In this section, you will learn other enhancements related to table partitions.

2.1. add multiple new partitions.

Before 12c R1, only one partition can be added to a partitioned table at a time. To ADD multiple partitions, you must execute an alter table add partition statement for each new PARTITION. 12c supports the use of an alter table add partition command to ADD multiple new partitions. 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), salnumber (6 ))

Partition by range (sal)

(PARTITION p1 values less than (10000 ),

PARTITION p2 values less than (20000 ),

PARTITION p3 values less than (30000)

);

Next let's add several new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION

PARTITION p4 values less than (35000 ),

PARTITION p5 values less than (40000 );

Similarly, you can add multiple new partitions to the list and system partition tables, provided that there are no maxvalue partitions on these partition tables.

2.2. If multiple partitions or subpartitions are deleted and truncated

As part of data maintenance, you can delete or cut partitions in a partition table. Before 12c R1, only one partition of the partition table can be deleted or truncated at a time. In 12c, you can use the alter table table_name {DROP | TRUNCATE} PARTITIONS command to delete or TRUNCATE multiple PARTITIONS or subpartitions. The following example shows how to delete or intercept multiple partitions in a partition table:

SQL> ALTER TABLEemp_part DROP PARTITIONS p4, p5;

SQL> ALTER TABLEemp_part TRUNCATE PARTITONS p4, p5;

To maintain indexes at the same timeUpdate indexes or UPDATE GLOBAL INDEXESClause,

As follows:

SQL> ALTERTABLE emp_part DROP PARTITIONS p4, p5 UPDATE GLOBAL INDEXES;

SQL> ALTERTABLE emp_part TRUNCATE PARTITIONS p4, p5 UPDATE GLOBAL INDEXES;

If you do not include the update global indexes clause when deleting or truncating a partition, You can query ORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary view to check whether the index contains obsolete index items.

2.3 split a partition into multiple new partitions

In 12c, the newly enhanced SPLITPARTITION clause allows you to use a command to split a specific partition or subpartition into multiple new partitions:

SQL> CREATE TABLE emp_part

(Eno number (8), enamevarchar2 (40), sal number (6 ))

Partition by range (sal)

(PARTITION p1 values less than (10000 ),

PARTITION p2 values less than (20000 ),

PARTITION p_max values lessthan (MAXVALUE)

);

 

SQL> ALTER TABLE emp_part SPLIT PARTITION p_max

(PARTITION p3 values less than (25000 ),

PARTITION p4 values less than (30000), PARTITION p_max );

2.4. Merge multiple partitions into one partition

You can useALTER TBALE MERGE PARTITIONSStatement to merge multiple partitions into one partition:

SQL> CREATETABLE emp_part

(Eno number (8), ename varchar2 (40), salnumber (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> ALTERTABLE emp_part MERGE PARTITIONS p3, p4, p5 INTO PARTITION p_merge;

If these partitions contain the partition key value range, you can also use the following command:

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

3. Database Upgrade and Improvement

Whenever a new version is released, each DBA immediately faces an upgrade. This section describes the two new upgrades to 12c.

3.1. Pre-upgrade script

In 12c R1, a new pre-upgrade information script preupgrd. SQL is greatly improved, replacing the previous utlu [121] s. SQL script. In addition to checking and verifying before the upgrade, the script can also solve various problems before and after the upgrade by using the fixup script. The generated fixup script can be executed to solve different levels of problems. For example, before and after the upgrade. When you manually upgrade the database, the script must be manually run before the actual Database Upgrade starts. However, when you use the DBUA tool to upgrade the database, it will be used as part of the upgrade process to automatically run the pre-upgrade script. When any error occurs, you will be prompted to run the fixup script. The following example shows how to run the script:

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

The above script will generate a log file and[Pre/post] upgrade_fixup. SQLScript. All these files are stored in$ ORACLE_BASE/logging toollogsDirectory. Before you continue to upgrade, you should check the log and run the operations and scripts recommended in the log to solve any problems.

Note: Make sure that youPreupgrd. SQLAndUtluppkg. SQLThe scripts are all copied from the home/rdbms/admin directory of Oracle in 12c to the current database/rdbms/admin location.

3.2. Parallel update

The Database Upgrade time is proportional to the number of database configuration components, rather than the database size. In earlier versions, no options are available directly or indirectly to complete the upgrade process in parallel.

Catctl in 12c R1. pl (parallel upgrade function) replaces the previous catupgrd. SQL script. The script in 12c R1 has an option for parallel upgrade, which can shorten the time required to complete the upgrade process. The following process describes how to enable the parallel (three processes) upgrade function, start the database in upgrade mode, and then run the following command:

Cd $ ORACLE_12_HOME/perl/bin

$./Perl catctl. pl-n 3-catupgrd. SQL

When the database is manually upgraded, the above two steps need to be run explicitly. However, the DBUA tool contains the preceding two changes.

4. Restore/restore data files through the network

In 12c R1, another major enhancement is that you can now restore or restore data files, control files, spfiles, tablespace or entire database. This is very useful when synchronizing data between the master and slave databases.

When a long latency (gap) is found between the master and slave databases, you no longer need a complicated rollback process to fill in the latency between the master and slave databases. RMAN can obtain incremental backups through the network and apply these backups to the physical standby database to restore the standby database. As you have already said, you can use the service name to directly copy the required data files from the slave database to the master database. For example, a data file or tablespace in the master database is lost, or the data file is not restored from the backup set.

The following process describes how to use the new feature to execute the rollback to synchronize the master and slave databases:

Physical standby database:

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

RMAN> recoverdatabase from service primary_db_tns using compressed backupset;

The above example uses the connection string primary_db_tns on the slave database to connect to the master database to perform an incremental backup. Then, these incremental backups are transmitted to the slave database and these files are applied to synchronize the slave database. However, you must be sure that the connection string primary_db_tns pointing to the master database has been configured on the slave database.

In the following example, I will use the slave database to retrieve and restore the lost data files in the master database to illustrate a scenario:

Master database:

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

RMAN> RESTOREDATAFILE '+ DG_DISKGROUP/DBANME/DATAFILE/filename' from service standby_db_tns;

5. Data Pump Enhancement

This section mainly introduces the Data Pump enhancement. There are many useful enhancements, such as the attempt to convert data to tables during export and the shutdown of logs during import.

5.1. Disable log generation

The newly introduced TRANSFORM option for Data Pump import supports no object redo during the import process. When the value of the TRANSFORM option is set to DISABLE_ARCHIVE_LOGGING, no redo is generated for objects in the context during the entire import process. When importing a large table, this feature can greatly reduce system pressure and generate redo operations, thus slowing down the import process. This feature can be applied to tables and indexes. The following example shows the features:

$./Impdp directory = dpump dumpfile = abcd. dmp logfile = abcd. logTRANSFORM = DISABLE_ARCHIVE_LOGGING: Y

5.2. convert a view to a table

This is another enhancement of the Data Pump. Using the VIEWS_AS_TABLES option, you can export a view as a table. The following example describes how to export a view as a table during the export process:

$./Impdp directory = dpump dumpfile = abcd. dmp logfile = abcd. logTRANSFORM = DISABLE_ARCHIVE_LOGGING: Y

6. Real-time ADDM Analysis

Analyzing past and current database health status using tools such as AWR, ASH, and ADDM is part of every DBA's life. Although each tool can measure the overall health and performance of the database at all levels, each tool can be used when the entire database does not respond or "hang.

When you encounter a database that does not respond or is hang, if you have configured the Oracle Enterprise Manager 12c cloud control, you can diagnose serious performance problems. This will provide you with an overall description of what is happening in the database, and may also provide you with a remedy to solve the problem.

The following describes how to use the Oracle Enterprise Manager cloud control to analyze the database status step by step:

1) on the Access Database homepage, select the EmergencyMonitoring option from the Performance menu, which will display the blocking session at the top of the hang analysis table.

2) Select Real-TimeADDM from Performance to perform Real-time ADDM analysis.

3) after collecting performance data, click the Findings page to get a summary of all the interactions found.

7. Collect statistics of multiple tables in parallel

In earlier Oracle versions, Oracle collects a table at a time no matter when you run the DBMS_STATS process to collect statistics on tables, indexes, modes, or databases. If the table is large, we recommend that you increase the degree of parallelism. In 12c R1, you can collect statistics for multiple tables, partitions, and subpartitions at the same time. When using this feature, you must set the following settings at the database level 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 ('constream', 'all ');

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('Scott ');

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.