ORA-01093 Close of standby Database

This problem usually occurs in Oracle database 10.1.0.2 to 10.2.0.3. However, this problem also exist in 11gR1 and 11gR2 database and the reason is different. During switchover from Primary to Physical Standby database ORA-1093 error occurs.

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby;
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

In case of 10g database you will see switchover status as Session Active.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Cause of the Problem
This error can also occur when any standby database registered with the Oracle Clusterware is being closed.

1) ALTER DATABASE CLOSE
2) Running RECOVER MANAGED STANDBY DATABASE on a standby that was previously opened in READ ONLY mode
 
Solution of the Problem
If the error occurs during switchover in a Data Guard environment, simply re-run the switchover command with the additional clause WITH SESSION SHUTDOWN.

SQL> alter database commit to switchover to {new database role} WITH SESSION SHUTDOWN;

Otherwise stop racgimon using the following command in the database ORACLE_HOME on a single node:

% racgimon stopd {db_name}

Re-issue the original command that failed.

Read More

ORA-32018: Parameter Cannot be modified in memory on another instance

ORA-32018: parameter cannot be modified in memory on another instance
 

Cause: Parameter adjustment can take a very long time
 

Action: Modify the parameter individually on each instance using the SID clause of the alter system command

Problem Description
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.

SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.

Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,

SQL> show parameter instance

NAME                                            TYPE      VALUE
------------------------------------       -------------- ------------------------------
active_instance_count              integer
cluster_database_instances      integer          3
instance_groups                      string
instance_name                        string            3
instance_number                      integer         3
instance_type                          string         
open_links_per_instance           integer         4
parallel_instance_group             string
parallel_server_instances          integer         3

From the parameter value we see we are in 3 nodes RAC database and our instance name is ORA3. So set the parameter in our current instance by specifying a SID value.

SQL> alter system set streams_pool_size=100M sid='ORA3';

System altered.

Similarly set it on rest of the instances ORA2 and ORA1 for example.


SQL> alter system set streams_pool_size=100M sid='ORA2';

System altered.


SQL> alter system set streams_pool_size=100M sid='ORA1';

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.

Read More

Types of problem that can occur during Media Recovery

Media recovery encounters a problem while recovering a database backup. There are some problems occur during media recovery.

1) Missing ArchivedLog:
In this database cannot find the archived log recorded in the control file so it stops the recovery.

2) When you attempt to open the database, error ORA-01113 Indicates that a datafile needs media recovery:  

- You are performing incomplete recovery but failed to restore all needed datafile backups. 

- Incomplete recovery stopped before datafiles reached a consistent SCN. 

- You are recovering datafiles from an online backup, but not enough redo was applied to make the datafiles consistent. 

- You are performing recovery with a backup control file, and did not specify the location of a needed online redo log. 

- A datafile is undergoing media recovery when you attempt to open the database. 

- Datafiles needing recovery were not brought online before executing RECOVER DATABASE, and so were not recovered.

 3) Corrupted archived logs:
Logs may be corrupted while they are stored on or copied between storage systems. If DB_BLOCK_CHECKSUM is enabled, then the database usually signals checksum errors. If checksumming is not on, then log corruption may appear as a problem with redo.

4) Redo record problems:
- In this, stuck recovery problem occur because of failed consistency checks thats why it stops recovery.
- The database signals an internal error when applying the redo. This problem can be caused by an Oracle bug. If checksums are not being used, it can also be caused by corruptions to the redo or data blocks.

5) Corrupted data blocks:
A datafile backup may have contained a corrupted data block, or the data block may become corrupted either during recovery or when it was copied to the backup. If checksums are being used, then the database signals a checksum error. Otherwise, the problem may also appear as a redo corruption.

6) Random problems:
Memory corruptions and other transient problems can occur during recovery.

Read More

ORA-00001 unique constraint (string.string) violated

ORA-00001 unique constraint (string.string) violated
 

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
 

Action: Either remove the unique restriction or do not insert the key.

• Drop the unique constraint
• Change the constraint to allow duplicate values
• Modify your SQL so that a duplicate value is not created

If you are not sure which unique constraint was violated, you can run the following SQL:

    select distinct table_name
    from all_indexes
    where index_name = 'CONSTRAINT_NAME';

Read More

ORA-16086 standby database does not contain available standby logfiles

Cause: The primary database is in "no data loss" mode, but the standby database does not contain any "standby logfiles".

Action: Add one or more standby logfiles to the standby database. This can be done while the standby database is mounted.
Increase the recovery area size of standby database. You can check the location of archived redo log file by,
SQL> connect / as sysdba
SQL> archive log list

If you see that archive destination is USE_DB_RECOVERY_FILE_DEST then the location will be specified by parameter DB_RECOVERY_FILE_DEST.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
+RECOVERY

Note that you have sufficient storage under +RECOVERY and also you have set proper size of the parameter db_recovery_file_dest_size.

To check the size of parameter db_recovery_file_dest_size issue,
SQL> show parameter db_recovery_file_dest_size

In order to increase the size of db_recovery_file_dest_size parameter issue,
SQL> alter system set db_recovery_file_dest_size = 1000G scope=both sid='*';

Read More

ORA-19555 invalid LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value

Cause: The value of parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was not set within the valid range.

Action:Specify a correct value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST.If the archive log parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST are in use, set parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to either 1 or 2.

Read More

ORA-00207 controlfiles are not for the same database

Cause: The database ID in the control file is not the same as the database ID in the control file used by the first instance to mount this database. Most likely one of the mounts used the wrong control file or there are two databases with the same name.

Action: Check that the control file is for the correct database and is not an old
version. When using multiplexed control files, that is, more than one control file
is referenced in the initialization parameter file, remove the control file name
listed in the message from the initialization parameter file and restart the
instance. If the message does not recur, remove the problem control file from the
initialization parameter file and create another copy of the control file using a
new file name in the initialization parameter file

Read More

ORA-12906 cannot drop default temporary tablespace

If you try to drop default temporary tablespace of database as it will result in error as:-

SQL>SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES;
TABLESPACE_NAME
---------------
TEMP

SQL>alter tablespace temp offline;
alter tablespace temp offline
*
Error at line 1;
ORA-12905:default temporary tablepspace cannot be offline

SQL>drop tablespace temp including contents and datafiles;
drop tablespace TEMP including contents and datafiles
*
Error at line2:
ORA-12906:cannot drop default Temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one. To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,

SQL>create temporary tablespace temp1 tempfile'/opt/nit/temp01/dbf' size 10M;
 Tablespace created.

Make this tablespace to database default temporary tablespace,
SQL>alter database default temporary tablespace temp1;
Database altered.

SQL>drop tablespace temp including contents and datafiles;
Tablespace dropped. 

Read More

Solution Of Startup Files With An Error ORA-00119 and ORA-00132

Error Description: Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'


Cause of The problem: If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.

PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.

Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.

Solution of The problem 

A)Correct the tnsnames.ora

i)Determine if the tns alias is good by using tnsping.

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name

So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')

and run tnsping utility,

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters

So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.

SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))

iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.


B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;

2)Open the pfile and remove the LOCAL_LISTENER entry.

$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora

3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;

4)Start the database.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.

In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';

In my system,
SQL>ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';

SQL> show parameter local

NAME TYPE VALUE 

local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))

Read More

Solution OF An Error ORA-27102

Scenario of The problem: 

In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,

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

Reason of The problem:

The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.

Solution of The problem:

1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,

SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.

SQL> alter system set sga_target=1600M;
System altered.

SQL>shutdown

Now set ORACLE_SID and start the instance.

bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;

And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.

And start the database with the pfile.

SQL>STARTUP PFILE='pfile_name';

Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;

Read More

My oracle primary server is crashed, then how to switch all users to standby oracle database ? also how to rebuild primary database using standby database ?

SQL>RECOVER STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Activate standby database:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Check New Status from V$DATABASE VIEW .
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;
Open Database
SQL> ALTER DATABASE OPEN;
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

Read More