Infrastructure at your Service

Category Archives: Database management

Franck Pachot

12cR2 Application Containers and Foreign Keys

By | Database management, Oracle | 2 Comments

Application Container brings a new way to share data among databases, and adds a new dimension to referential integrity. A foreign key in an application PDB can reference a row belonging to a root data link table. But then, should a delete on the root validate that there are no orpheans in the application PDBs? And what if those PDBs are closed at the time of this delete? Here is a small example.  

 
Read More
Olivier Toussaint

DBSAT un outil pour la sécurité de vos bases de données Oracle

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

Qu’est-ce que DBSAT ? C’est un outil gratuit d’Oracle que vous pouvez télécharger sur My Oracle Support avec comme référence : Doc Id 2138254.1. Il a pour but d’évaluer la sécurité de vos bases de données Oracle en analysant la configuration et les stratégies de sécurité mise en place afin de découvrir les risques liés à la sécurité. Comment cela fonctionne t-il ? Dans un premier temps il sera nécessaire de collecter les informations de…

 
Read More
William Sescu

Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF)

By | Database Administration & Monitoring, Database management | 7 Comments

The listener log file contains a lot of very useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client  and many many more. 05-JUN-2017 12:36:19 * service_update * DBIT122 * 0 05-JUN-2017 12:36:19 * (CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(CID=(PROGRAM=sqlplus@dbidg01)(HOST=dbidg01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.201)(PORT=42619)) * establish * DBIT122_SITE1_DGMGRL * 0 05-JUN-2017 12:36:19 * (CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(CID=(PROGRAM=sqlplus@dbidg01)(HOST=dbidg01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.201)(PORT=42620)) * establish * DBIT122_SITE1_DGMGRL * 0 However,…

 
Read More
Franck Pachot

12cR2 auditing all users with a role granted

By | Database management | 2 Comments

12.1 introduced Unified Auditing where you define policies and then enable them. As with the traditional audit, you enable them for all users or for specific users. The unified auditing adds a syntax to audit all users except some listed ones. 12.2 adds a syntax to audit a group of users, defined by the role granted. This is the best way to enable a policy for a group of users, including those created later.  

 
Read More
Franck Pachot

Which privilege for CREATE PLUGGABLE DATABASE from DB LINK?

By | Database management | One Comment

When cloning a PDB from a remote CDB you need to define a database link to be used in the CREATE PLUGGABLE DATABASE … FROM …@… command. The documentation is not completely clear about the privileges required on the source for the user defined in the database link, so here are the different possibilities. Remote clone Here is what the documentation says: So you can connect to the CDB or to the PDB. In order…

 
Read More
Franck Pachot

12cR2 needs to connect with password for Cross-PDB DML

By | Database management | No Comments

In a previous post, I explained that Cross-PDB DML, executing an update/delete/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username/password; logon denied This blog post also explains a consequence of this implementation, the big inconsistency of CONTAINERS() function because the implementation is completely different for queries (select) and for…

 
Read More