Drop Database Don’t Clean All

Today I was performing housekeeping, I have drop a database (Version 12.1.0.2), below post the command;

 
SQLPLUS / AS SYSDBA
STARTUP MOUNT RESTRICT
SET LINES 1000 PAGES 1000
REM
REM Make sure which database you want to drop 
REM 
SELECT INSTANCE_NAME, HOST_NAME FROM V$INSTANCE;
DROP DATABASE;

The “drop databae” drop the datafiles, redo, CF, passwd file, etc. but not all, archives are not deleted (backup are not deleted too, you need use the “including backups” with the drop database command), you need drop them by hand, in a way similar to this using asmcmd;

 

 
ASMCMD [+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG] > cd 2017_05_25/
ASMCMD [+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG/2017_05_25] > ls
thread_1_seq_558.323.944877607
thread_1_seq_559.322.944892039
ASMCMD [+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG/2017_05_25] >
ASMCMD [+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG/2017_05_25] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG/2017_05_25] > ls
ASMCMD-8002: entry '2017_05_25' does not exist in directory '+DB_PROD_ARCH_DG/VANTIVE/ARCHIVELOG/'

HTH – Antonio NAVARRO

 

How Set To Broken Jobs From Other Users

Of course, you should not use dbms_job any more, but how many times are we working on an installation or a database that already has them? It would be best to migrate these jobs to dbms_scheduler. In this case I will post a small script to put all jobs to broken, without having to log in as the owner of the same.

 
SET SERVEROUTPUT ON

DECLARE
   UserID        NUMBER;
   Sentence      VARCHAR2 (500);
   Result        PLS_INTEGER;
   HandleCursor  PLS_INTEGER;
   debug         BOOLEAN := FALSE;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

  FOR i IN (SELECT JOB, SCHEMA_USER FROM DBA_JOBS WHERE BROKEN != 'Y') LOOP
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (JOB)         : ' || I.JOB);  end if;
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (SCHEMA_USER) : ' || I.SCHEMA_USER);  end if;

      SELECT USER_ID INTO UserId FROM DBA_USERS WHERE USERNAME = RTRIM (LTRIM (i.SCHEMA_USER));
      Sentence := 'BEGIN DBMS_JOB.BROKEN (' || i.JOB ||', TRUE); END; ';

      HandleCursor := sys.dbms_sys_sql.open_cursor ();
      sys.dbms_sys_sql.parse_as_user (HandleCursor, Sentence, dbms_sql.native, UserId);
      Result := sys.dbms_sys_sql.execute (HandleCursor);
      sys.dbms_sys_sql.close_cursor(HandleCursor);

      DBMS_OUTPUT.PUT_LINE ('Executing :   ' || Sentence);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE (CHR (10) || 'Please, remember execute commit to close the actual transaction...');
END;
/

The trick here is to make the parseo as the owner user.

HTH – Antonio NAVARRO

ORA-01157, ORA-01110 Errors, When Query DBA_TEMP_FILES

Last morning I was migrating a database from filesystem to ASM, when I was quering the dba_temp_files get the next error.

 

 
SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/pludev/pludev_temporal_01.dbf'

Ok, the CF file reference old temporary files on filesystem. You can check this by the next query (I have added news tempfiles on ASM);

 
SQL> select ts#, name from v$tempfile;

       TS# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         3 /pludev/pludev_temporal_01.dbf
         5 /pludev/pludev_temporal_users01.dbf
         3 /pludev/pludev_temporal_02.dbf
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.298.955652617
         5 +PLU_DEV_DAT_DG/pludeV/tempfile/temp_users.299.943353021
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.300.945664717

6 rows selected.

In this case the easy way is drop old tempfiles (in filesystem format) as show in the next script;

 
alter tablespace temp1 drop tempfile '/pludev/pludev_temporal_01.dbf'; 
alter tablespace temp2 drop tempfile '/pludev/pludev_temporal_users01.dbf'; 
alter tablespace temp3 drop tempfile '/pludev/pludev_temporal_02.dbf'; 

After that, if you repeat the query on dba_temp_files;

 
SQL> select file_name from dba_temp_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+PLU_DEV_DAT_DG/pludeV/tempfile/temp.298.955652617
+PLU_DEV_DAT_DG/pludeV/tempfile/temp_users.299.943353021
+PLU_DEV_DAT_DG/pludeV/tempfile/temp.300.945664717

HTH – Antonio NAVARRO

 

How To Find Info About ORA-600

Last morning a coworker ask me about the best way of find documentation for ora-00600 errors. He told me Google (or anothers search engines) is the best option but I don’t agree with him. Of course Google and others are a easy ang faster way of find results but in this case I think the only way (if you have support) is Metalink/MOS. The not you must to use is;

Note 153788.1

It is  a simple web form and maybe the only option you have in this kind of problems.

HTH – Antonio NAVARRO.

 

DBMS_ASSERT Package

Today I’ll like to talk about the dbms_assertion package. It is used in many differents ways. I usually use it for prevent SQL injection, it must to be a best practies for developers.

There are many kinds of SQL injecto, being the most simple case introduce in a windows field the date requested by the app more a little of sql. Suppose that your app required an username, in the usual way of work you type de username

USERNAME: SCOTT

The injection would be (I have assumed a very simple case where the app recive the data in a kind the raw value);

USERNAME: SCOTT UNION ALL SELECT * FROM DBA_USERS

Depend on your app and how it work this can be a serious problem. DBMS_ASSERT package prevent this by “sanitizing” the SQL.

 
Some examples can be like those;

 
REM
REM In this first case, check quotes
REM

WEUP*ANTO> BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL ('DUMMY'));
  3  END;
  4  /
'DUMMY'

Transcurrido: 00:00:00.04
WEUP*ANTO> BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL (''''DUMMY''''));
  3  END;
  4  /
DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL (''''DUMMY''''));
                                                     *
ERROR en línea 2:
ORA-06550: línea 2, columna 54:
PLS-00103: Encountered the symbol "DUMMY" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from using || multiset member
submultiset
The symbol ", was inserted before "DUMMY" to continue.

REM
REM In the nex case check a valid name for an object
REM 

Transcurrido: 00:00:00.08
WEUP*ANTO>  BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.QUALIFIED_SQL_NAME ('SCOTT.TABLE_ONE'));
  3  END;
  4  /
SCOTT.TABLE_ONE

WEUP*ANTO>  BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.QUALIFIED_SQL_NAME ('SCOTT TABLE_ONE'));
  3  END;
  4  /
 BEGIN
*
ERROR en línea 1:
ORA-44004: nombre de SQL cualificado no válido
ORA-06512: en "SYS.DBMS_ASSERT", línea 315
ORA-06512: en línea 2

HTH – Antonio NAVARRO

How To Find Master Node

Sometimes can be interesting to know which is the master node in a cluster, in my case, this is needed becouse the machines of cluster have different hardware, being one more powerfull than others because of this I prefer like the master node the more powerfull machine (more CPUs and memory).

The first rule (and logical) is

The default master is always the first node that is started in the cluster.

The problem is when we have had some reboots and the timeline is not very clearly. In this case you have two ways;

By locating automatic OCR backup

orcconfig -showbackup

It returned some rows, the more recent backup perform by one of nodes is the master.

By searching the string “MASTER” crsd.l* on all nodes:

grep “MASTER” $ORA_CRS_HOME/log/$HOST/crsd/crsd.l*

HTH – Antonio NAVARRO