How To Trace DDL Sentences

Last day someone drop a import table in a database. The app get crazy and nothing work fine. The solution was recreate the table but the problem was who deleted it?.

In this case there weren’t any audit. You can activate audit (it is powerfull) but in this case I want talk about trace only DDL SQL (Data Definition Language) by using the enable_ddl_logging parameter.

 

How to check;

BBDD*ANR> show parameter enable_ddl_logging

NAME                                 TYPE                             VALUE
____________________________________ ________________________________ _________________________
enable_ddl_logging                   boolean                          FALSE

How to enable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

Hot to disable;

ALTER SYSTEM SET ENABLE_DDL_LOGGING=FALSE;

The audit will be write to (version 12c) a file with format show below into the diagnostic_dest;

ddl_${ORACLE_SID}.log

 

HTH – Antonio NAVARRO

RMAN-11003 ORA-32001 Errors When Duplicate Database

Last day I was performing a duplicate from production enviroment to developmente enviroment, when I received the next error;

 
channel: aux3 released channel: aux4 released channel: aux5 released channel: aux6 released channel: aux7 released channel: aux8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/11/2017 17:09:24
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 09/11/2017 17:09:24
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_name =  'SAPP' comment= 'Modified by RMAN duplicate' scope=spfile
ORA-32001: write to SPFILE requested but no SPFILE is in use

In this case the problem was the SPFILE, like the error show. I remove the previus clone of database before to duplicate again, but when I removed the previous image it not remove the service,
It is a RAC and I need remove from CRS. Remove the service and repeat the duplicate command again solve the problem.

HTH – Antonio NAVARRO

 

Copy Password File From ASM To Filesystem

In Oracle 121, password file can be store into the ASM. I think it is a good idea but the problem is when you want to copy or reply to another system. You need perform a copy of this file. In this case I goint to show the pwcopy command from asmcmd. Using pwcopy you can perform a copy of a password file, for asm or database instance, to another path into the asm or out to the filesystem.

Please look the below example;

 
ASMCMD [+DB12C_PROD_DATOS_DG/db12c/PASSWORD] > pwcopy pwddb12c.299.956724623 /export/home/grid2/pwdfile_anr
copying +DB12C_PROD_DATOS_DG/db12c/PASSWORD/pwdwebp.299.956724623 -> /export/home/grid2/pwdfile_anr
ASMCMD [+DB12C_PROD_DATOS_DG/db12c/PASSWORD] > exit
grid2@/export/home/grid2 $ ls -rlt
total 125574
drwxr-xr-x   2 grid2     oinstall       3 Feb  1  2017 Opatch
drwxr-xr-x   3 grid2     oinstall       5 Feb  2  2017 PSU
drwxr-xr-x   2 grid2     oinstall       8 Jun 14 09:00 admin
-rw-r-----   1 grid2     oinstall    7680 Aug 31 09:14 pwdfile_anr

In this case I performed a copy of a password file to the file system.

HTH – Antonio NAVARRO