Infrastructure at your Service

Oracle 12c Archives - Page 2 of 27 - Blog dbi services

Michael Hein

Naming of archivelog files with non existing top level archivelog directory

By | Database Administration & Monitoring, Database management, Oracle | No Comments

In Oracle 12.2 an archive log directory is accepted, if top level directory does not exist: oracle@localhost:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/ [DMK] ls -l /u02/oradata/DMK/ total 2267920 drwxr-xr-x. 2 oracle dba        96 Dec  6 05:36 arch … Now database accepts this non existing archivelog destination: SQL> alter system set log_archive_dest_3=’LOCATION=/u02/oradata/DMK/arch/arch2′; System altered. But not this: SQL> alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4′; alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4′ * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid…

 
Read More
Franck Pachot

12c Multitenant Internals: compiling system package from PDB

By | Oracle | No Comments

When I explain the multitenant internals, I show that all metadata about system procedures and packages are stored only in CDB$ROOT and are accessed from the PDBs through metadata links. I take an example with DBMS_SYSTEM that has nothing in SOURCE$ of the PDB. But I show that we can compile it from the PDB. This is my way to prove that the session can access the system objects, internally switching the session to the…

 
Read More
Franck Pachot

Dynamic Sampling vs. Extended Statistics

By | Oracle | 2 Comments

On datawarehouse databases, I frequently recommend increasing the level of dynamic sampling because: Queries have complex predicates with AND, OR, IN(), ranges and correlated values for which the optimizer cannot estimate the cardinality properly Queries are long anyway (compared to OLTP) and can afford more parse time to get an optimized execution plan However, there’s a drawback with this approach because sometimes the dynamic sampling estimation may give bad estimations, and supersedes the static statistics…

 
Read More
Franck Pachot

Multitenant internals: INT$ and INT$INT$ views

By | Oracle | No Comments

This month, I’ll talk – with lot of demos – about multitenant internals at DOAG conference. The multitenant dictionary architecture starts with a simple idea: system metadata and data are in CDB$ROOT and user metadata and data are in PDB. And it could have been that simple. When a session connected to a PDB needs to read some system information, the session context is switched to the CDB$ROOT container and reads from CDB$ROOT SYS tablespace…

 
Read More
Franck Pachot

Wrong result with multitenant, dba_contraints and current_schema

By | Oracle | No Comments

Multitenant architecture is not such a big change and this is why I recommend it when you start a project in 12c or if you upgrade to 12.2 – of course after thoroughly testing your application. However, there is a point where you may encounter problems on dictionary queries, because it is really a big change internally. The dictionary separation has several side effects. You should test carefully the queries you do on the dictionary…

 
Read More
Franck Pachot

Active Data Guard services in Multitenant

By | Oracle | No Comments

A database (or the CDB in multitenant) registers its name as the default service. When a standby database is on the same server, or same cluster, you have no problem because this database name is the db_unique_name which is different between the primary and the standby(s). In multitenant, in addition to that, each PDB registers its name as a service. But the PDB name is the same in the primary and the standby database. This…

 
Read More