Infrastructure at your Service

Category Archives: Database management

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
Clemens Bleile

Workaround for bug 19566227/20563128 doing Cross Platform Migration (MOS Note 1389592.1)

By | Database Administration & Monitoring, Database management | One Comment

In a project I have to move an Oracle 11.2.0.4-DB (around 7TB in size) from Sparc Solaris (called Source in the remainder of this Blog) to Linux x86-64 (called Target in the remainder of the Blog). I.e. a platform migration from Big Endian to Little Endian. A good method to do that is described in My Oracle Support Note 1389592.1: 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup Basically I copy the…

 
Read More
Franck Pachot

random “ORA-01017: invalid username/password” in 12cR2

By | Database management | 4 Comments

Since 12cR2 is out, we give our 12c new feature workshop with hands-on exercises on 12.1 and 12.2 releases. When I gave it last month, I had a small problem when doing demos: sometimes the connections as sysdba failed with “ORA-01017: invalid username/password”. It was at random, about one every 5 login attempts and I was sure that the password did not change. As I give another of this training next week, I tried to…

 
Read More
Franck Pachot

12cR2 Cross-container DML – insert into container()

By | Database management | No Comments

Multitenant has been introduced in 12.1.0.1 with the goal to share resources but isolate data. However, having all PDBs in the same root may be convenient to manipulate data in multiple PDBs. In the first patchset, 12.1.0.2, a way to query cross-container has been introduced for the CDB administrator to see data in other containers. In the second release, 12.2.0.1, this goes further with the introduction of Application Containers and cross-PDB DML. Currently, not all…

 
Read More
Joël Cattin

Oracle 12cR2 : Optimizer Statistics Advisor

By | Database Administration & Monitoring, Database management, Development & Performance | No Comments

The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2. The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics to the CBO. This Advisor is also able to generate remediation scripts to apply the statistics gathering “best practices”. The…

 
Read More