ORA-27102 When starting Database On Solaris

Last morning I got the next error when I was restarting a database;

 
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 6291456000

Oracle try to allocate all memory in an unique chunck but there is not a chunck of this size avaliable. Operating system report this error to Oracle and Oracle show it us;

I have red about it is a bug (20635316) in ML/MOS. You have like workaround the hidden parameter

_use_osm = false

Of course, it is a hidden parameter and you must be carefull with it. Don’t set up in production enviroment.

HTH – Antonio NAVARRO

Advertisements

How To Drop A RAC Database (12c)

In this sample I like show you how to delete a database in RAC mode, it is a little different from standalone.

We need change de cluster database parameter;

show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

exit

Stop the database in all nodes, in this case I have two, you can only have one instance active to drop database, in other case you will get an error;

srvctl stop database -d DB121

Mount in restrict the database like previous step to the delete;

sqlplus / as sysdba
startup mount exclusive restrict

-- Make sure you are deleting the correct database
select instance_name from gv$instance;
SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
DB121_1

drop database;
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

exit

After that remove database from the clusterware;

srvctl remove database -db DB121
Remove the database DB121? (y/[n]) y

Like final step, you must to cleanup other files, manually, like archives, password file,….

HTH – Antonio NAVARRO

I Cannot Delete Into ASMCMD

A coworker ask me about a problem he had when trying delete into the asmcmd. At shell prompt he can deleted without problems but from asmcmd doesn’t. He get the control and interrogation symbol like show below;

 
grid@mortir./grid/home/grid $ asmcmd -p                   
ASMCMD [+] > lsdg dddssss^?^?^?^?                                                                                                                  
ASMCMD-8001: diskgroup 'ddd' does not exist or is not mounted
ASMCMD [+] > exit                                            

The problem here is that the erase is not set correct. He needed to define the stty erase for the backspace key.

 
grid@mortir./grid/home/grid $
grid@mortir./grid/home/grid $ stty erase ^?

HTH – Antonio NAVARRO

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.