How to change NLS_CHARACTERSET to AL32UTF8

Below are steps:

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT;
ORACLE instance started.

Total System Global Area 6.0663E+10 bytes
Fixed Size                  2268032 bytes
Variable Size            5637145728 bytes
Database Buffers         5.4895E+10 bytes
Redo Buffers              128344064 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

Database altered.

ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+ERP_DB/spfileerp.ora_1522172043297' ORA-17503: ksfdopn:10 Failed to open file +ERP_DB/spfileerp.ora_1522172043297 ORA-01034: ORACLE not available ORA-27121: unable to determine size of shared memory segment Linux-x86_64 Error: 13: Permission denied Additional information: 6761 Additional information: 60391427

Issue Type:

SQL> startup mount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+ERP_DB/spfileerp.ora_1522172043297'
ORA-17503: ksfdopn:10 Failed to open file +ERP_DB/spfileerp.ora_1522172043297
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux-x86_64 Error: 13: Permission denied
Additional information: 6761
Additional information: 60391427

Solution:
***********


To place your database in archivelog mode, perform the following steps:

  1. Check listener status.
  2. Check oracle utility permission for both users grid & oracle.
  3. Change permission of oracle utility for both users grid & oracle.
  4. Startup your database.

[grid@myserver]$ cd $GRID_HOME/bin
[grid@myserver]$ chmod 6751 oracle

[oracle@myserver]$ cd $ORACLE_HOME/bin
[oracle@myserver]$ chmod 6751 oracle

Process (index=2,uid=1039335819,pid=14570) time out while waiting for a managed process to stop Log: /opt/erp/product/inst/apps/erp1/logs/ora/10.1.3/opmn/default_group~oafm~default_group~2.log

Solution:

$ $ORACLE_HOME/opmn/bin/opmnctl shutdown
opmnctl: opmn is not running
 
$ cd $ORACLE_HOME/opmn/logs
$ mkdir oracle_backup
$ mv *.* oracle_backup

 cd $ADMIN_SCRIPTS_HOME

# adapcctl.sh start

Issue is fixed.

GI ocssd.log rotation fails and logfile grows to huge size

Solution 1:

The CSSD thread that encountered the LFI-00142 error needs to be restarted to ensure log rotation works again.

Manually deleting the logfile will not resolve the log rotation problem.

1).  Shutdown CRS on the node reporting the problem.

# crsctl stop crs

2).  Once CRS is down,  proceed to manually delete the 'ocssd.l10' file, or copy the logfile to another location if you need to keep a backup.

# rm  $GRID_HOME/log/<hostname>/cssd/ocssd.l10

3).  Startup Clusterware again

# crsctl start crs

Solution 2:

If you don't get downtime, then follow below step. But it is not sure whether issue will resolve.

% echo 0 > ocssd.l10

Please note this does not resolve the log rotation problem but only allows you to free up some space.

Connection issue from

While connecting to Database from client or using DB link if you face below issue , Please follow below mentioned steps.

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

I wrote this piece after a team of operation at my workplace required a partial database. When I prepared what they wanted and sent them a TNS to connect, they informed me that they could not  connect because of our login trigger.

That was quite easy to understand the problem. Our login trigger was recording sessions in a table that resides on a tablespace that I have not restored =)

If you wat to disable all the system triggers in your database, you must set an undocumented parameter in your parameter file as below:

_system_trig_enabled=FALSE

 alter system set "_system_trig_enabled"=FALSE; --- On Database side.

Thanks.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch Log file:

 Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5931)
StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:644)
StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1088)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
StackTrace: java.lang.reflect.Method.invoke(Method.java:592)
StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:317)
StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2581)
StackTrace: oracle.opatch.OPatch.main(OPatch.java:626)


Cause:

Linux/Unix:

According to the above  error message, oracle binary ($ORACLE_HOME/bin/oracle) is still active or in use.
It can also  fail with other ORACLE_HOME active files/libraries .
Example:
/u01/app/oracle/10.2.0/bin/tnslsnr
/u01/app/oracle/11.2.0.3/lib/libclntsh.so.11.1

Windows:

Please be aware that before applying patches, you must shutdown the database, listener, sqlplus, EM dbconsole/dbcontrol and any and all processes, including 3rd-party processes, which use files in the ORACLE_HOME.  The error messages in the OPatch log files show that there are one or more programs still running that are using files in the ORACLE_HOME you are trying to patch.

Linux/Unix:

Verify and ensure that all database instances/services that are running from the $ORACLE_HOME being patched are cleanly shutdown.
Also  identify the process that is using these active files/libraries. Then stop the process who is still accessing it and then apply/rollback the patch.

To identify active process, execute

$ fuser -c $ORACLE_HOME

Windows:

If the database and Oracle services are already shutdown, then perhaps it is a 3rd-party program.  If you do not already have it, you should consider installing "Process Explorer" from Microsoft.  After starting procexp.exe, press Ctl-F (Find) and type in one of the DLLs from the "active" list (in the OPatch log). For this example, based on the "active" list shown above, the first DLL to check is orageneric11.dll.  That should tell you what process is using that DLL so you can shut it down. Process Explorer is fairly easy to use, but if you need help with downloading or using it, please contact Microsoft Support as usage of Process Explorer is outside the scope of Oracle Support.

There are a variety of non-Oracle 3rd-party programs which could be using files in the ORACLE_HOME.  One culprit is WMI (Windows Management Instrumentation).  Once you shutdown the 3rd-party process(es) using files in the ORACLE_HOME, and Process Explorer comes back clean, then attempt to apply the patch again and the OUI-67074 error should not reoccur.

Thanks.

How to check Oracle Application URL in 11i and R12 on UNIX prompt

Connect to Application tier with applmgr account.

[appl@prapp1 ~]$cat SID_hostnameapp1.xml| grep http | grep login

How to find Oracle Applications Login URL from database

Execute either of the following query logging into Oracle Applications database from "APPS" user:-

1.
SELECT home_url
  FROM icx_parameters;


2.
SELECT profile_option_value
  FROM fnd_profile_option_values
 WHERE profile_option_id=
   (SELECT profile_option_id                          
      FROM fnd_profile_options WHERE profile_option_name ='APPS_FRAMEWORK_AGENT')                            
   AND level_value = 0;

Thanks

ORA-00020: maximum number of processes (1000) exceeded

ORA-00020: maximum number of processes (1000) exceeded

Alert log:

ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Dec 29 22:48:29 2014
ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Dec 29 22:52:32 2014
ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

Solution:

If you cannot connect to the sqlplus traditinal method use prelim option. This is one of the bypass option to connect the sqlplus.

sqlplus -prelim is a backdoor method to connect the sqlplus where no othere method not supporting to create new session. It will connect only SGA and it will not create any new session.

$ sqlplus -prelim

oradb@CGABCDRADBOP01:/oracle_home/app $ sqlplus -prelim

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 30 13:04:12 2014

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

Enter user-name: / as sysdba
 
SQL>

Bounce the DB and  you can increase the processes with below options.


processes=x
sessions=x*1.1+5
transactions=sessions*1.1
sql> show parameter sessions

sql> show parameter processes
sql> show parameter transactions
sql> alter system set processes=500 SCOPE=SPFILE; 
sql> alter system set sessions=555 SCOPE=SPFILE; 
sql> alter system set transactions=610 SCOPE=SPFILE; 

and Restart the database.

Below parameters values also must be changed.....

sql>alter system set open_cursors=1500 scope=both;
sql>alter system set processes=3000 scope=spfile;
sql>alter system set job_queue_processes=4 scope=both;
sql>alter system set undo_retention=10800 scope=both;
sql>alter system set max_shared_servers=50 scope=both;
sql>alter system set db_files=2000 scope=spfile; 
sql>alter system set sessions=3305 SCOPE=SPFILE;  
sql>alter system set transactions=3635 SCOPE=SPFILE; 

Issue get fixed.

1. After you connect with “-prelim” mode, it’s also possible to use oradebug commands to examine the situation. 

2. It will not create new session and connecting only SGA. So you can performance all the oradebug utilities to analyse the issue.
 

List Session ID for a Given OS Process ID

-- -- List Session ID for a Given OS Process ID. --


 SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT
   CHR(10)||
'Check for SESSION ID ----> '||LPAD( s.sid, 4 )||CHR(10)||CHR(10) as "Session ID"
FROM   
v$session s, v$process p
WHERE   
p.addr = s.paddr
AND   
p.spid = &Enter_OSPid /


========================================


SELECT
a.sid,
b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
and b.spid = 1234;

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

test db$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.6

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

 

 

Oracle Home       : /test_bin/prod/db/tech_st/11.2.0.4

Central Inventory : /home/test/oraInventory

   from           : /test_bin/prod/db/tech_st/11.2.0.4/oraInst.loc

OPatch version    : 11.2.0.3.6

OUI version       : 11.2.0.4.0

Log file location : /test_bin/prod/db/tech_st/11.2.0.4/cfgtoollogs/opatch/opatch2014-10-20_05-31-01AM_1.log

 

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

 

 

Following executables are active :

/test_bin/prod/db/tech_st/11.2.0.4/lib/libclntsh.so.11.1

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /test_bin/prod/db/tech_st/11.2.0.4/cfgtoollogs/opatch/opatch2014-10-20_05-31-01AM_1.log

 

OPatch failed with error code 73

Description:

Your Listener is in active status. It means Listener is up. So ensure that  Instance & Listener must be down when applying the Opatch.

After bounce the Listener try the opatch apply steps.

Thanks..

ORA-00844: Parameter not taking MEMORY_TARGET into account ORA-00851: SGA_MAX_SIZE ****** cannot be set to more than MEMORY_TARGET ********. ORA-01078: failure in processing system parameters

SQL> startup ;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2449473536 cannot be set to more than MEMORY_TARGET 855638016.
ORA-01078: failure in processing system parameters;



[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 15 17:51:47 2014

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

SQL> conn sys/admin@orcl as sysdba

Connected to an idle instance.

 SQL> create pfile='/u01/app/oracle/product/pfile.ora' from SPFILE='+DATA/orcl/spfileorcl.ora';

File created.

Note: switch to grid user and connect ASM instance (asmcmd) find the spfile location.

Go to pfile location and remove below lines;

*.sga_max_size=123456789

*.sga_target=0

save the file.

[oracle@rac1 product]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 15 18:26:55 2014

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

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/pfile.ora';

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1348244 bytes

Variable Size             616566124 bytes

Database Buffers          226492416 bytes

Redo Buffers                5124096 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile='/u01/app/oracle/product/pfile.ora';

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[oracle@rac1 product]$ srvctl start database -d ORCL
[oracle@rac1 product]$ srvctl status database -d ORCL
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2
[oracle@rac1 product]$
[oracle@rac1 product]$


Thank you...

How to configure Oracle Enterprise Manager on two node cluster

[oracle@rac2 cfgtoollogs]$  emca -reconfig dbcontrol -cluster -EM_NODE rac2 -EM_NODE_LIST rac2

STARTED EMCA at Jul 15, 2014 3:04:51 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: orcl
Service name: orcl
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 15, 2014 3:04:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/em                                                                                        ca_2014_07_15_15_04_51.log.
Jul 15, 2014 3:05:03 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 15, 2014 3:05:05 PM oracle.sysman.emcp.EMAgentConfig performDbcReconfigurati                                                                                        on
INFO: Propagating /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl/sysman/confi                                                                                        g/emd.properties to remote nodes ...
Jul 15, 2014 3:05:05 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 15, 2014 3:06:21 PM oracle.sysman.emcp.EMDBPostConfig performDbcReconfigurat                                                                                        ion
INFO: Database Control started successfully
Jul 15, 2014 3:06:21 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMes                                                                                        sage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

orcl              rac1              rac1.localdomain
orcl              rac2              rac2.localdomain


Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 15, 2014 3:06:21 PM
[oracle@rac2 cfgtoollogs]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://rac2.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orc                                                                                        l/sysman/log
[oracle@rac2 cfgtoollogs]$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.4
OMS Version       : 10.2.0.4.4
Protocol Version  : 10.2.0.4.4
Agent Home        : /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl
Agent binaries    : /u01/app/oracle/product/11.2.0/dbhome_1
Agent Process ID  : 9325
Parent Process ID : 9303
Agent URL         : https://rac2.localdomain:3938/emd/main
Repository URL    : https://rac2.localdomain:1158/em/upload/
Started at        : 2014-07-15 15:05:07
Started by user   : oracle
Last Reload       : 2014-07-15 15:05:07
Last successful upload                       : 2014-07-15 15:10:36
Total Megabytes of XML files uploaded so far :    48.62
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    39.61%
Data channel upload directory                : /u01/app/oracle/product/11.2.0/db                                                                                        home_1/rac2_orcl/sysman/recv
Last successful heartbeat to OMS             : 2014-07-15 15:10:21
---------------------------------------------------------------
Agent is Running and Ready

Check the first node :

Node 1:

[oracle@rac1 emca]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://rac1.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac1_orcl/sysman/log

Node 2: 

[oracle@rac2 cfgtoollogs]$ emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

https://rac2.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.

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

Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl/sysman/log

[oracle@rac2 cfgtoollogs]$

This is indicates two Grid controls for two individual node with 2 instance.


Thank you...

Metadata file does not match checksum Trying other mirror.

Error message

http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/repodata/filelists.xml.gz: [Errno -1] Metadata file does not match checksum
Trying other mirror.
public_ol6_latest/primary                                |  39 MB     01:48
http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/repodata/primary.xml.gz: [Errno -1] Metadata file does not match checksum
Trying other mirror.
Error: failure: repodata/primary.xml.gz from public_ol6_latest: [Errno 256] No more mirrors to try.

Solution :

I have seen the "Metadata file does not match checksum" error once too and was able to resolve it by:

1) Setting http_caching=none in /etc/yum.conf file

2) yum clean metadata

3) yum update

It will work fine......

Thank you.......

Database Recovery with RMAN backup (Disaster Recovery)

Database Recovery with RMAN backup (Disaster Recovery)

This are the steps for Database recovery when entire complete database crashed even though don’t have spfile and control file backup. I have one copy of Full RMAN backup. Using this backup I am going to be performing the recovery scenario.

Here in this case required to rebuild entire database server

Environment is  OEL 6.5 Oracle 11g with ASM standalone server.

RMAN Backup:

1)      Controlfile and SPFILE autobackup

2)      RMAN Full Backup files

3)      RMAN Archivelog files

Steps:

1)      Create new file system same as old server.

2)      Install same version of Oracle Software in the production server.(In this case 11.2.0.3 Enterprise Edition).

3)      Create the Instance.

4)      Create the relevant folder hierarchy under <ORACLE_BASE> directory.

5)      Restore the SPFILE from autobackup, and use the SPFILE to start the instance in NOMOUNT state.

6)      Restore the controlfile from autobackup, and MOUNT the database .

7)      Restore the Database from RMAN backup files .

8)      Recover the Database from RMAN backup files, and OPEN the database with RESETLOGS.

Database Disaster Recovery using only RMAN Backups

Note: After installation of same version of Oracle software below are the steps to be followed.

[oracle@linux dbs]$ set ORACLE_SID=ORCL

[oracle@linux dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 20 19:04:55 2014

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

connected to target database (not started)

RMAN> set DBID=1372442605

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora_2'

ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora_2

ORA-15056: additional error message

ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora_2

ORA-15173: entry 'spfileorcl.ora_2' does not exist in directory 'orcl'

ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                          2226456 bytes

Variable Size                104859368 bytes

Database Buffers          46137344 bytes

Redo Buffers                    5439488 bytes

RMAN>

[oracle@linux dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 20 19:11:56 2014

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

connected to target database: DUMMY (not mounted)

RMAN> set DBID=1372442605

executing command: SET DBID

RMAN> restore spfile to '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initasm.ora' from "+DATA/ORCL/AUTOBACKUP/2014_06_20/s_850757433.294.850757669";

Starting restore at 20-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=172 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA/ORCL/AUTOBACKUP/2014_06_20/s_850757433.294.850757669

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 20-JUN-14

RMAN> shutdown immediate;

Oracle instance shut down

RMAN>

(Or )

2^nd Method :

I am using my pfile for temporarily to start the Instance  Based on my test server. Below is my pfile script

orcl.__db_cache_size=3305111552

orcl.__java_pool_size=50331648

orcl.__large_pool_size=16777216

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=22598909952

orcl.__sga_target=4294967296

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=855638016

orcl.__streams_pool_size=33554432

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/orcl/controlfile/current.261.845224563','+DATA/orcl/controlfile/current.260.845224563'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.open_cursors=300

*.pga_aggregate_target=22595764224

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=4293918720
*.undo_tablespace='UNDOTBS1'

RMAN> startup force pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initasm.ora' nomount;

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes

Variable Size                956302520 bytes

Database Buffers            3305111552 bytes

Redo Buffers                  12132352 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 20-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=135 device type=DISK

recovery area destination: +DATA

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: AUTOBACKUP +data/ORCL/AUTOBACKUP/2014_06_20/s_850757433.294.850757669 found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140620

channel ORA_DISK_1: restoring control file from AUTOBACKUP +data/ORCL/AUTOBACKUP/2014_06_20/s_850757433.294.850757669

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=+DATA/orcl/controlfile/current.272.850764747

output file name=+DATA/orcl/controlfile/current.275.850764749

Finished restore at 20-JUN-14

RMAN>

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 20-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.270.850764909

channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.273.850764909

channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.261.850764909

channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.277.850764911

channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/datafile/example.281.850764909

channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2014_06_20/nnndf0_tag20140620t173119_0.295.850757481

channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2014_06_20/nnndf0_tag20140620t173119_0.295.850757481 tag=TAG20140620T173119

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:03:25

Finished restore at 20-JUN-14

RMAN> recover database;

Starting recover at 20-JUN-14

using channel ORA_DISK_1

starting media recovery

RMAN-08187: WARNING: media recovery until SCN 2293142 complete

Finished recover at 20-JUN-14

RMAN> alter database open resetlogs;

database opened

RMAN>

Exit RMAN prompt and check Database recovered properly or not.

Thank you for visiting my blog

Steps for media recovery:

Steps for media recovery:

Restore and recover the whole database

 RMAN> STARTUP FORCE MOUNT;
 RMAN> RESTORE DATABASE;
 RMAN> RECOVER DATABASE;
 RMAN> ALTER DATABASE OPEN;
 
Restore and recover a tablespace

 RMAN> SQL 'ALTER TABLESPACE AXPERT OFFLINE';
 RMAN> RESTORE TABLESPACE AXPERT;
 RMAN> RECOVER TABLESPACE AXPERT;
 RMAN> SQL 'ALTER TABLESPACE AXPERT ONLINE';

Restore and recover a datafile

 RMAN> SQL 'ALTER DATABASE DATAFILE 8 OFFLINE';
 RMAN> RESTORE DATAFILE 8;
 RMAN> RECOVER DATAFILE 8;
 RMAN> SQL 'ALTER DATABASE DATAFILE 8 ONLINE';

Steps to be Performed for tablespace recovery:

1. Mount the database

2. Make the data file offline

3. Restore the data file

4. Recover the data file

5. Make the data file online

6. Open the database

Thanks...

ORA-12516: TNS:LISTENER COULD NOT FIND AVAILABLE HANDLER WITH MATCHING PROTOCOL STACK

Dear Friends,

Below are steps for ORA-12516: TNS:LISTENER COULD NOT FIND AVAILABLE HANDLER WITH MATCHING PROTOCOL STACK error

ERROR : ORA-12516: TNS:listener could not find available handler with matching protocol stack

CAUSE : Its Reached maximum process.

SOLUTION : 

Check maximum value for processes and sessions

select name, value from v$parameter where name in (‘processes’,’sessions’);

Check current process and session and maximum limitation

select * from v$resource_limit;

Increase your process and session, for example to 800

alter system set processes=800 scope=spfile;

alter system set sessions=885 scope=spfile;

Restart the Database with below commands,

shutdown immediate;

and 

startup;

or 

startup force ;

all changes will effected permanently.