DBA Scripts and Articles
Table Enhancements Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application. To create an invisible column: [crayon-5446ba3883090413320732/] You can’t create invisible columns on : External [...]
ADR enhancements In oracle 12c the Automatic Diagnostic Repository contains a new log directory with 2 subdirectories : DDL Debug The DDL log When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log. The DDL logging feature [...]
Emergency Monitoring Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung. Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening. You can access real-time performance data from ASH and access [...]
What’s new ? Oracle 12c introduces a major update called Adaptive Query Optimization which is based on : Adaptive execution plans Adaptive Statistics These two functionnalities are used to improve execution plans by using dynamic statistics gathered during the first part of the SQL execution. This allow to create more efficient plans that those using [...]
What is Real Time Database Operation Monitoring ? Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report. Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR … You can generate Active Reports which are [...]
RMAN Enhancements New Privilege A new SYSBACKUP privilege is created in Oracle 12c, it allows the grantee to perform BACKUP and RECOVERY operations with RMAN SQL in RMAN You can now use SQL Statements in RMAN like you would do in SQL*PLUS : BEFORE : RMAN> SQL “alter system switch logfile”; NOW : RMAN> alter system switch logfile; [...]
User Task-Specific Administrative Privileges To continue with the objective of separating duties and the least privileges, Oracle 12c introduce new administratives privileges all destinated to accomplish specific duties: SYSBACKUP : Used for RMAN operations like BACKUP, RESTORE, RECOVER SYSDG : Used to administer DATAGUARD, In 12c when you use DGMGRL commandline interface your are automatically [...]
Unified Audit Data Trail Unifed Auditing offers a consolidated approach, all the audit data is consolidated in a single place. Unified Auditing consolidate audit records for the following sources : Standard Auditing Fine-grained auditing (DBMS_FGA) RAC security auditing RMAN auditing Database Vault auditing Oracle Label Security auditing Oracle Data Mining Oracle Data Pump Oracle SQL*Loader In [...]
In Database Archiving In Database Archiving is a new feature of Oracle 12c meant to solve management of historical data inside the database. Like its name says, this functionnality leaves data into the database, so it remains accessible if you need it. With In Database Archiving, historical data remains in the database but is invisible to [...]
The post OCP 12C – In Database Archiving and Temporal Validity appeared first on Oracle DBA Scripts and Articles (Montreal).
Information Lifecycle Management Before reading this flashcard I recommend you to read my article on ILM. Automatic Data Optimization (ADO) Policy based data management Let you define policies at : Tablespace level Segment level Row level Policies can either compress data or move data to a different tablespace Policies let you define when, what and [...]
The post OCP 12C – Information Lifecycle Management and Storage Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).
Backup a CDB/PDB To make a database backup you need the SYSBACKUP or SYSDBA privilege. You can backup the CDB and all the PDBs independantly, all together, or by specifying a list. You can backup a PDB by connecting directly to it and use: RMAN> BACKUP DATABASE: You can backup a PDB by connecting to [...]
The post OCP 12C – Backup, Recovery and Flashback for a CDB/PDB appeared first on Oracle DBA Scripts and Articles (Montreal).
Connecting to a CDB or PDB Connecting to a CDB or PDB is not different of connecting to a standard database. When you create a PDB, it is automatically assigned a service which can be used to reach this specific PDB. You can either use the EZ connect syntax, the Oracle Net Service syntax or the [...]
The multitenant architecture Needs enterprise edition and the multitenant option. Consists of a CDB (Container database) and zero, one or up to 252 PDBs (pluggable databases). Has a root container (the CDB itself) and a seed container (template to create PDBs) There is only one instance per CDB. A PDB doesn’t have : background processes [...]
The post OCP 12C – Basics of Multitenant Container Database (CDB) appeared first on Oracle DBA Scripts and Articles (Montreal).
EM Database Express The old Enterprise Manager database control is replaced by Enterprise Manager database express in Oracle 12c. It contains only basic administration capabilities as the advanced ones are included in Enterprise Manager cloud control With EM database Express you can manage Security (Users, roles, profiles), Configuration (Instance Parameters, memory, database features), Storage (tablespaces, [...]
This script will help you find the space reclaimable in your datafiles, it finds the High Water Mark of all your datafiles (the minimum size) and then report the following information: Datafile Size Datafile HWM Percentage of space reclaimable Command to resize the datafile Total space reclaimable in your datafiles Percentage of space reclaimable in your datafiles Find space reclaimable [...]
What is SQL Tuning Health Check? The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs. It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed. The script generates an [...]
Calculate the space used by a single object This script will help you calculate the size of a single object : [crayon-542d77fe88875325905449/] Calculate the space used by a whole schema If you want the space used by a whole schema, then here is a variation of the first query : [crayon-542d77fe88880816189925/]