Showing posts with label ERROR. Show all posts
Showing posts with label ERROR. Show all posts

Sunday 4 October 2015

SHUTDOWN: waiting for active calls to complete.

Issue:

Active call for process 1993 user ‘oraprod’ program ‘oracleprod@prod
Active call for process 18672 user ‘oraprod’ program ‘oracleprod@prod’
Active call for process 19304 user ‘oraprod’ program ‘oracleprod@prod’
SHUTDOWN: waiting for active calls to complete.
Problem: When SHUTDOWN IMMEDIATE HUNG with above message means PMON process is unable to clear any uncommitted transaction and oracle hung for long time.
Solution:
1. Connect to SYS user with new terminal window
SQL> conn / sysdba
Connected to idle instance.
2. Shutdown database with ABORT option
SQL> shutdown abort;
ORACLE instance shut down.
3. Open database for normal shutdown with RESTRICT option
SQL> startup restrict
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 149682160 bytes
Database Buffers 16777216 bytes
Redo Buffers 524288 bytes

Database mounted.
Database opened.
4. Now shutdown database with NORMAL option
SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.







I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!


Monday 28 September 2015

ora-01244: unnamed datafile(s) added to control file by media recovery

Cause: Media recovery with a backup control file or a control file that was rebuilt, encountered the creation of a datafile that was not in the control file. An entry has been added to the control file for the new datafiles, but with the file name UNNAMEDnnnn, where nnnn is the file number. Attached errors describe the file names that were originally used to create the files.


Action: Rename the files to valid file names and resume recovery. If necessary the command ALTER DATABASE CREATE DATAFILE may be used to create a file suitable for recovery and do the rename. If the file is not going to be recovered then take it offline with the FOR DROP option.
You'll encouter below error in alert log:
Errors in file /u01/app/11.2.0.3/diag/rdbms/odsprod/odsprod/trace/odsprod_pr00_16095.trc:
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 684: '/OSP/Data45/osp/data/tblspc_osp_686.dbf'
Slave exiting with ORA-283 exception

And your database recovery will abort
Now there can be a couple of scenarios, where in you may encounter this error, and their respective solutions .

1.You restored a full database and are recovering it through archives from source

Here, you are recovering after a full restore and hence the error says that the required data file doesn't exist.
To over come this issue, you simply have to execute below SQL:
alter database create datafile file#_from_error as '/new/path/to/data/file.dbf';
So, in our case that would be:
SQL> alter database create datafile 684 as '/bkp_loc/OSP/data/tblspc_osp_686.dbf';
The path is where you want the data file to be created. This can be similar to the one reported in error (if you have same path as source) or could be different, as in this case.
Also, if you are using ASM or to create Oracle managed datafile name
SQL> alter database create datafile 684 as new;
Now, you can continue with your recovery.

2.You lost a control file and restored it from a backup after which you had added data files to the database

Here, the data files already exist, (but they didn't at the time you backed up control file) but just that the restored control file is from time before the data files were added and hence not aware of them.
So, in this case, we can just rename the file to its existing file:

SQL> alter database rename file '/u01/app/product/11.2.0/dbhome_1/dbs/UNNAMED00684' to '/OSP/Data45/osp/data/tblspc_osp_686.dbf';

While renaming or recreating, just ensure that the target file being created doesn't already exist or if renaming, you aren't renaming it to other existing data files.
In any case if you know that you dont need that file, you can mark the same offline




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!


Friday 10 April 2015

ORA-01031: insufficient privileges while configure Enterprise Manager

Configuring OEM manually seems to be very straight forward but sometimes you may come across erors which make it difficult.
Below is one of the problem and its solution which I faced last week.
[oracle@PSNA-UAT-DBO001(VOTEUT01):~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:53:32 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: VOTEUT01
Listener port number: 1571
Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1
ASM SID [ +ASM ]:
ASM port [ 1571 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com
Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1
Listener port number ................ 1571
Database SID ................ VOTEUT01
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1
ASM SID ................ +ASM
ASM port ................ 1571
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 6, 2015 4:54:32 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log.
Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: ORA-01031: insufficient privileges

Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.EMConfig perform
SEVERE:

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

Some of the possible reasons may be:

1) Listener port 1571 provided is incorrect. Provide the correct port.
2) Listener is not up. Start the Listener.
3) Database service VOTEQA01 is not registered with listener. Register the database service
4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.
5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.
6) /etc/hosts does not have correct entry for hostname.

Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.
Could not complete the configuration. Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.


You may face this issue because OS user not added to onistall or dba group.but in my case it was there.
After researching I found the reason was missing password file.So to resolve it I have created new password file and re-ran the command and it completed successfully.

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$orapwd file=orapw$ORACLE_SID password=oracle entries=1 force=y

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:58:39 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: VOTEUT01
Listener port number: 1571
Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1
ASM SID [ +ASM ]:
ASM port [ 1571 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com
Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1
Listener port number ................ 1571
Database SID ................ VOTEUT01
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1
ASM SID ................ +ASM
ASM port ................ 1571
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 6, 2015 4:59:28 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_58_39.log.
Apr 6, 2015 4:59:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 6, 2015 5:01:54 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Apr 6, 2015 5:01:55 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...

Apr 6, 2015 5:08:59 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 6, 2015 5:09:01 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 6, 2015 5:09:54 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 6, 2015 5:09:55 AM oracle.sysman.emcp.ParamsManager getLocalListener
WARNING: Error retrieving listener for PSNA-UAT-DBO001.ad.issgovernance.com
Apr 6, 2015 5:09:57 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://PSNA-UAT-DBO001.ad.issgovernance.com:5500/em <<<<<<<<<<<
Apr 6, 2015 5:10:32 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1/PSNA-UAT-DBO001.ad.issgovernance.com_VOTEQA01/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully






I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Wednesday 1 April 2015

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, ORA-29283: invalid file operation

When doing export occur above error, let’s check directories:
SQL> select directory_name, directory_path from dba_directories
DIRECTORY_NAME     DIRECTORY_PATH
——————————————————————————–
DUMP_DIR       /u02/dump_dir


Two thing’s that hadn’t done is:

1) given correct permissions for that user to acccess the logical directory for export:
SQL> GRANT read, write on directory dump_dir TO ika; <-- username
2) create the physical directory
[oracle@oel6]$ mkdir -p /u02/dump_dir
After that our export run successfully;



I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thursday 19 February 2015

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

While RMAN full backup, you may encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

Error log:

[oracle@NVMBD1BZY150D00 ~]$ rman target / cmdfile=bkp_inc_lev0.rcv

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 20 12:31:30 2015

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

connected to target database: CLONEDB (DBID=958582256)

RMAN> run
2> {
3> backup incremental level 0 database tag "level 0 "  plus archivelog delete input tag "ARCHIVE BACKUP";
4> }
5>
6>

Starting backup at 20-FEB-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=176 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/20/2015 12:31:31
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /data1/clonedb/archive/1_50_872092231.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.



Cause:

This can happen for a variety of reasons; the file has been manually moved or deleted,
the archive log destination has recently been changed,
the file has been compressed, etc

Solution:

Your options are either to restore the missing file(s), or to perform a crosscheck.
To perform a crosscheck, run the following command from within RMAN:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Above command will give information of expired RMAN repository after verification. (i.e. in catalog or controlfile)

Above will marked archives as Expired who are not available physically and who are not required for any kind of recovery.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Above command will delete all archive logs who are marked as expired while crosscheck.





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Friday 28 November 2014

How to re-open expired oracle database account without change password



Imagine that you have an expired Oracle Database account and you have an urgent need to recover it. Maybe you don’t have any chance to ask that user to change password, or maybe you expired such an account that used by a production application by mistake!

-yes, I confess; it was me who experienced that situation-

If so, do what I did; calm down and take a deep breath and imagine that you are the one who survived after a big disaster on the earth. Just kidding, it’s not a big trouble.

Anyway, I couldn’t find any documented solution for that by Oracle, but found a way to accomplish this task:
Alter user’s password with existing password. Yes, it works. Let’s make a short demo:
    1. Create a user
    2. Expire it
    3. Alter user’s password with existing password to unexpire

1. CREATE AN ACCOUNT NAME TEST

SQL> CREATE USER TEST IDENTIFIED BY "manager";

User Created

SQL> SELECT username,account_status,expiry_date FROM dba_users WHERE username='TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- -----------
TEST OPEN

2. EXPIRE IT
 
SQL> ALTER USER TEST PASSWORD EXPIRE;

User altered.


SQL> SELECT username,account_status,expiry_date
FROM dba_users
WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- ------------
TEST EXPIRED 19/12/2012
 
3. GET THE EXISTING PASSWORD

SQL> SELECT DBMS_METADATA.get_ddl ('USER', 'TEST')
FROM DUAL;

CREATE USER "TEST" IDENTIFIED BY VALUES 'S:24BF9B8EBC5BC5B609D01915E089AF86D13E1F0627A729146708F5296E32;806B1E705874556A'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
 
4.EDIT ABOVE SCRIPT TO HAVE ALTER STATEMENT AND EXECUTED IT

SQL> ALTER USER "TEST"
IDENTIFIED BY VALUES 'S:4F5BD279183624CB94C3E67420A1E748A484183579F89E64B98ECB17A162;806B1E705874556A';

User altered.


SQL>SELECT username,account_status, expiry_date FROM dba_users WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- -----------
TEST OPEN


5.OPTIONALLY DELETE TUTORIAL OBJECT


SQL> Drop user TEST cascade;




I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!