Duplicate database fails with RMAN-05537

Error statement

[oracle@blr221012 dbs]$ rman TARGET sys/*****@B12500DV AUXILIARY sys/****@B12500DV_DEST

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Dec 17 11:11:24 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: B12500DV (DBID=3563904870)
connected to auxiliary database: B12500DV (not mounted)

RMAN> RUN
{
2> 3>   SET NEWNAME FOR DATAFILE 1 TO '/orasw/oracle/app/oracle/oradata/B12500DV/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/orasw/oracle/app/oracle/oradata/B12500DV/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/orasw/oracle/app/oracle/oradata/B12500DV/undotbs01.dbf';
4> 5> 6>   SET NEWNAME FOR DATAFILE 4 TO '/orasw/oracle/app/oracle/oradata/B12500DV/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/orasw/oracle/app/oracle/oradata/B12500DV/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/orasw/oracle/app/oracle/oradata/B12500DV/cists01.dbf';
7> 8> 9>   SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.dbf';
  DUPLICATE TARGET DATABASE TO 'B12500DV' FROM ACTIVE DATABASE
10> 11>   SPFILE
  SET LOG_FILE_NAME_CONVERT '/orasw/oracle/app/oracle/oradata/B12500DV/','/orasw/oracle/app/oracle/oradata/B12500DV/'
12> 13>   SET DB_FILE_NAME_CONVERT  '/orasw/oracle/app/oracle/oradata/B12500DV/','/orasw/oracle/app/oracle/oradata/B12500DV/'
    LOGFILE
14> 15>       GROUP 3 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo03.log') SIZE 50M REUSE,
16>       GROUP 4 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo04.log') SIZE 100M REUSE,
17> GROUP 5 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo05.log') SIZE 100M REUSE,
18> GROUP 6 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo06.log') SIZE 100M REUSE,
GROUP 7 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo07.log') SIZE 100M REUSE,
19> 20> GROUP 8 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo08.log') SIZE 100M REUSE,
21> GROUP 9 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo09.log') SIZE 100M REUSE;
}
22>
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 17-DEC-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/17/2012 11:11:37
RMAN-05501: aborting duplication of target database
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

Reason: 

Auxiliary instance was started with spfile and we specified spfile in duplicate command as below. RMAN cannot restore the server parameter file if the auxiliary database is already started with a server parameter file

RUN
 {
  SET NEWNAME FOR DATAFILE 1 TO '/orasw/oracle/app/oracle/oradata/B12500DV/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/orasw/oracle/app/oracle/oradata/B12500DV/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/orasw/oracle/app/oracle/oradata/B12500DV/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/orasw/oracle/app/oracle/oradata/B12500DV/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/orasw/oracle/app/oracle/oradata/B12500DV/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/orasw/oracle/app/oracle/oradata/B12500DV/cists01.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.dbf';
  DUPLICATE TARGET DATABASE TO 'B12500DV' FROM ACTIVE DATABASE
  SPFILE
  PASSWORD FILE
  NOFILENAMECHECK
  LOGFILE
  GROUP 3 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo03.log') SIZE 50M REUSE,
  GROUP 4 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo04.log') SIZE 100M REUSE,
  GROUP 5 ('//orasw/oracle/app/oracle/oradata/B12500DV/redo05.log') SIZE 100M REUSE,
  GROUP 6 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo06.log') SIZE 100M REUSE,
  GROUP 7 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo07.log') SIZE 100M REUSE,
 GROUP 8 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo08.log') SIZE 100M REUSE,
 GROUP 9 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo09.log') SIZE 100M REUSE;
 }

Solution:

Start the auxiliary database with a client parameter file or do not specify SPFILE in duplicate command if you had started auxiliary database using spfile.

Eg:
[oracle@blr221012 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 17 11:03:30 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount pfile='/orasw/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initB12500DV.ora';
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2225064 bytes
Variable Size             159386712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5214208 bytes
SQL> exit


OR

Use duplicate command as below.

RUN
 {
  SET NEWNAME FOR DATAFILE 1 TO '/orasw/oracle/app/oracle/oradata/B12500DV/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/orasw/oracle/app/oracle/oradata/B12500DV/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/orasw/oracle/app/oracle/oradata/B12500DV/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/orasw/oracle/app/oracle/oradata/B12500DV/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/orasw/oracle/app/oracle/oradata/B12500DV/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/orasw/oracle/app/oracle/oradata/B12500DV/cists01.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.dbf';
  DUPLICATE TARGET DATABASE TO 'B12500DV' FROM ACTIVE DATABASE
  PASSWORD FILE
  NOFILENAMECHECK
  LOGFILE
  GROUP 3 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo03.log') SIZE 50M REUSE,
  GROUP 4 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo04.log') SIZE 100M REUSE,
  GROUP 5 ('//orasw/oracle/app/oracle/oradata/B12500DV/redo05.log') SIZE 100M REUSE,
  GROUP 6 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo06.log') SIZE 100M REUSE,
  GROUP 7 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo07.log') SIZE 100M REUSE,
 GROUP 8 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo08.log') SIZE 100M REUSE,
 GROUP 9 ('/orasw/oracle/app/oracle/oradata/B12500DV/redo09.log') SIZE 100M REUSE;
 }

Duplicate Database fails with RMAN-05001

RMAN Duplicate fails with following errors, even though allocated channel for duplication is auxiliary using below command.


sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/17/2012 11:18:31
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/cists01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/example01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/users01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/undotbs01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/sysaux01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/system01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo10.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo08.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo09 conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo06.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo05.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo04.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /orasw/oracle/app/oracle/oradata/B12500DV/redo03.log conflicts with a file used by the target database

RMAN> exit

Reason:
Source and Auxiliary have same directory structure and are running on two different machines. Source database files are sharing the name as the duplicate datafiles being generated. So we should tell rman not to check file names and directory structure of destination(where duplicate db is being created) using nofilenamecheck command. Please note that, if you are duplicating database on the same server, you shouldn't use this parameter,

Solution:

Use the nofilenamecheck in syntax as below


RMAN> DUPLICATE TARGET DATABASE TO 'B12500DV' FROM ACTIVE DATABASE NOFILENAMECHECK;

Starting Duplicate Db at 17-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=135 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''B12500DV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''B12500DV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/orasw/oracle/app/oracle/oradata/B12500DV/control01.ctl';
   restore clone controlfile to  '/orasw/oracle/app/oracle/fast_recovery_area/B12500DV/control02.ctl' from
 '/orasw/oracle/app/oracle/oradata/B12500DV/control01.ctl';

How to add and remove databases from Oracle cluster

To remove database from Oracle clusterware, use below commands using "oracle" user(Oracle software owner).

srvctl remove database -d db_name

$ srvctl remove database -d test
PRKO-3141 : Database test could not be removed because it was running

$ srvctl stop database -d test
$ srvctl remove database -d test
Remove the database test? (y/[n]) y
$


Once you remove the database,cluserware doesn't maintain any information about this database and its instances and hence you can't manage this database using srvctl.

$ srvctl status database -d TEST
PRCD-1120 : The resource for database TEST could not be found.
PRCR-1001 : Resource ora.test.db does not exist
$ srvctl status instance -d TEST -i TEST1
PRCD-1120 : The resource for database TEST could not be found.
PRCR-1001 : Resource ora.test.db does not exist
$ srvctl status instance -d TEST -i TEST2
PRCD-1120 : The resource for database TEST could not be found.
PRCR-1001 : Resource ora.test.db does not exist



To add database to Oracle clusterware,use below commands using "oracle" user(Oracle software owner).

$ srvctl add database -d test -o /u01/app/oracle/product/11.2.0/dbhome_1 --> This is $ORACLE_HOME of database
$ srvctl config database -d test
Database unique name: test
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances:
Disk Groups:
Services:
Database is administrator managed
$ srvctl status database -d test
Database is not running.

$ srvctl start database -d test
Database test cannot be started since it has no configured instances.

So register the database instances as below

$ srvctl add instance -d test -i TEST1 -n rsvmsb300
$ srvctl add instance -d test -i TEST2 -n rsvmsb302
$ srvctl start database -d test
$ srvctl config database -d TEST
Database unique name: test
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances: TEST1,TEST2
Disk Groups:
Services:
Database is administrator managed


$ id
uid=1000(oracle) gid=2262(oinstall) groups=1001(dba),2259(asmadmin),2260(asmdba),2261(asmoper),2262(oinstall)
$

Password file and ASM users maintenance in RAC

Password file maintenance prior to 11gR2.

If you configure ASM and database using dbca, password file is automatically created and if you configure  without using DBCA, then you must manually create a password file using orapwd utility and is named orapw$ORACLE_SID. It is stored in the $ORACLE_HOME/dbs directory. Since $ORACLE_HOME is usually not shared in a clustered environment, this results in each instance having its own password file as shown below

orapwd file=$ORACLE_HOME/dbs/orapw+ASM1 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+ASM2 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+TEST1 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+ASM2 password=**** entries=5

This may lead to inconsistencies between the password files because password file will be updated on the cluster node at which the grant was executed(eg:grant sysdba to user).

So not sharing Oracle password files between the nodes in a cluster will result in inconsistencies between the password files if grant is made at only one of the instances. In order to avoid this you can either store the password file on a shared filesystem and create symbolic links from $ORACLE_HOME/dbs to this shared filesystem or execute the command on each instance to update password file at each instance

Password file maintenance in 11gR2

From 11gR2 onward  when password is changed for a privileged user on ASM instance of one node in a cluster, it is immediately propagated on all other nodes in the cluster using ASM CKPT process and hence there is no need to place theASM password files on a shared filesystem.

Another change that was made in Oracle 11g release 2 is a new password file name convention. In prior releases the password file name was based on $ORACLE_SID including the node suffix, since Oracle 11g release 2 the password file name is based on $ORACLE_SID without the node suffix. Thus orapw+ASM instead of orapw+ASM1 for an ASM instance.


[root@rsvmsb300 dbs]# ps -ef|grep pmon
root     13565 22639  0 02:23 pts/0    00:00:00 grep pmon
oracle   20527     1  0 Nov26 ?        00:00:32 ora_pmon_TEST1
grid     27380     1  0 Nov26 ?        00:00:13 asm_pmon_+ASM1



[root@rsvmsb300 dbs]# ls -lrt /u01/app/11.2.0/grid/dbs/orapw*
-rw-r----- 1 grid oinstall 1536 Dec 11 00:59 /u01/app/11.2.0/grid/dbs/orapw+ASM

[root@rsvmsb300 dbs]# ls -lrt /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapw*
-rw-r----- 1 oracle oinstall 1536 Nov 26 05:56 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTEST1
[root@rsvmsb300 dbs]#


Eg: When we grant privilege to user in ASM, then it is automatically propagated to other instances as shown below.

[grid@rsvmsb300 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 00:58:14 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user test identified by test;

User created.

SQL> grant sysasm to test;

Grant succeeded.

SQL> conn test/test as sysasm
Connected.

SQL> select * from gv$pwfile_users order by inst_id;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
         1 ASMSNMP                        TRUE  FALSE FALSE
         1 SYS                            TRUE  TRUE  TRUE
         1 TEST                           FALSE FALSE TRUE
         2 ASMSNMP                        TRUE  FALSE FALSE
         2 SYS                            TRUE  TRUE  TRUE
         2 TEST                           FALSE FALSE TRUE

6 rows selected.

SQL> exit


However, privileges are not propagated to another instances in case of database instances as show below.

$ . oraenv
ORACLE_SID = [TEST1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 02:34:30 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user test identified by test;

User created.

SQL> grant sysdba to test;

Grant succeeded.

SQL> select * from gv$pwfile_users order by inst_id;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS

---------- ------------------------------ ----- ----- -----

         1 SYS                            TRUE  TRUE  FALSE

         1 TEST                           TRUE  FALSE FALSE

         2 SYS                            TRUE  TRUE  FALSE

Note: Change in password file maintenance in a cluster is currently only implemented for clustered ASM and not for RAC database.For RAC database environments password files should either be placed on a shared filesystem (and symbolic linkfrom $ORACLE_HOME/dbs) or password file changes should be performed manually on all instances in order to keep the password file contents synchronized on all nodes. In example shown above, when I granted sysdba to user TEST on instance 1, it was updated to only in instance 1 (node1)password file. However as show in example above, when I granted sysasm privilege to ASM user , it propagated to all instances

ASM user maintenance in 11g

In Oracle 11g(R1 and R2),we can create user in +ASM instance just like in RDBMS instance and can view these users using gv$pwfile_users ,connecting to ASM instance.

You can't give sysasm privilege to database users. If you try to give, you will get error as

SQL> grant sysasm to test;
grant sysasm to test
*
ERROR at line 1:
ORA-15294: SYSASM privilege not allowed on database instance


You can view ASM users by connecting to ASM instance with SYSASM privilege.We can create ASM users and grant privileges as shown below

[grid@rsvmsb300 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/grid

[grid@rsvmsb300 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:16:22 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user test identified by test123;

User created.

SQL> grant sysasm to test;

Grant succeeded.

SQL> grant sysdba to test;

Grant succeeded.

SQL> create user test2 identified by test2;

User created.

SQL> select * from gv$pwfile_users;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
         1 SYS                            TRUE  TRUE  TRUE
         1 ASMSNMP                        TRUE  FALSE FALSE
         1 TEST                           TRUE  FALSE TRUE
         1 TEST2                          FALSE FALSE FALSE
         2 SYS                            TRUE  TRUE  TRUE
         2 ASMSNMP                        TRUE  FALSE FALSE
         2 TEST                           TRUE  FALSE TRUE
         2 TEST2                          FALSE FALSE FALSE

8 rows selected.

SQL> exit


We can connect to ASM instance using privileged users as below

[grid@rsvmsb300 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/grid.

[grid@rsvmsb300 ~]$ sqlplus test/test123 as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:22:36 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> exit

[grid@rsvmsb300 ~]$ sqlplus test as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:22:48 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

In ASM, users are authenticated using password file. there is no dictionary table to maintain users in ASM. So if you try to connect to ASM instance like database users, you will get error as below. To connect to ASM, you must be authenticated using password file.

[grid@rsvmsb300 ~]$ sqlplus test

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:29:17 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test
Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test /as sysasm
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>

[grid@rsvmsb300 ~]$ sqlplus test2

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:32:05 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test2 /as sysasm
Enter password:
ERROR:
ORA-01031: insufficient privileges


Enter user-name:



[grid@rsvmsb300 ~]$

What is the difference between RPO and RTO in dataguard

Recovery Point Objective(RPO) is the amount of data you can afford to lose, if a server had a failure. It depends on the backup strategies in your Organization

Recovery Time Objective(RTO) is the time that it could take to get your systems back up and running after a failure.

SYSAUX tablespace growing rapidly

From Oracle 10gR1 onwards, there is serious bug(14373728 and 8553944), due to which the SYSAUX tablespace will grow continuously. This issue is fixed in 12.1 release

Why It grows?

Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. This history information is stored in SYSAUX tablespace and tables involved in this are as below:

WRI$_OPTSTAT_OPR                     
WRI$_OPTSTAT_AUX_HISTORY                 
WRI$_OPTSTAT_TAB_HISTORY                 
WRI$_OPTSTAT_IND_HISTORY                 
WRI$_OPTSTAT_HISTGRM_HISTORY             
WRI$_OPTSTAT_HISTHEAD_HISTORY          

 By default, the MMON performs the automatic purge that removes all stats history older than the following:

* current time - statistics history retention (by default 31 days)
* time of recent analyze in the system - 1

MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job.MMON will do this activity once in 24 hrs. If the operation takes more than 5 minutes, then it is aborted and stats not purged. No trace or alert message is reported. Because of this, as time elapse more data will be accommodated in above tables.

Statistics history retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.


How to Identify

I have an Oracle 11g R2 (11.2.0.3) database where I noticed the SYSAUX tablespace was growing larger every day.   After running $ORACLE_HOME/rdbms/admin/awrinfo.sql (Doc ID 1292724.1), I found the largest consumer to be SM/OPTSTAT(9.5GB) and SM/AWR(1GB) as shown below.

Note: When collected data for AWR and similar OP_STAT tables reaches an internally defined threshold volume of data Oracle will automatically create partitions . This AWR, SQLSETs and similar data is then stored in partitions including WRH/WRI based objects.


(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                        9,415.1 MB
| SM/AWR               SYS                        1,005.3 MB
| SM/ADVISOR           SYS                          188.5 MB
| XDB                  XDB                          125.8 MB
| EM                   SYSMAN                        82.3 MB


(3b) Space usage within AWR Components (> 500K)
**********************************


COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
ASH           382.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_3563904870_4350       -  97%  TABLE PARTITION
ASH            38.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_3563904870_4350    -  98%  INDEX PARTITION



(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR     3,220.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR     2,905.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR     1,930.0 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR       448.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                    INDEX
NON_AWR       296.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                                      TABLE
NON_AWR       232.0 SYS.I_WRI$_OPTSTAT_H_ST                                               INDEX
NON_AWR       168.0 SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST                                        INDEX
NON_AWR       141.0 SYS.SYS_LOB0000006306C00038$$                                         LOBSEGMENT
NON_AWR       104.0 SYS.I_WRI$_OPTSTAT_TAB_ST                                             INDEX


How to resolve

1. Turn off the Autoextend on the SYSAUX at the earliest to ensure that the tablespace doesn’t grow out of bounds and finally become complete unmanageable.

2. Manually purge old statistics using DBMS_STATS.purge_stats as below


Find out your present retention value using the below statement

select dbms_stats.get_stats_history_retention from dual;

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

Find out the oldest statistics history using below statement(Shows available stats that have not been purged):

select dbms_stats.get_stats_history_availability from dual;

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
05-NOV-12 05.31.04.053232000 AM +05:30


Set retention of old stats to less number of days. I set here it to 10 days as below.

exec dbms_stats.alter_stats_history_retention(&days);

SQL> exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         10

Purge stats older than 10 days. Best to do this in stages if there is a lot of data (sysdate-30,sydate-28 etc)since it consumes more resources. Do this activity during less activities on the database. This purge will delete data from WRI$ tables.

Below command will purge stats which is older than 28 days.

SQL> exec dbms_stats.purge_stats(sysdate-28);


PL/SQL procedure successfully completed.

Below command shows available stats that have not been purged

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
08-NOV-12 09.46.46.000000000 AM +05:30


Once purge is done, reorg these tables to release space to the database. Refer ID 1271178.1 for more details.

To Configure boot.properties file for auto login while starting/stopping OBIEE 11g

A boot.properties file contains user credentials for starting and stopping an instance of WebLogic Server. An Administration  Server can refer to this file for user credentials instead of prompting you to provide them.

To configure the boot.properties file for the AdminServer (WebLogic), perform the following steps:

1. Go to $MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServers/security and create the boot.properties file.
vi boot.properties

2. add the below values in a file and save it.
username=admin_username
password=admin_password

eg:
username=weblogic
password=weblogic1

3. Now go to $MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin and run Admin server in nohup mode

nohup ./startWebLogic.sh >startWebLogic.log &

You can check in the log that it's using boot.properties file for login credentials.

eg:

/scratch/home/mw1/user_projects/domains/bifoundation_domain/servers/AdminServer/security/boot.properties>

Nov 28, 2012 11:40:25 PM oracle.ods.virtualization.engine.util.VDELogger info
INFO: Notification sent for Mapping config object reloaded




and also username and password will be encrypted automatically once admin server is started using boot.properties file. Samething with managed server
eg:
-bash-4.1$ cat boot.properties
#Wed Nov 28 23:39:59 PST 2012
password={AES}KzE36wWcZfXJriP/ctNLXyiJ7XhGliErrE3Hd9WJyj4\=
username={AES}L/OMSXhRReONiUV+ts45JE7uM5IZ+WAjSbhBNsG6xH8\=
-bash-4.1$ pwd
/scratch/home/mw1/user_projects/domains/bifoundation_domain/servers/AdminServer/security



To configure the boot.properties file for the Managed Server, perform the following steps:

1. Go to $MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/security and create the boot.properties file.
vi boot.properties

2. add the below values in a file and save it.
username=admin_username
password=admin_password

eg:
username=weblogic
password=weblogic1

3. Now go to $MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin and run managed server in nohup mode.

nohup ./startManagedWebLogic.sh bi_server1 >startManagedWebLogic.log &

Why Oracle Clusterware Needs Odd Number of Voting Disks

An odd number of voting disks is required for proper clusterware configuration. A node must be able to strictly access more than half of the voting disks at any time. So, in order to tolerate a failure of n voting disks, there must be at least 2n+1 configured.

When you have one voting disk and it goes bad, the cluster stops functioning and when you have two and one goes bad, the same happens because the nodes realize they can only write to half of the original disks , violating the rule that they must be able to write > half.When you have three and one goes bad, the cluster runs fine because the nodes know they can access more than half of the original voting disks (2/3 > half). That's why oracle recommend 3 voting disks for two node cluster.

Oracle Warehouse Builder patches for 10.2.0.2 and 10.2.0.3

Oracle warehouse builder software for 10.2.0.2 and 10.2.0.3 can be found in Oracle meta link with patch numbers 5696353 and 6264457 respectively.  Refer the document 340955.1 for further details.

Error while starting managed server in OBIEE 11.1.1.5:The persistent file store "_WLS_bi_server1" cannot open file _WLS_BI_SERVER1000000.DAT

While starting managed server we got the error as




weblogic.store.PersistentStoreException: [Store:280105]The persistent file store "_WLS_bi_server1" cannot open file _WLS_BI_SERVER1000000.DAT.
        at weblogic.store.io.file.Heap.open(Heap.java:325)
        at weblogic.store.io.file.FileStoreIO.open(FileStoreIO.java:104)
        at weblogic.store.internal.PersistentStoreImpl.recoverStoreConnections(PersistentStoreImpl.java:431)
        at weblogic.store.internal.PersistentStoreImpl.open(PersistentStoreImpl.java:422)
        at weblogic.store.admin.AdminHandler.activate(AdminHandler.java:126)


Solution:



1. Go to $BI_DOMAIN_HOME


2. find _WLS_BI_SERVER1000000.DAT file as below 


$ find -name "*.DAT"
./servers/AdminServer/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT
./servers/AdminServer/data/store/default/_WLS_ADMINSERVER000000.DAT
./servers/bi_server1/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT
./servers/bi_server1/data/store/default/_WLS_BI_SERVER1000000.DAT
./BipJmsStore/BIPJMSSTORE000000.DAT


3.go to the location and then romove it else rename it


$ cd ./servers/bi_server1/data/store/default/
$ ls -lrt
total 1032
-rw-r----- 1 oracle oinstall 1049088 May 17 07:29 _WLS_BI_SERVER1000000.DAT
$ mv _WLS_BI_SERVER1000000.DAT _WLS_BI_SERVER1000000.DAT_old


4. start the managed server as below.


$ cd /orasw/MW5/user_projects/domains/bifoundation_domain/bin
$ nohup ./startManagedWebLogic.sh bi_server1 http://tudevlv0337.us.oracle.com:10000 >startManagedWebLogic.log &

Workflow 2.6.4 Configuration In Database 11g Fails With error "WFCA OUT: Error: Could not connect to the remote server."

While installing Oracle workflow in 11g, it fails with error as "WFCA OUT: Error: Could not connect to the remote server. Please check if the server is down or the client is using invalid host, ORMI port or password to connect: Connection refused".

Error in workflow log is as below.
WorkflowCA: Executing: /orasw/app/oracle/product/11.2.0.2_db/jdk/bin/java -jar /orasw/app/oracle/product/11.2.0.2_db/oc4j/j2ee/home/admin.jar ormi://slc00uzk.us.oracle.com:6041 oc4jadmin welcome -application WFMLRSVCApp -installDataSource -url jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=slc00uzk.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=B12402Q2))) -username OWFMGR -password ->pwForOwfMgr -className com.evermind.sql.DriverManagerDataSource -location jdbc/WorkflowDS -xaLocation jdbc/xa/WorkflowDS -ejbLocation jdbc/WorkflowDS -connectionDriver oracle.jdbc.driver.OracleDriver

WFCA OUT: Error: Could not connect to the remote server. Please check if the server is down or the client is using invalid host, ORMI port or password to connect: Connection refused
WorkflowCA: Exit Val: 2
WorkflowCA:  Created a redirected data source with application WFMLRSVCApp :
WorkflowCA: Executing: /orasw/app/oracle/product/11.2.0.2_db/jdk/bin/java -Djava.security.properties=/orasw/app/oracle/product/11.2.0.2_db/oc4j/j2ee/home/config/jazn.security.props -Doracle.security.jazn.config=/orasw/app/oracle/product/11.2.0.2_db/oc4j/j2ee/OC4J_Workflow_Component_Container/config/jazn.xml -jar /orasw/app/oracle/product/11.2.0.2_db/oc4j/j2ee/home/jazn.jar -user oc4jadmin -password welcome -adduser jazn.com pwForOwfMgr
The specified user already exists in the system.
WorkflowCA:   Created obfusticated password for redirect datasource:
WorkflowCA: Executing: /orasw/app/oracle/product/11.2.0.2_db/jdk/bin/java -jar /orasw/app/oracle/product/11.2.0.2_db/oc4j/j2ee/home/admin.jar ormi://slc00uzk.us.oracle.com:6041 oc4jadmin welcome -shutdown
WFCA OUT: Error: Could not connect to the remote server. Please check if the server is down or the client is using invalid host, ORMI port or password to connect: Connection refused
WorkflowCA: Exit Val: 2
WorkflowCA:   Executed OC4J Admin script to shut down the OC4J instance :
WorkflowCA: Sun May 13 23:50:13 PDT 2012
WorkflowCA: Workflow Configuration has completed with error.
WorkflowCA: Terminating...

Solution:
Oracle Workflow will not check for or allocate a free port during the installation.By default http-web-site.xml uses port 6004 and rmi.xml uses port 6101. If either or both of these ports are in use by other processes, the Oracle Workflow Configuration Assistant will error out when trying to deploy Workflow Applications. To solve this issue, follow the below steps.


1. Check ports which are not in use as below

#> netstat -atnp|grep LISTEN |grep 6102
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product
#> netstat -atnp|grep LISTEN |grep 6009
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)

and also you can check ports which are not in use as below.

#> telnet slc00uzk.us.oracle.com 6102
Trying 10.241.39.115...
telnet: connect to address 10.241.39.115: Connection refused
telnet: Unable to connect to remote host: Connection refused
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product
#> telnet slc00uzk.us.oracle.com 6009
Trying 10.241.39.115...
telnet: connect to address 10.241.39.115: Connection refused
telnet: Unable to connect to remote host: Connection refused


2. Make a backup copy of :
- $ORACLE_HOME/oc4j/j2ee/OC4J_Workflow_Management_Container/config/rmi.xml
- $ORACLE_HOME/oc4j/j2ee/OC4J_Workflow_Component_Container/config/http-web-site.xml

3. Modify "port=6101" and "web-site port=6004" to other available ports which can be checked by using the netstat command

4. Re-run Oracle Workflow Configuration Assistant
as below

cd $ORACLE_HOME/owb/wf/install/wfinstall.csh

error ORA-27154: post/wait create failed while starting up database

We got below error while starting up the database.


$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 10 03:14:49 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@BI2400R1 AS SYSDBA 10-MAY-12> startup;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

Solution: This is caused by Kernel parameter setting. Since we were running many databases on a server, we should check our semaphore. Recommended value is

# semaphores: semmsl, semmns, semopm, semmni 

kernel.sem = 250 32000 100 128

In our case it's 

$ /sbin/sysctl -a | grep sem

error: permission denied on key 'kernel.cad_pid'

kernel.sem = 250        32000   100     142

and still we were getting the error as more than 15 databases were running on the server.

We increased the value to 275 as below

# /sbin/sysctl -w kernel.sem=275

added this new value into /etc/sysctl.conf in order the changes persist after system boot

  

and then started the DB.

$ . oraenv

ORACLE_SID = [B12400D1] ? BI2400R1

The Oracle base remains unchanged with value /orasw/app/oracle_test

$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 10 03:18:24 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@BI2400R1 AS SYSDBA 10-MAY-12> startup;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2226912 bytes

Variable Size             788530464 bytes

Database Buffers          805306368 bytes

Redo Buffers                7348224 bytes

Database mounted.

Database opened.

SYS@BI2400R1 AS SYSDBA 10-MAY-12> exit

Installing Oracle Grid Infrastructure for a Standalone Server

Perform the following steps to install Oracle Grid Infrastructure on a stand alone server.

Oracle Grid Infrastructure installs Oracle Restart and Oracle ASM and you can configure Oracle ASM with at least one disk group during configuration. Once grid installation is completed,install Oracle Database that stores database files in Oracle ASM disk groups.

1.Go to software location and Start Oracle Universal Installer as the Oracle Grid Infrastructure software owner user(eg:Oracle).

2.Start the installer with the command
$ ./runInstaller

3.On the first screen of the installer, select Install and Configure Grid Infrastructure for a Standalone Server. Click Next.

4.On the Select Product Languages screen, select one or more languages.

5. On the CreateASMDiskGroup screen,The Create ASM Disk Group screen lists all the Oracle ASM disks.

Click Change Discovery Path to select any devices to be used by Oracle ASM.In the Change Disk Discovery Path window, enter a string to use to search for devices that Oracle ASM will use. Click OK. In our example, I selected path as "/dev/*"

    * Set Redundancy to Normal
    * Select the disks: /dev/sdb1, /dev/sdc1
    * Click Next

Note:During installation, disk paths mounted on Oracle ASM and registered on ASMLIB with the string ORCL:* are listed as default database storage candidate disks


6. On the Specify ASM Password page,

    * Select 'Use same passwords for these accounts'
    * Enter your password
    * Confirm the password
    * Click Next

When you get warning on weak password, click Yes.

7.  In the Privileged Operating System Groups screen, select the name of the operating system group you created for the OSDBA group, the OSASM group, and the database operator group OSOPER.

If you create only the dba group, then you can use that group for all three privileged groups.
If you created a separate asmadmin group, then use that value for the OSASM group. Click Next.

Note:These groups do not allow for job role separation. So a warning will appear. Click Yes

8.   On the Specify Installation Location page, the location of the database home will be shown

    * Set the Software Location to /u01/app/oracle/product/11.2.0/grid.
    * Set the Oracle Base to /u01/app/oracle
    * Click Next

9.select location for Inventory.

10. The Perform Prerequisite Check page appears and completes the checks.

Since the swap size and also some kernel parameters values were sufficient for testing purpose, I ignored the below errors. If it's for production, correct these errors and proceed.

On the Summary page, the installation settings are shown. Click Finish to complete the installation.

11. The setup page shows the install progress 

12. The Execute Configuration Scripts page asks you to execute a configuration script as the root user.

    * Open a terminal window.
    * connect as the root user.

Copy the script name from the Execute Configuration Scripts page into to the terminal window and execute it with root user.

13. When the script has completed, Click OK in the Execute Configuration Scripts page.

14.When the Finish page appears, with the message "The installation of Oracle Grid Infrastructure for single Server was successful", click Close 

Oracle ASMCA is installed as part of the Oracle Grid Infrastructure for a Standalone Server installation. To create additional disk groups, run the Oracle ASMCA utility as below.

Go to $GRID_HOME/bin and execute asmca as below
./asmca

To verify that the Oracle High Availability Service is installed properly, run ./crsctl check has command from Grid_home/bin directory.

[oracle@testdb bin]$ ./crsctl check has
CRS-4638: Oracle High Availability Services is online

To check the ASM. Connect to the ASM as below and check the diskgroup.

[oracle@testdb bin]$ . oraenv
ORACLE_SID = [oracle] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[oracle@testdb bin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 16:24:51 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> select name,type from v$asm_diskgroup;

NAME                           TYPE
------------------------------ ------
DATA                           NORMAL

ORA-01031: insufficient privileges while connecting to database as sysdba

While I was trying to connect to the database, which was created recently, I was getting error as


#> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 21 02:52:18 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:
oracle@test.sangam /orasw/app/oracle/product/10.2.0/bin

Solution:


1. Check the  uid and gid of an oracle user. 

#> id oracle
uid=1014(oracle) gid=3005(oinstall) groups=100(users),3004(dba),3005(oinstall),8500(oemdba)

But the problem was db was created giving "users" as dbagroup.

You can check this in $ORACLE_HOME/rdbms/lib/config.c or $ORACLE_HOME/rdbms/lib/config.s file (depends on the OS type)

Eg:

#> cat config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "users"
#define SS_OPER_GRP "users"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Once file is updated, relink the oracle binaries as below
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
 $ORACLE_HOME/bin/relink all
writing relink log to: /oracle/product/11.2.0/install/relink.log

2.check the same thing exist in /etc/passwd file.

#> cat /etc/passwd |grep oracle
oraem:x:24872:8500::/opt/oracle/oraem:/bin/csh
oracle:x:1014:3005::/orasw:/usr/bin/ksh
root@test.sangam /orasw/app/oracle/product/10.2.0

3.Check the permissions of all oracle binaries.

4.Assign the "users" group gid to "oracle" by editing /etc/passwd file.

 #> cat /etc/passwd |grep oracle
oraem:x:24872:8500::/opt/oracle/oraem:/bin/csh
#oracle:x:1014:3005::/orasw:/usr/bin/ksh
oracle:x:1014:100::/orasw:/usr/bin/ksh
root@test.sangam /orasw/app/oracle/product/10.2.0

5. Now try to connect to the database. You would be able to connect to the database.


#> . oraenv
ORACLE_SID = [TESTDB] ?
oracle@test.sangam /orasw/app/oracle/product/10.2.0/bin
#> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 21 02:59:12 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2026224 bytes
Variable Size             272631056 bytes
Database Buffers          792723456 bytes
Redo Buffers                6361088 bytes
Database mounted.
Database opened.
SQL>

Error in invoking target 'client_sharedlib' of makefile '/u01/app/oracle/product/10.2.0.3/db_1/network/lib/ins_net_client.mk' while installing 10g database software

While installing oracle 10g software, It threw error as
"Error in invoking target 'client_sharedlib' of makefile '/u01/app/oracle/product/10.2.0.3/db_1/network/lib/ins_net_client.mk' "


Solution: Check the software you are using. Probably you are using 64 bit OS software for 32 bit operating system.

ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid]

Recently, all databases went down because of uid of "oracle" user was changed by system admin. When I tried to bring up the database, got error as

04:02:31 SYS@MDMDV7>startup;
ORA-27121: unable to determine size of shared memory segment
Linux-x86_64 Error: 13: Permission denied
04:02:35 SYS@MDMDV7>exit

and in the alter log, the error was


Errors in file /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/trace/MDMDV7_w000_27702.trc  (incident=79650):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1014], [54321], [], [], [], [], [], [], [], [], []
Incident details in: /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/incident/incdir_79650/MDMDV7_w000_27702_i79650.trc
Errors in file /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/incident/incdir_79650/MDMDV7_w000_27702_i79650.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1014], [54321], [], [], [], [], [], [], [], [], []
Process W000 died, see its trace file
Tue Mar 13 03:33:05 2012
Errors in file /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/trace/MDMDV7_w000_27708.trc  (incident=79658):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1014], [54321], [], [], [], [], [], [], [], [], []
Incident details in: /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/incident/incdir_79658/MDMDV7_w000_27708_i79658.trc
Errors in file /orasw/app/oracle/admin/MDMDV7/bdump/diag/rdbms/mdmdv7/MDMDV7/incident/incdir_79658/MDMDV7_w000_27708_i79658.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1014], [54321], [], [], [], [], [], [], [], [], []

and file permissions were being shown as

drwxr-xr-x 2  54321 oinstall       4096 Feb 26 23:40 DXTRNSL
drwxr-xr-x 2  54321 oinstall       4096 Mar  6 05:29 D72000I1
oracle@sfogbu015.us.oracle.com /mdm_oradata_02



Solution:
1. Change the file permission to oracle user

chown -R oracle:oinstall data_file_folder

chown -R oracle:oinstall $ORACLE_BASE folder


2.reboot the server with root user

#> reboot
Broadcast message from root (pts/6) (Tue Mar 13 04:23:04 2012):

The system is going down for reboot NOW!

3.Bring up the databases now

#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 13 04:18:44 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

04:18:47 SYS@MDMDV7>startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             457183064 bytes
Database Buffers          603979776 bytes
Redo Buffers                5554176 bytes
Database mounted.
Database opened.

How to check ORA Error on DB Server

To check ORA errors on database server itself, go to shell prompt and type as below.

oerr ora error_number

Eg:
$ oerr ora 00845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

How to Drop Database in Oracle 10g and onwards...

#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 8 23:22:42 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
Database mounted.
SQL> drop database;

Database dropped.

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

Oracle DBNEWID Utility

What Is the DBNEWID Utility

Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:

Both the DBNAME and DBID of a database

Only the DBNAME of a database
Only the DBID of a database



Consequences of Changing the DBID and DBNAME

 When the DBID of a database is changed, all previous backups and archived logs of the database become unusable.You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1

Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name.

Changing the DBID and Database Name

The following steps describe how to change the DBID and database name.

1.Take the Database backup

2.Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example:

SHUTDOWN IMMEDIATE
STARTUP MOUNT


#> . oraenv
ORACLE_SID = [TESTDB1] ?
The Oracle base remains unchanged with value /orasw/app/oracle
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product
#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:21:19 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
Database mounted.
SQL>


3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege

#> nid TARGET=SYS DBNAME=new_db_name

#> nid TARGET=SYS DBNAME=TESTDB2

DBNEWID: Release 11.2.0.3.0 - Production on Wed Mar 7 22:24:18 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:
Connected to database TESTDB1 (DBID=1076930572)

Connected to server version 11.2.0

Control Files in database:
    /test_oradata_01/TESTDB1/control01.ctl
    /test_oradata_01/TESTDB1/control02.ctl
    /test_oradata_01/TESTDB1/control03.ctl

Change database ID and database name TESTDB1 to TESTDB2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1076930572 to 1999354907
Changing database name from TESTDB1 to TESTDB2
    Control File /test_oradata_01/TESTDB1/control01.ctl - modified
    Control File /test_oradata_01/TESTDB1/control02.ctl - modified
    Control File /test_oradata_01/TESTDB1/control03.ctl - modified
    Datafile /test_oradata_01/TESTDB1/system01.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/SYSAUX.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/UNDOTBS1.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/tools01.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/cists01.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/user01.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/xmldbts01.db - dbid changed, wrote new name
    Datafile /test_oradata_01/TESTDB1/temp01.db - dbid changed, wrote new name
    Control File /test_oradata_01/TESTDB1/control01.ctl - dbid changed, wrote new name
    Control File /test_oradata_01/TESTDB1/control02.ctl - dbid changed, wrote new name
    Control File /test_oradata_01/TESTDB1/control03.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to TESTDB2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB2 changed to 1999354907.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Note: If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown below. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID

NID-00122: Database should have no offline immediate datafiles

Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.


4. Modify the parameter file and also password file if you are using password file.

Modify the below parameters

db_name = TESTDB2
instance_name = TESTDB2
service_names = TESTDB2


Rename init.ora file from initTESTDB1.ora to initTESTDB2.ora

Note: If you are using spfile, create spfile from pfile.

Reset the ORACLE_SID environment variable.


Note: If you don't modify the parameter file, you will get error as below


#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:28:26 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
ORA-01103: database name 'TESTDB2' in control file is not 'TESTDB1'


SQL> exit

#> . oraenv
ORACLE_SID = [TESTDB1] ? TESTDB2
ORACLE_HOME = [/home/oracle] ? /orasw/app/oracle/product/11.2.0.3
The Oracle base remains unchanged with value /orasw/app/oracle


4.Mount the database


#> . oraenv
ORACLE_SID = [TESTDB1] ? TESTDB2
ORACLE_HOME = [/home/oracle] ? /orasw/app/oracle/product/11.2.0.3
The Oracle base remains unchanged with value /orasw/app/oracle
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product/11.2.0.3/dbs
#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:42:29 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
Database mounted.

5.Open the database in RESETLOGS mode and resume normal use.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,status from v$database,v$instance;

NAME      STATUS
--------- ------------
TESTDB2  OPEN

Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.


To change DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility.

nid TARGET=sys/password@old_db_name DBNAME=new_db_name SETNAME=YES



To change DBID Only

1.Backup the database.


2.Mount the database after a clean shutdown
SHUTDOWN IMMEDIATE
STARTUP MOUNT

#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:53:36 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
Database mounted.
SQL> exit


3. Invoke the DBNEWID utility (nid) using a user with SYSDBA privilege. Do not specify a new DBNAME.

nid TARGET=sys/password@db_name

#> nid TARGET=SYS

DBNEWID: Release 11.2.0.3.0 - Production on Wed Mar 7 22:54:09 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:
Connected to database TESTDB2 (DBID=1999354907)

Connected to server version 11.2.0

Control Files in database:
    /test_oradata_01/TESTDB1/control01.ctl
    /test_oradata_01/TESTDB1/control02.ctl
    /test_oradata_01/TESTDB1/control03.ctl

Change database ID of database TESTDB2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1999354907 to 1999396119
    Control File /test_oradata_01/TESTDB1/control01.ctl - modified
    Control File /test_oradata_01/TESTDB1/control02.ctl - modified
    Control File /test_oradata_01/TESTDB1/control03.ctl - modified
    Datafile /test_oradata_01/TESTDB1/system01.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/SYSAUX.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/UNDOTBS1.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/tools01.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/cists01.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/user01.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/xmldbts01.db - dbid changed
    Datafile /test_oradata_01/TESTDB1/temp01.db - dbid changed
    Control File /test_oradata_01/TESTDB1/control01.ctl - dbid changed
    Control File /test_oradata_01/TESTDB1/control02.ctl - dbid changed
    Control File /test_oradata_01/TESTDB1/control03.ctl - dbid changed
    Instance shut down

Database ID for database TESTDB2 changed to 1999396119.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

4.Mount the database

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             402656720 bytes
Database Buffers         1191182336 bytes
Redo Buffers                7344128 bytes
Database mounted.


5.Open the database with resetlogs.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,status from v$database,v$instance;

NAME      STATUS
--------- ------------
TESTDB2  OPEN

"libXp.so.6: cannot open shared object file: No such file or directory" error while installing Oracle

We got below error while installing Oracle 10g on Oracle Enterprise Linux 5


libXp.so.6: cannot open shared object file: No such file or directory
While doing a fresh install of 11i vision on Oracle Enterprise Linux 5, RapidInstall gave the following error:

Rapid Install Wizard will now launch the Java Interface.....

Exception in thread "main" java.lang.UnsatisfiedLinkError: /11istage/startCD/Disk1/rapidwiz/jre/Linux/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.(Unknown Source)
        at java.awt.Component.(Unknown Source)

Solution:

This error comes because libXp.so.6 library is not present in /usr/lib.  The rpm which contains this file is libXp-1.0.0-8.i386.rpm.  It is present in the Oracle Enterprise Linux 5 DVD  in /server directory.  So copy this rpm from the DVD or CD and install it:

rpm -ivh libXp-1.0.0-8.i386.rpm

Once this is installed, confirm about the presence of the the file libXp.so.6 by this command:

$ ls -ld /usr/lib/libXp.so.6

lrwxrwxrwx 1 root root 14 Jul 29 06:45 /usr/lib/libXp.so.6 -> libXp.so.6.2.0

$ 

Deconfigure Enterprise Manager Database Control in 11g

We can deconfigure Enterprise Manager Database Control using below command.

Syntax:
emca -deconfig dbcontrol db -repos drop -SYS_PWD -SYSMAN_PWD


Eg:
$ emca -deconfig dbcontrol db -repos drop -SYS_PWD sys123 -SYSMAN_PWD sys123


STARTED EMCA at Feb 28, 2012 3:11:53 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: omsdb12c
Listener port number: 1521

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 28, 2012 3:12:06 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/cfgtoollogs/emca/omsdb12c/emca_2012_02_28_03_11_52.log.
Feb 28, 2012 3:12:06 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Feb 28, 2012 3:12:26 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Feb 28, 2012 3:13:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 28, 2012 3:13:32 AM