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

Clean Up A Obsolete Thread

Today I have been working with a new database, it is an upgrade from a RAC database but now it is single instance. The problem (maybe not, you can work in this way without issues) is thread 2 is not active. I like to keep clean all I can. Here I show you how to drop this thread.

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        247  104857600        512          1 NO  INACTIVE
         2          1        248  104857600        512          1 NO  INACTIVE
         3          1        249  104857600        512          1 NO  CURRENT
         4          2          1  104857600        512          1 NO  CURRENT
         5          2          0  104857600        512          1 YES UNUSED

In this case you only need execute the next commands;

First of all

SQL> ALTER DATABASE DISABLE THREAD 2;

After you need drop the group associaties to the thread 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Now, if we repeat the query on v$log;

SQL> SELECT * FROM V$LOG;                                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        250  104857600        512          1 NO  INACTIVE
         2          1        251  104857600        512          1 NO  INACTIVE
         3          1        252  104857600        512          1 NO  CURRENT

HTH – Antonio NAVARRO

How To Install Statspack On 9i Windows-box

Today I have been installing a statspack package for a older database, version 9i, maybe it is not relevant for many people but I want to post it in this entry because of I use the blog like a work diary and documentation place.

First of all we need to create a new tablespace where create the new objects for perfstat user, of course, you can use an existing tablepaspace but it is not a good idea;

 


CREATE TABLESPACE "ESTADISTICAS" DATAFILE
'X:\ORACLE\DATOS\ESTADISTICAS.DBF' SIZE 2147483648 -- 2 Gigas
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

After we need the spcreate.sql scritp, locate on %ORACLE_HOME%\rdbms\admin

foto1

Connect to database as sysdba and execute de sql;

foto2

This sql script call the following scripts;

  • SPCUSR.SQL: Creates the user and grants privileges
  • SPCTAB.SQL: Creates the tables
  • SPCPKG.SQL: Creates the package

Ask me for the password for user perfstat (in clear text);

foto3.PNG

Next ask me for tablespace where locate tables with the data are recolected from the system. You must write down the tablespace name create before. You need a temporary tablespace too;

foto5.PNG

if all is okay you get a reply similar to the next window, however it is necessary to review the spcpkg.lis file for any errors;

foto7.PNG

Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;

foto7

And the output;

foto8

HTH – Antonio NAVARRO

 

RDBMS 32 Or 64 Bits?

In this entry, I like show how to know  if platform is 32 Bits or 64 Bits, for this simple question you can user the next query.

SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

Output example for 32 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
32

Output example for 64 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
64

HTH – Antonio NAVARRO

How To Create A Oracle User

I like show a other way to create a user in Oracle database, usually people use the next sentence to create a new user;

CREATE USER XXX IDENTIFIED BY YYY;

It is okay and it is accord to ISO 9075/ANSI SQL but other way in Oracle is with GRANT statement. I post below a pair of examples;

 
REMARK -- This create a single user ---
GRANT CONNECT  TO USER1 IDENTIFIED BY USER1;

REMARK -- This create multiple users ---
GRANT CONNECT TO USER1,USER2,USER3 IDENTIFIED BY USER1,USER2, USER3;

For mor information, please, refer the next link (from Oracle Documentation);

Oracle Documentation (12.1c)

HTH – Antonio NAVARRO

 

One More Time, “IS NULL” Is Not The Same That “= NULL”

Today a developer ask me about a issue that he has when executed a query, it must to return rows and he didn’t any get. When I took a look at query I can see a “= NULL” like a filter condition in the where clause. Of course, I see it many times, and it is a error in Oracle, Sybase, MySQL, SQL Server, ……. In this case the person has been working as sql developer since two months ago, it can be a excuse.

I post the, maybe, more simple example;

TEST*ANTO> create table uno (col1 number(5), col2 number(5));

Tabla creada.

TEST*ANTO> insert into uno values (1,2);

1 fila creada.

TEST*ANTO> insert into uno (col1) values (3);

1 fila creada.

TEST*ANTO> select * from uno;

      COL1       COL2
__________ __________
         1          2
         3 ø

REM 
REM AND NOW, THE PROBE
REM 

REM *** This not return anything ***

TEST*ANTO> select * from uno where col2 = NULL;

ninguna fila seleccionada

REM *** This return one row ***

TEST*ANTO> select * from uno where col2 IS NULL;

      COL1       COL2
__________ __________
         3 ø

HTH – Antonio NAVARRO

Create Partitioned Table Using CTAS

Leave here an example of how to create a partitioned table from a non partitioned table ( in this case with organization head). Remember that CTAS copy the NULL or NOT NULL constraint, but not as DEFAULT VALUES conditions.

 

 CREATE TABLE SIEBEL.USERS_PARTITIONATED
PARTITION BY RANGE (F_ALTA)
(
   PARTITION P1 VALUES LESS THAN (TO_DATE('01-01-2013','DD-MM-YYYY')),
   PARTITION P2 VALUES LESS THAN (TO_DATE('01-01-2014','DD-MM-YYYY')),
   PARTITION P3 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY'))
)
TABLESPACE WORK
AS SELECT * 
FROM  SIEBEL.USERS
/

 

 

HTH – Antonio NAVARRO