ORA-01586: When trying to drop a database (RAC)

SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             507511688 bytes
Database Buffers          545259520 bytes
Redo Buffers               13905920 bytes
Database mounted.
SQL> select name from v$database;

NAME
---------
R12TST

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation


SQL> sho parameter cluster_data

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             507511688 bytes
Database Buffers          545259520 bytes
Redo Buffers               13905920 bytes
Database mounted.
SQL> select name from v$database;

NAME
---------
R12TST

SQL> sho parameter cluster_data

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>

Apps User Connection Details

This SQL shows details about users connected to the system (sid, responsibilty, form name, Oracle pid, Application pid and more..)

The parameters are Apps Username or Oracle Session Id (put % where you don't pass a parameter).

It contains 3 SQL's with unions:
1) 1st sql - OA Framework screens (login screen, iSupport etc...)
2) 2nd sql - Responsibility connection details (for Java Applet)
3) 3rd sql - Forms connection details


select usr.user_name "Apps Username"
,i.first_connect "First Connect Date"
,ses.sid
,ses.serial#
,ses.module
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect "Function Start Time"
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,rsp.responsibility_name
,r.start_time
,null
,null
,null form_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and rsp.language(+) = 'US'
and r.audsid = ses.audsid
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,null
,null
,frm.user_form_name
,ff.type
,f.start_time
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID';

DBC file parameters for R12

DBC file parameters for R12 :

FND_MAX_JDBC_CONNECTIONS=200
FND_JDBC_BUFFER_MIN=5
FND_JDBC_BUFFER_MAX=25%
FND_JDBC_BUFFER_DECAY_INTERVAL=60
FND_JDBC_BUFFER_DECAY_SIZE=1
FND_JDBC_USABLE_CHECK=true
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_PLSQL_RESET=false

When Apache starts, it will create a buffer of 5 JDBC connections (FND_JDBC_BUFFER_MIN=5) plus a few more as Java dependent products initialize. As users login, those 5 connections will get used and more connections will be created to replenish the buffer. These connections will increase as needed up to the limit of 200 (FND_MAX_JDBC_CONNECTIONS). As users log off, the JDBC connections will die off at a rate of 1 every 60 seconds (FND_JDBC_BUFFER_DECAY_SIZE=1, FND_JDBC_BUFFER_DECAY_INTERVAL=60), but this decay won’t start until you have reached 50 JDBC connections (25% of 200 where FND_JDBC_BUFFER_MAX=25% and FND_MAX_JDBC_CONNECTIONS=200). The decay will slowly reduce the number of inactive JDBC connections down to FND_JDBC_BUFFER_MIN=5, with the least recently used ones being removed first. Note - that each of these parameters are per JVM.

How to migrate Oracle datafiles to ASM storage via RMAN

$ sqlplus / as sysdba

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled

/*
   If you are using block change tracking, then disable it.
   In my case I am not using it.
*/

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

System altered.

/*
  This parameter define the default location for data files,
  control_files etc, if no location for these files is specified
  at the time of their creation. +DATA is the diskgroup in ASM.
*/

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.

/*
  This parameter defines the default location number 1 for online logs.
*/

SQL> alter system reset control_files scope=spfile sid='*';

System altered.

/*
   Here we remove the control_files parameter from spfile.
   So next time we restore the control file it will automatically go
   to +DATA diskgroup since it is defined in db_create_file_dest,
   and the new path will be automatically updated in spfile.
*/

SQL> exit

Once all initialization parameters are set properly, start RMAN to migrate the files to ASM diskgroups.

$ rman target /

RMAN> startup nomount

Oracle instance started

Total System Global Area     608174080 bytes

Fixed Size                     1220820 bytes
Variable Size                171970348 bytes
Database Buffers             427819008 bytes
Redo Buffers                   7163904 bytes

RMAN> restore controlfile from
      '/u01/apps/oracle/oradata/ora10g/control01.ctl';

output trimmed......

channel ORA_DISK_1: copied control file copy
output filename=+DATA/ora10g/controlfile/backup.256.716296513
Finished restore at 28-AUG-09

/*
   Control file restored to +DATA from its old location.
   It will be restored to +DATA because we set db_create_file_dest to
   +DATA.
*/

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

.......
input datafile fno=00001 name=/u01/apps/oracle/oradata/ora10g
/system01.dbf
output filename=+DATA/ora10g/datafile/system.257.716296627
 tag=TAG20090828T111707 recid=2 stamp=716296715
.......
input datafile fno=00003 name=/u01/apps/oracle/oradata/ora10g
/sysaux01.dbf
output filename=+DATA/ora10g/datafile/sysaux.258.716296723
 tag=TAG20090828T111707 recid=3 stamp=716296785
.......
input datafile fno=00005 name=/u01/apps/oracle/oradata/ora10g
/example01.dbf
output filename=+DATA/ora10g/datafile/example.259.716296789
 tag=TAG20090828T111707 recid=4 stamp=716296809
.......
input datafile fno=00002 name=/u01/apps/oracle/oradata/ora10g
/undotbs01.dbf
output filename=+DATA/ora10g/datafile/undotbs1.260.716296815
 tag=TAG20090828T111707 recid=5 stamp=716296822
.......
input datafile fno=00004 name=/u01/apps/oracle/oradata/ora10g
/users01.dbf
output filename=+DATA/ora10g/datafile/users.261.716296823
tag=TAG20090828T111707 recid=6 stamp=716296823
.......
Finished backup at 28-AUG-09

/* make a copy of all datafiles on +DATA diskgroup. */

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ora10g/datafile
/system.257.716296627"
datafile 2 switched to datafile copy "+DATA/ora10g/datafile
/undotbs1.260.716296815"
datafile 3 switched to datafile copy "+DATA/ora10g/datafile
/sysaux.258.716296723"
datafile 4 switched to datafile copy "+DATA/ora10g/datafile
/users.261.716296823"
datafile 5 switched to datafile copy "+DATA/ora10g/datafile
/example.259.716296789"

/*
   Switch database to the datafile copies. After this switch the
copies on +DATA
   have become the database datafiles and the original files have
 become copies.
*/

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.

After you open the database, go ahead and enable the block change tracking file if you disabled it in the start of this process.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Lets now verify where our control file and data files are sitting:

$ sqlplus / as sysdba

SQL> select name from v$datafile;

NAME
-------------------------------------------------
+DATA/ora10g/datafile/system.257.716296627
+DATA/ora10g/datafile/undotbs1.260.716296815
+DATA/ora10g/datafile/sysaux.258.716296723
+DATA/ora10g/datafile/users.261.716296823
+DATA/ora10g/datafile/example.259.716296789

SQL> show parameter control_files

NAME                TYPE        VALUE
------------------- ----------- ------------------------------
control_files       string      +DATA/ora10g/controlfile/backu
                                p.256.716296513
SQL>

They are on the +DATA diskgroup in ASM storage.

Migrate tempfile to ASM storage:

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf

SQL> alter tablespace temp add tempfile size 20m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf
+DATA/ora10g/tempfile/temp.264.718706509

SQL> alter tablespace temp drop tempfile
  2  '/u01/app/oracle/oradata/ora10g/temp01.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
+DATA/ora10g/tempfile/temp.264.718706509

SQL> 

How to Enable Passwordless Authentication with SSH

Often times you have automated scripts that require access to multiple machines from a single source and need to do so without having to deal with ssh password prompts. And in other instances you may have a bastian host (strong point) security model which you would like to have passwordless communication from. Below are 10 steps to setting up passwordless authentication with SSH in Linux.

Step 1:(*note server1 is the source server and server2 will be the destination server)

server1# mkdir ~/.ssh

Step 2:

server1# cd ~/.ssh

Step 3:

server1# ssh-keygen -t rsa

Generating public/private rsa key pair.
Enter file in which to save the key (“your_local_home”/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in id_rsa.
Your public key has been saved in id_rsa.pub.
The key fingerprint is:
18:6a:e3:78:ab:2d:0c:8e:f9:67:f7:30:32:44:77:34 vv@server1

Step 4:

server1# scp ~/.ssh/id_rsa.pub vv@server2:/home/vv/id_rsa.server1.pub

Step 5:

server1# ssh vv@server2 Password:

Step 6:

server2# mkdir .ssh

Step 7:

server2# chmod 700 .ssh

Step 8:

server2# cat id_rsa.server1.pub >> .ssh/authorized_keys

Step 9:

server2# chmod 644 .ssh/authorized_keys

Step 10:

server2# exit server1# ssh vv@server2

*Note repeat steps 4-10 for all target servers you would like passwordless access from server1

* FYI Ensure your /home/user directory has the permission 755 also!

You should be all set!

Exporting tables from different Schemas using EXPDP

Following command can be used to export a single table from a different schema :

$ expdp directory=DATA_PUMP_DIR dumpfile=expdp_tst.dmp logfile=expdp_tst.log TABLES=SCHEMA.TABLE_NAME,SCHEMA1.TABLE_NAME

Restore FNDLIBR executable

Restore FNDLIBR executable which has been deleted accidentally:

1. Invoke adadmin on concurrent manager node

2. Select option 2. Maintain Applications Files menu

3. Select 1. Relink Applications programs

4. Enter FND when it prompts for

Enter list of products to link ('all' for all products) [all]

5. When prompted for

Generate specific executable for each selected product [No] ? YES

select YES

& from list of executable select FNDLIBR.

This will create new FNDLIBR executable.

Build oracle with RAC off/on

If the Oracle binaries are installed with the RAC, one can’t use those binaries to start Oracle Instance if CRS is not running, so in case when one needs to start the instance without CRS one needs to rebuild the oracle and it’s libraries to start the instance.

To turn off RAC

# link the oracle libraries

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_off

# rebuild oracle

$ cd $ORACLE_HOME/bin

$ relink oracle

To turn on RAC

# link the oracle libraries

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_on

# rebuild oracle

$ cd $ORACLE_HOME/bin

$ relink oracle

Incase above set of commands do not work following can be used :

To turn on RAC
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on ioracle