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

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'





[oracle@ora11 bin]$ ./crsctl stat res -t -init

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE OFFLINE ora11 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE ora11 STABLE

ora.asm

ONLINE ONLINE ora11 Started,STABLE

ora.ons

OFFLINE OFFLINE ora11 STABLE

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

Cluster Resources

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

ora.cssd

1 ONLINE ONLINE ora11 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE ora11 STABLE

ora.oraafi.db

1 ONLINE OFFLINE STABLE

ora.orabig.db

1 ONLINE OFFLINE STABLE

ora.oradb.db

1 ONLINE OFFLINE STABLE

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

[oracle@ora11 bin]$









Solution
----------

[oracle@ora11 bin]$ ./crsctl start resource -all

CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'ora11'

CRS-5702: Resource 'ora.asm' is already running on 'ora11'

CRS-5702: Resource 'ora.cssd' is already running on 'ora11'

CRS-5702: Resource 'ora.evmd' is already running on 'ora11'

CRS-2501: Resource 'ora.ons' is disabled

CRS-2672: Attempting to start 'ora.DATA.dg' on 'ora11'

CRS-2672: Attempting to start 'ora.diskmon' on 'ora11'

CRS-2676: Start of 'ora.diskmon' on 'ora11' succeeded

CRS-2676: Start of 'ora.DATA.dg' on 'ora11' succeeded

CRS-2679: Attempting to clean 'ora.oraafi.db' on 'ora11'

CRS-2679: Attempting to clean 'ora.orabig.db' on 'ora11'

CRS-2679: Attempting to clean 'ora.oradb.db' on 'ora11'

CRS-2681: Clean of 'ora.oraafi.db' on 'ora11' succeeded

CRS-2672: Attempting to start 'ora.oraafi.db' on 'ora11'

CRS-2681: Clean of 'ora.orabig.db' on 'ora11' succeeded

CRS-2672: Attempting to start 'ora.orabig.db' on 'ora11'

CRS-2681: Clean of 'ora.oradb.db' on 'ora11' succeeded

CRS-2672: Attempting to start 'ora.oradb.db' on 'ora11'

CRS-5010: Update of configuration file "/ora/oracle/app/oracle/product/12.1.0/grid/srvm/admin/oratab.bak.ora11" failed: details at "(:CLSN00011:)" in "/ora/oracle/app/oracle/product/12.1.0/grid/log/ora11/agent/ohasd/oraagent_oracle/oraagent_oracle.log"

　

CRS-2676: Start of 'ora.oraafi.db' on 'ora11' succeeded

CRS-5702: Resource 'ora.oraafi.db' is already running on 'ora11'

CRS-2676: Start of 'ora.oradb.db' on 'ora11' succeeded

CRS-2676: Start of 'ora.orabig.db' on 'ora11' succeeded

CRS-5702: Resource 'ora.orabig.db' is already running on 'ora11'

CRS-4000: Command Start failed, or completed with errors.

[oracle@ora11 bin]$

[oracle@ora11 bin]$


[oracle@ora11 bin]$ ./sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 29 02:32:07 2015

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

SQL> conn / as sysdba

Connected.

SQL> exit


[oracle@ora11 bin]$ ./crsctl stat res -t -init

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE ora11 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE ora11 STABLE

ora.asm

ONLINE ONLINE ora11 Started,STABLE

ora.ons

OFFLINE OFFLINE ora11 STABLE

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

Cluster Resources

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

ora.cssd

1 ONLINE ONLINE ora11 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE ora11 STABLE

ora.oraafi.db

1 ONLINE ONLINE ora11 Open,STABLE

ora.orabig.db

1 ONLINE ONLINE ora11 Open,STABLE

ora.oradb.db

1 ONLINE ONLINE ora11 Open,STABLE

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

[oracle@ora11 bin]$






 

USE_SID_AS_SERVICE_listener_name

Unable To Access EM Express For Non-CDB

Transport Database Over Network

Transportable Database

SQL Management Base Stores Plan Rows too

Solution To Performance Issue With In-database Archiving

Plug in 12c Non-CDB As PDB

Performance Issue With In-database Archiving

Enhancements To Partition Exchange Load

PDB cannot share CDB’s temporary tablespace

Optimizer_dynamic_sampling = 11

Improve Backup Performance Using In-database Archiving

In-database Archiving

Does PDB have an SPfile?

Display CDB/PDB Name In SQL Prompt

Display CDB/PDB Name In SQL Prompt

DDL Log Does Not Identify The Source PDB

Connecting To PDB’s With Same Name

Connecting to CDB/PDB – Set Container vs Connect

Clone An Existing PDB as Non-Sys User

Access Objects Of A Common User Non-existent In Root

In a multitenant environment, a common user is a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB). Common users can connect to the root and perform administrative tasks specific to the root or PDBs. There are two types of common users :

• All Oracle-supplied administrative user accounts, such as SYS and SYSTEM

•  User created common users- Their names  must start with C## or c##.

When a PDB having a user created common user is plugged into another CDB and the target CDB does not have  a common user with the same name, the common user in a newly plugged in PDB becomes a locked account.
To access such common user’s objects, you can do one of the following:

• Leave the user account locked and use the objects of its schema.
• Create a common user with the same name as the locked account

 

Standby Database in READ ONLY Mode

Standby Database in Read Only mode
----------------------------------------------


Purpose:

As explained before, a physical standby database can be toggled between recovery and read-only mode. If the database is currently in managed recovery mode, in order to open it for reporting, cancel the recovery and open it as read-only.


First of all we have to cancel the recover mode of standby database.

SQL> alter database recover managed standby database cancel;
Database altered.


Open the standby database in read only mode.

SQL> alter database open read only;
Database altered.


Once you finished your reporting u have to shutdown the standby database.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start up the database in mount mode.



SQL> startup mount
ORACLE instance started.
Total System Global Area 7348420608 bytes
Fixed Size                  2150272 bytes
Variable Size            1201614976 bytes
Database Buffers         6006243328 bytes
Redo Buffers              138412032 bytes
Database mounted.


You can start recovery mode again using below command.



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;



You can monitor the archive log files.



SQL> SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

ORA-01102 cannot mount database in exclusive

The Oracle docs note this on the ora-01102 error:

ORA-01102 cannot mount database in EXCLUSIVE mode
 

Cause: Some other instance has the database mounted exclusive or shared.

Action: Shut down the other instance or mount in a compatible mode.

 

 

 

 

 

POSSIBLE SOLUTION:

 

      Verify that the database was shutdown cleanly by doing the following:

1. Verify that there is not a "sgadef<sid>.dbf" file in the directory
"ORACLE_HOME/dbs".

% ls $ORACLE_HOME/dbs/sgadef<sid>.dbf

If this file does exist, remove it.

% rm $ORACLE_HOME/dbs/sgadef<sid>.dbf

2. Verify that there are no background processes owned by "oracle"

% ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix
command "kill". For example:

% kill -9 <Process_ID_Number>

3. Verify that no shared memory segments and semaphores that are owned
by "oracle" still exist

% ipcs -b

If there are shared memory segments and semaphores owned by "oracle",
remove the shared memory segments

% ipcrm -m <Shared_Memory_ID_Number>

and remove the semaphores

% ipcrm -s <Semaphore_ID_Number>

NOTE: The example shown above assumes that you only have one
database on this machine. If you have more than one
database, you will need to shutdown all other databases
before proceeding with Step 4.

4. Verify that the "$ORACLE_HOME/dbs/lk<sid>" file does not exist

5. Startup the instance

Solaris 10/Oracle: Fixing ORA-27102: out of memory Error

Solaris 10/Oracle: Fixing ORA-27102: out of memory Error

Symptom:

As part of a database tuning effort you increase the SGA/PGA sizes; and Oracle greets with an ORA-27102: out of memory error message. The system had enough free memory to serve the needs of Oracle.

SQL> startup
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument

Diagnosis

$ oerr ORA 27102
27102, 00000, "out of memory"
// *Cause: Out of memory
// *Action: Consult the trace file for details

Not so helpful. Let's look the alert log for some clues.

% tail -2 alert.log
WARNING: EINVAL creating segment of size 0x000000028a006000
fix shm parameters in /etc/system or equivalent

Oracle is trying to create a 10G shared memory segment (depends on SGA/PGA sizes), but operating system (Solaris in this example) responded with an invalid argument (EINVAL) error message. There is a little hint about setting shm parameters in /etc/system.

Prior to Solaris 10, shmsys:shminfo_shmmax parameter has to be set in /etc/system with maximum memory segment value that can be created. 8M is the default value on Solaris 9 and prior versions; where as 1/4th of the physical memory is the default on Solaris 10 and later. On a Solaris 10 (or later) system, it can be verified as shown below:

% prtconf | grep Mem
Memory size: 32760 Megabytes

% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Now it is clear that the system is using the default value of 8G in this scenario, where as the application (Oracle) is trying to create a memory segment (10G) larger than 8G. Hence the failure.

So, the solution is to configure the system with a value large enough for the shared segment being created, so Oracle succeeds in starting up the database instance.

On Solaris 9 and prior releases, it can be done by adding the following line to /etc/system, followed by a reboot for the system to pick up the new value.

set shminfo_shmmax = 0x000000028a006000
However shminfo_shmmax parameter was obsoleted with the release of Solaris 10; and Sun doesn't recommend setting this parameter in /etc/system even though it works as expected.

On Solaris 10 and later, this value can be changed dynamically on a per project basis with the help of resource control facilities . This is how we do it on Solaris 10 and later:

% prctl -n project.max-shm-memory -r -v 10G -i project 3

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      10.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Note that changes made with the prctl command on a running system are temporary, and will be lost when the system is rebooted. To make the changes permanent, create a project with projadd command and associate it with the user account as shown below:

% projadd -p 102  -c 'eBS benchmark' -U oracle -G dba  -K 'project.max-shm-memory=(privileged,10G,deny)' OASB
% usermod -K project=OASB oracle

Finally make sure the project is created with projects -l or cat /etc/project commands.

% projects -l
...
...
OASB
        projid : 102
        comment: "eBS benchmark"
        users  : oracle
        groups : dba
        attribs: project.max-shm-memory=(privileged,10737418240,deny)

% cat /etc/project
...
...
OASB:102:eBS benchmark:oracle:dba:project.max-shm-memory=(privileged,10737418240,deny)

With these changes, Oracle would start the database up normally.

SQL> startup
ORACLE instance started.

Total System Global Area 1.0905E+10 bytes
Fixed Size                  1316080 bytes
Variable Size            4429966096 bytes
Database Buffers         6442450944 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.

ORA-01031: insufficient privileges

ORA-01031: insufficient privileges


Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the appropriate privilege at a higher label, ask the database administrator to re-grant the privilege at the appropriate label. 
 


I resolved this issue to add oinstall group in /etc/group in solaris and oracle user part of this group.