ORA-28001: the password has expired

sqlplus /nolog 

SQL> connect / as SYSDBA 

Connected.

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

check "PASSWORD_LIFE_TIME" by

Sql > select * from dba_profiles;

Set to Never expire

Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Then reset the password of locked user

Sql> alter user myuser identified by mynewpassword account unlock;

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

ORA-03113: end-of-file on communication channel

[root@testwms orawms]# ps -ef |grep smon

orawms    6818     1  0 15:49 ?        00:00:00 ora_smon_AAD

oradev    6930     1  0 Oct19 ?        00:03:09 ora_smon_ADV

oradev    7500     1  0 Oct19 ?        00:02:08 ora_smon_AAD4

root      8068  3227  0 15:55 pts/2    00:00:00 grep smon

orawms    9300     1  0 Oct19 ?        00:03:13 ora_smon_AAD3

Case:

While trying to open db 11g on linux machine we encounter the error ORA-03113: end-of-file on communication channel.



Investigation and solution:

Returning back to the alert log file we can see the following lines:

*************************************************************************************

ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

Errors in file /u01/app/oracle/diag/rdbms/mostprod/mostprod/trace/mostprod_ora_11853.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 42960384 bytes disk space from 5368709120 limit

ARCH: Error 19809 Creating archive log file to ‘/u01/flash_rec_area/MOSTPROD/archivelog/2012_11_21/o1_mf_1_177_%u_.arc’

Errors in file /u01/app/oracle/diag/rdbms/mostprod/mostprod/trace/mostprod_ora_11853.trc:

ORA-16038: log 3 sequence# 177 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/mostprod/redo03.log’

*************************************************************************************

As you can see the probles is regarding the archive log area is ran out of space.

To solve this issue:
We can follow any of the given suggestions but what we do in our case is to archive some old archived logs and delete then from the archive log area.
Optionally you may change the archive log retention policy to a suitable period of time.
As we remove the archived log files through the OS command we need to tell the database about what we did that is using rman otherwise the database will still failed to open. So…

SQL> startup — Failed to startup, the same ORA-03113 Error

SQL> startup nomount — ORACLE Instance Started

SQL> alter database mount — Database altered

SQL> exit

$ cd $ORACLE_HOME/bin

$ ./rman target /

RMAN> crosscheck archivelog all — Here you will see all the names of archivelogs still exist

RMAN> delete expired archivelog all

RMAN> exit

$ sqlplus / as sysdba

SQL> alter database open — Database altered

Auto config in Oracle Apps R12 adautocfg.sh

Autoconfig is tool/utility to reconfigure your Oracle Application configuration files using context (XML file) and template files, more information here and here

This post covers steps to run Autoconfig in Oracle Apps R12 (steps are similar to 11i, only change is location of autoconfig script and log files)

.

A) Running Autoconfig on R12 environment Application Tier

1. Login as user owning application tierfor R12 (usually applmgr)

2. Set environment variable by executing env file $INSTALL_BASE/ apps/ apps_st/ appl/ APPL[$SID]_[$hostname].env

3. cd $ADMIN_SCRIPTS_HOME (or $INSTALL_BASE/apps/$CONTEXT_NAME/ admin/ scripts)

4. run adautocfg.sh (Autoconfig script)
 ./adautocfg.sh or sh adautocfg.sh  provide apps password when prompted.
.
B) Running Autoconfig on R12 environment Database Tier

1.Login as user owning database tier for R12 (usually oracle)

2. Set environment variable by executing env file $INSTALL_BASE/ db/ tech_st/ [11.1.0 or 10.2.0]/ [$SID]_[$hostname].env

3. cd $ORACLE_HOME/ appsutil/ scripts/ $CONTEXT_NAME

4. run adautocfg.sh (Autoconfig script)
./adautocfg.sh or sh adautocfg.sh

5. Provide apps password when prompted

Things good to know about Autoconfig in R12
1. Autoconfig logs for R12 application tier are at

$INSTALL_BASE/inst/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log ($INST_TOP/admin/log/[$MMDDhhmm])

2. Autoconfig logs for R12 Database tier are at[$RDBMS_ORACLE_HOME]/appsutil/log/[$CONTEXT_NAME]/[$MMDDhhmm]/adconfig.log



3. R12 system is autoconfig enabled and uses context file stored in [INST_TOP]/appl/admin/[CONTEXT_NAME].xml (Application Tier)
and
[$DATABASR_ORACLE_HOME]/appsutil/[$CONTEXT_NAME].xml (Database Tier)


Related
387859.1 Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12

Oracle 11gR - 11.2.0.2 to 11.2.0.4 upgrade steps

* yum install oracle-rdbms-server-11gR2-preinstall

Download the ‘patch’ software

Currently, the Oracle Database 11.2.0.4 files are not available to download from the usual Oracle Technology Network (OTN) area, instead you have to log into My Oracle Support (MOS)and search for patch number ‘13390677’. It’s probably worth mentioning at this point, that the ‘patch’ is a full install, despite the reference – so you don’t need to download Oracle Database 11.2.0.2 files in addition here

Once you’ve searched for the ‘patch’, select the link appropriate to your platform, in this case ‘Linux x86-64’. When you click download, there will be a total of 7 zip files available. For a standard installation, you only need zip parts 1 and 2…

• p13390677_112040_platform_1of7.zip (Oracle Database, including Oracle RAC components)
• p13390677_112040_platform_2of7.zip (Oracle Database, including Oracle RAC components)

Once you’ve downloaded the software, unzip both files to a staging area:

unzip /tmp/p13390677_112040_Linux-x86-64_1of7.zip -d /u01/app/oracle/software
unzip /tmp/p13390677_112040_Linux-x86-64_2of7.zip -d /u01/app/oracle/software

All of the contents from the above will be extracted to a “database” directory.
Start the installer

Open an X-Windows session to the server as the ‘oracle’ user.

Once you’ve met the requirements, and have unzipped the software, you can start the install:

Upgrade DB using DBUA

Update environment settings

Once the installation has finished, add the appropriate environment variables to your ‘oracle’ user profile.

How to find Oracle EBS Weblogic Server Admin Port Number and URL

How to find Oracle EBS Weblogic Server Admin Port Number and URL

Web Logic Server Admin Port  

 Method 1:

Open the EBS domain config file in following location.
$EBS_DOMAIN_HOME/config/config.xml

Then check for Admin Server Port.

Method 2:

Open the application tier context file
$CONTEXT_FILE

Then check the value of variable "s_wls_adminport" in the for the correct Web Logic Server Admin port number

Web Logic Server Console URL 

http://<server name>. <domain name> : < WLS Admin Port>/console
For example. http://oracle.test.com:7003/console

ORA-02020: Too Many Database Links In Use

PROBLEM:

While querying against a database link, got below error.

select sysdate from dual@DB5

ERROR at line 1:

ORA-02020: too many database links in use

CAUSE & SOLUTION:
open_links parameter control, the number of database links each session can use without closing it.
If you access a database link in a session, then the link remains open until you close the session.


SQL> show parameter open_link

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4


Here open_links is set to 4, i.e a session can access only 4 open database links in that session.When the open db_link connection reaches the limit(open_links), it throws ORA-02020: too many database links in use.
Solution:


1. Close the open db_link connections

2. Increase the open_links parameter (bounce required)


Let’s reproduce this error.


SQL> select sysdate from dual@DB1;

SYSDATE
---------
30-JUL-17


SQL> select sysdate from dual@DB2;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB3;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB4;

SYSDATE
---------
30-JUL-17

SQL> select sysdate from dual@DB5;

select sysdate from dual@DB5

ERROR at line 1:


ORA-02020: too many database links in use

Now we reached maximum open database link connections.


— View the open database link connection[Need to run this from same session ]

-- The table v$dblink populates data only for the current session,

SQL> select db_link,logged_on,open_cursors from v$dblink;

DB_LINK LOG OPEN_CURSORS
---------------- --- ------------

DB1 YES 0

DB2 YES 0

DB3 YES 0

DB4 YES 0

We can see there are 4 open database link transactions and it is matching the open_links parameter( i.e 4). So quick way to fix is to close these connections.

SQL> alter session close database link DB4;

ERROR:

ORA-02080: database link is in use

Now we are getting an ORA-02080 error. So before closing the database link, we need to either commit/rollback.

SQL> commit;

Commit complete.

SQL> alter session close database link DB4;

Session altered.

SQL> select db_link,logged_on,open_cursors from v$dblink;

SQL> select db_link,logged_on,open_cursors from v$dblink;

DB_LINK LOG OPEN_CURSORS
---------------- --- ------------

DB1 YES 0

DB2 YES 0

DB3 YES 0

SQL> select sysdate from dual@DB5;

SYSDATE
---------
30-JUL-17

Alternative solutions is:

Increase the open_links parameter.

alter system set open_links=8 scope=spfile;

shutdown immediate;

startup