New feature 3 of Oracle12c-enhanced management functions and new features of oracle12c

New feature 3 of Oracle12c-enhanced management functions and new features of oracle12c

1. Online renaming and migration of data files

In Oracle12cR1, migration or renaming of data files does not require a series of steps. For example, you need to change the tablespace to read-only mode and then perform operations such as data file logic. In 12cR1, data files can be easily completed online using the SQL statement "ALTERDATABASE MOVE DATAFILE. During the process of moving data files, you can execute query, DML, and DDL tasks. In addition, data files can be migrated between storage, for example, from non-ASM to ASM, and vice versa.

1.1. Rename the data file:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u01/data/users_02.dbf ';

1.2. migrate data files from non-ASM storage to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+ DG_DATA ';

Data files are migrated from one ASM disk to another:

SQL> ALTER DATABASE MOVE DATAFILE '+ DG_DATA/users_01.dbf' TO '+ DG_DATA_02 ';

1.3. If the data file exists in the new location, it overwrites the data file with the same name:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '/u02/data_new/users_01.dbf' REUSE;

1.4. copy the data file to the new location, and retain the old copy at the old location:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

By querying the dynamic view v $ session_longops, You can monitor the process of moving data files. In addition, you can also refer to the database's alert. log, because Oracle will write the details of ongoing operations into this log.

2. Online migration of table partitions or subpartitions

In Oracle12c, migrating table partitions or subpartitions to a different tablespace does not require a complex process. Similar to migrating a non-Partition Table online in earlier versions, table partitions or subpartitions can be migrated online or offline to a different tablespace. When the ONLINE clause is determined, all DML operations on partitions or subpartitions related to the migration process will not be interrupted. On the contrary, if a partition or subpartition is migrated offline, all DML operations are not allowed.

The following is an example:

SQL> ALTER TABLE table_name MOVE PARTITION | SUBPARTITIONpartition_name TO tablespacetablespace_name;

SQL> ALTER TABLE table_name MOVE PARTITION | SUBPARTITIONpartition_name TO tablespacetablespace_name UPDATE INDEXES ONLINE;

The first example is to migrate table partitions or subpartitions offline to a new tablespace. The second example is to migrate table partitions or subpartitions online, and maintain both local and global indexes on the table. In addition, DML operations are not interrupted when the ONLINE clause is determined.

Note:

1) The UPDATEINDEXES clause will avoid the unavailability of local and global indexes on the table.

2) Restrictions on online table migration are also used here.

3) locks will be applied during the migration process, which may lead to lower performance and generate a large number of redo operations. In this way, the size of the partition or sub-partition is determined.

3. Invisible Columns

In Oracle 11g R1, Oracle introduced several enhancements in terms of invisible indexes and virtual columns. To further develop these features, Oracle12cR1 introduces the concept of not seeing columns. Remember, in previous versions, to hide important data columns in a common query, we often create a view to hide necessary information or apply some security conditions.

In 12c R1, you can create an invisible column in the table. When a column is defined as invisible, this column does not appear in normal queries unless it is explicitly referenced in SQL statements or conditions, or DESCRIBED in the table definition. It is very easy to add or modify an invisible column, and vice versa.

SQL> CREATE TABLE emp (eno number (6), ename namevarchar2 (40), sal number (9) INVISIBLE );

SQL> ALTER TABLE emp MODIFY (sal visible );

To insert data into an invisible column, you must explicitly reference it. A virtual column or partition column can also be defined as an invisible column. However, temporary tables, external tables, and cluster tables do not support invisible columns.

4. Create multiple indexes on the same column

Before Oracle 12c, Multiple indexes cannot be created in the same column or in the same group of columns. For example, if you have an index in column (a) or column (a, B), you cannot create another index in the same column or group of columns in the same order. In 12c, you can create multiple indexes in the same column or in the same group of columns, as long as the index type is different. However, at any time, only one index is available or visible. To test the invisible index, you must set the parameter optimizer_use_use_invisible_indexes = true. The following is an example:

SQL> CREATEINDEX emp_ind1 ON EMP (ENO, ENAME );

SQL> CREATEBITMAP INDEX emp_ind2 ON EMP (ENO, ENAME) INVISIBLE;

5. DDL logs

In earlier versions, no Command Options for recording DDL operations were available. In 12cR1, you can record DDL operations into xml and log files. This is useful when you want to know who has executed the delete and create commands. To enable this feature, you must configure the initialization parameter ENABLE_DDL_LOGGING, which can be set at the database and session level. When this parameter is enabled, all DDL commands are recorded in xml and log files under $ ORACLE_BASE/diag/rdbms/DBNAME/log | ddl. Each xml file contains information such as DDL commands, IP addresses, and timestamps. This helps identify when a user, table, or DDL statement is deleted.

5.1. To enable DDL logs:

SQL> ALTERSYSTEM | SESSION SET ENABLE_DDL_LOGGING = TRUE;

5.2. The following DDL statements may be recorded in xml and log files:

1) CREATE | ALTER | DROP | TRUNCATETABLE

2) DROPUSER

3) CREATE | ALTER | DROPPACKAGE | FUNCTION | VIEW | SYNONYM | SEQUENCE

6. Temporary Undo

Each Oracle database contains a set of SYSTEM-related tablespaces, such as SYSTEM, SYSAUX, UNDO & TEMP. Each tablespace in an Oracle database has different functions. Before Oracle12cR1, the undo records generated by temporary tables are stored in the undo tablespace, which is similar to the undo records of normal or permanent tables. However, in 12cR1, temporary undo records can now be stored in temporary tables rather than in undo tablespaces. This includes reducing the usage of the undo tablespace and reducing the generation of redo data, because the information is not recorded in the redo log. You can enable the temporary undo option at the session or database level.

6.1. enable temporary undo

To use this new feature, you need to make the following settings:

1) The Compatibility parameter must be set to 12.0.0 or higher.

2) Enable the TEMP_UNDO_ENABLED initialization parameter.

3) because the temporary undo records are stored in the temporary tablespace, make sure that the temporary tablespace has enough space.

4) You can use this command to enable the temporary undo feature at the session level: altersession set TEMP_UNDO_ENABLE = TRUE;

6.2. query temporary undo Information

The dictionary view listed below is used to browse or query statistics about temporary undo data:

1) V $ TEMPUNDOSTAT

2) DBA_HIST_UNDOSTAT

3) V $ UNDOSTAT

6.3. to disable this feature, you only need to make the following settings:

SQL> ALTER SYSTEM | SESSION SET TEMP_UNDO_ENABLED = FALSE;

7. Backup User Permissions

In 11g R2, SYSASM permissions are introduced to perform specific ASM operations. Similarly, in 12c, The SYSBACKUP permission for specific backup and recovery operations is introduced to facilitate backup and recovery operations in RMAN. Therefore, you can create a local user in the database without the SYSDBA permission. Instead, you only need to grant the SYSBACKUP permission to perform backup and recovery tasks in RMAN.

$./Rman target "username/password asSYSBACKUP"

8. How to execute SQL statements in RMAN

In 12c, You can execute any SQL and PL/SQL commands without the SQL prefix in RMAN. You can directly execute any SQL and PL/SQL commands in RMAN. How can I execute SQL statements in RMAN?

RMAN> SELECT username, machine FROM v $ session;

RMAN> alter tablespace users add datafile size 500 m;

9. Restore tables and partitions in RMAN

Oracle Database Backup is divided into two types: logical and physical. Each Backup Type has its own advantages and disadvantages. In earlier versions, it is not appropriate to use an existing physical backup to restore a table or partition. To restore a specific object, you must have a logical backup. In 12cR1, after accidental deletion or truncation, you can restore a specific table or partition to a time point or SCN from the RMAN backup.

9.1. When you start to restore a table or partition through RMAN, perform the following operations:

1) Prepare a backup set required to restore a table or partition.

2) during table or partition recovery, You need to temporarily configure a secondary database to a certain time point.

3) You need to use a Data Pump to export the required tables or partitions into dumpfile.

4) (optional) You can import tables or partitions to the source database.

5) You can rename it when restoring.

9.2. Example of restoring a table at a time point using RMAN (make sure you have an earlier full-Database Backup ):

RMAN> connect target "username/password as SYSBACKUP ";

RMAN> recover table username. tablenameUNTIL TIME 'timestamp... '

AUXILIARYDESTINATION '/u01/tablerecovery'

DATAPUMPDESTINATION '/u01/dpump'

DUMPFILE 'tablename. dmp'

NOTABLEIMPORT -- this option prevents automatic table import.

REMAPTABLE 'username. tablename': 'username. new_table_name '; -- this option can be used to rename a table.

9.3. Note:

1) Make sure there is sufficient free space in the/u01 file system to store the auxiliary database and data pump files.

2) full database backup must exist, or at least SYSTEM-related tablespaces exist.

The following restrictions apply to RMAN recovery tables or partitions:

1) SYS user tables or partitions cannot be restored.

2) tables or partitions stored in the SYSAUX and SYSTEM tablespaces cannot be recovered.

3) You cannot use the REMAP option to restore a table that contains the not null constraint.

10. Maximum PGA size

Before Oracle12c R1, there is no option to limit and control the PGA size. Although you can set PGA_AGGREGATE_TARGET to a specific value, Oracle can dynamically increase or decrease the PGA Size Based on load and demand. In 12c, you can enable automatic PGA management to set a hard limit for PGA. To enable automatic PGA management, you must set the PGA_AGGREGATE_LIMIT parameter. Therefore, you can now set this new parameter to set the hard limit of PGA to avoid excessive use of PGA:

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 2G;

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

Note:

When the current PGA limit is exceeded, Oracle will automatically terminate sessions holding the maximum amount of PGA memory that cannot be maintained.

 

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