Pages

Monday 17 December 2012

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';

Sunday 16 December 2012

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)
$



Tuesday 11 December 2012

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 ~]$


Monday 10 December 2012

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.

Thursday 6 December 2012

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.