Auto Extend Tablespace scripts

1 - A script for summation of datafiles in tablespaces, ie a tablespace space usage script.

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

2- A script that can give accurate tablespace usage statistics that include auto extend. It determines the space usage WITH auto-extend has taken into account, not just current free space.

select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_data_files a,V$DATAFILE b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID
union
select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_temp_files a,V$tempfile b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID;

Reference: Auto Extend Tablespace scripts (Doc ID 1908578.1)

APP-FND-01564: ORACLE error 1455 in afpodbinit

 The issue started after cloning the Oracle Database from an earlier release of 12.1.0.2 to 12.1.0.2 [ODA to ODA]

This issue was caused by the newly enabled Unified Auditing feature in the target database server.

Error:

Cause: afpodbinit failed due to ORA-01455: converting column overflows integer datatype.

The SQL statement being executed at the time of the error was: begin fnd_global.bless_next_init('FND_PERMIT_0000'); fnd_global.initialize(:session_id, :user_id, :resp_id, :resp_appl_id, :security_group_id, :site_id, :login_id, :conc_login_id, :prog_appl_id, :conc_program_id, :conc_request_id, :conc_priority_request, :form_id, :form_application_id, :conc_process_id, :conc_queue_id, :queue_appl_id, :server_id); fnd_profile.put('ORG_ID', :org_id); fnd_profile.put('MFG_ORGANIZATION_ID', :mfg_org_id); fnd_profile.put('MFG_CHART_OF_ACCOUNTS_ID', :coa); fnd_profile.put('APPS_MAINTENANCE_MODE', :amm); end; and was executed from the file &ERRFILE.

Fix:

With Oracle Database 12c, Unified Auditing is introduced. Prior to Oracle Database 12c, auditing is configured using the AUDIT_TRAIL database parameter, 

which is called traditional auditing. Unified Auditing is not certified with E-Business Suite. Bug 25540183 is logged for this Enhancement Request.

To disable the new Unified Auditing functionality:

1. Take the Apps and DB tier offline

2. Source RDBMS environment

3. cd $RDBMS_ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

4. Verify the output of the following query is FALSE

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

5. Take the DB and Apps tier online

Reference: APP-FND-01564 AND ORA-1455 Errors In Afpodbinit When A EBS Form Is Called Or Responsibility Selected (Doc ID 1963969.1)

RMAN Duplicate Fails ORA-38862: Flashback Database Logs Are In Use

 RMAN backup restore is failing with the following error when the flashback is turned on in the source database.

Error:
Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 14894
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 04/04/2021 23:37:08
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter database flashback off
RMAN-11001: Oracle Error:
ORA-38862: Flashback database logs are in use.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/04/2021 23:37:21
RMAN-05501: aborting duplication of target database
Recovery Manager complete.

Fix:

Update the below values on the target database and retry the clone.

SQL> alter system set db_flashback_retention_target=0 scope=both;
SQL> alter system set db_recovery_file_dest='' scope=both;

Reference  -  (Doc ID 2418803.1)

OEM 12c: ORA-28001: the password has expired The Connect Descriptor was (DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))(CONNECT_DATA=(SID=)(SERVER=DEDICATED)))

This error occurred on DB home page in OEM 12c.
ORA-28001: the password has expired The Connect Descriptor was (DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=<port>)))(CONNECT_DATA=(SID=<sid>)(SERVER=DEDICATED)))

Cause:

The cause of this issue is monitoring user "DBSNMP" password expired.

select username, account_status from dba_users where username='DBSNMP';

Solution:

To resolve this issue we need to reset the password and unlock the user account using the below steps.

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' from dba_users d, sys.user$ u where d.username = upper('DBSNMP') andu.user# = d.user_id;

'ALTERUSER"'||D.USERNAME||'"IDENTIFIEDBYVALUES'''||U.PASSWORD||''';'
--------------------------------------------------------------------------------
alter user "DBSNMP" identified by values '31B5G2AQ92890956';

SQL> alter user "DBSNMP" identified by values '31B5G2AQ92890956';

Note: If you want to keep the old password then you no need to do any changes in OEM but if you want to update the new password then follow the below steps in OEM.
1.      Click Targets->Databases.
2.      Select radio button for Database Instance Name and then Click Configure
3.      Change the Monitor Password, Click Next and Submit

Unable to create an Oracle ASM database lower than 12.1.0.2

Unable to create an Oracle ASM database lower than 12.1.0.2.17814 PSU (12.1.2.12).

Hardware Models:

Oracle Database Appliance X6-2-HA, X5-2, X4-2, X3-2, and V1.

Workaround:

There is not a workaround. If you have Oracle Database 11.2 or 12.1 that is using Oracle Automatic Storage Management (Oracle ASM) and you want to upgrade to a higher release of Oracle Database, then you must be on at least Oracle Database Appliance 12.1.2.12.0 and Database Home 12.1.0.2.170814.

The upgrade path for Oracle Database 11.2 or 12.1 Oracle ASM is as follows:

• If you are on Oracle Database Appliance version 12.1.2.6.0 or later, then upgrade to 12.1.2.12 or higher before upgrading your database.
• If you are on Oracle Database Appliance version 12.1.2.5 or earlier, then upgrade to 12.1.2.6.0, and then upgrade again to 12.1.2.12 or higher before upgrading your database.

This issue is tracked with Oracle bug 21626377, 27682997, and 21780146. The issues are fixed in Oracle Database 12.1.0.2.170814.

Error when upgrading virtualized platforms to 12.2.1.4.0 in ODA

When upgrading Oracle Database Appliance hardware models with virtualized platforms to 12.2.1.4.0, perform these manual steps before upgrading to release 12.1.2.12.

If you upgrade Oracle Database Appliance hardware models with a virtualized platform, to 12.1.2.12, then there is an error when using the driver domain functionality. To upgrade to Oracle Database Appliance release 12.2.1.4.0, follow the steps documented in the workaround section.

Hardware Models:

Oracle Database Appliance X7-2-HA, X6-2-HA, X5-2, X4-2, X3-2, and V1 with a virtualized platform that use driver domain virtual machines

Workaround:

Follow these steps to upgrade to Oracle Database Appliance release 12.2.1.4.0:

• Deploy Oracle Database Appliance release 12.1.2.11 and create multiple virtual machines with driver domain enabled.
• Shut down all running virtual machines and repository.
• Apply the release 12.1.2.12 patch bundle.
• Apply the release 12.2.1.4.0 patch bundle.
• Start the repository. If you encounter the error OAKERR:5015 Start repo operation has been disabled by flag, then run the following command: 
• "oakcli enable startrepo -node 0/1"
• Start the virtual machines and confirm that they have started.

ODA: 'oakcli update -patch --database' Does Not Run Datapatch

Upon running /opt/oracle/oak/bin/oakcli update -patch 12.1.2.6.0 -database, some of the databases do not receive the update. The database homes are patched correctly and there are no errors in the update logs.

Symptoms:
select * from dba_registry_sqlpatch;

This either shows no rows selected or some previous entry.

Cause:
There are multiple bugs around this where ODA is still trying to run catbundle instead of datapatch, and will be fixed in future release.

Fix:
Workaround
Please set your oracle home and SID for each database one at a time, cd to
$ORACLE_HOME/OPatch directory and run
./datapatch

After it is run get the following from each DB
select * from dba_registry_sqlpatch;

This should show the current output with "Success"

ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST

Below error occurred while changing the destination of archive log files in oracle database.

Error:

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;
ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Fix:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439
SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439

Errors while applying 12.2.7 RUP Patch (24690690) in Oracle E-Business Suite 12.2

System got hanged while applying 12.2.7 RUP Patch (24690690) and adop exits with the errors.

Error:

1) All workers are failed with the Java error..
2) Invalid objects count is increased to 56k.

Troubleshooting:

1) Cleared the invalid objects by using utlrp.sql
2) Run the auto config on DB node
3) Start the patching activity again.

In step (3) while running auto config in database node exists with the below error.

ORA-01400: cannot insert NULL into ("APPLSYS"."FND_NODES"."NODE_NAME")

Adop error message: 

ERRORMSG: Cannot identify any valid application tier nodes in ADOP_VALID_NODES table. Ensure AutoConfig has been run on all nodes.

To resolve this error need to follow the below steps:

Step 1:
drop synonym apps.fnd_nodes;
create synonym apps.fnd_nodes for applsys.fnd_nodes;
drop synonym apps.fnd_oam_context_files;
create synonym apps.fnd_oam_context_files for applsys.fnd_oam_context_files;

Step 2:
Change the worker status to restart using adcrtl utility.

Step 3:
Run auto config again in database node. (Auto config completed without any errors)

Step 4:
Restart the patching activity.

adop phase=apply apply_mode=downtime patches=24690690 abandon=no restart=yes workers=16

ORA-04063: package body "SYS.DBMS_STATS" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"

After applying the patch (26925311: DATABASE PATCH SET UPDATE 12.1.0.2.180116)  in Oracle Database 12.1.0.2. We have faced below errors while executing the post patch installation steps.

Error:

We try to compile the invalid objects using utlrp.sql after applying the patch got the below error.

Warning: XDB now invalid, could not find xdbconfig
ORDIM INVALID OBJECTS: ORDIMDPCALLOUTS - INVALID - PACKAGE BODY

ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at "SYS.UTL_RECOMP", line 875
ORA-06512: at line 4

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                          INVALID
Oracle Multimedia 12.1.0.2.0                                                       INVALID
Oracle XML Database 12.1.0.2.0                                                 INVALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                          INVALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                       INVALID


Fix:

SQL> shutdown immediate;
SQL> startup restrict
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

Shutdown and restart the database in normal mode.

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                         VALID
Oracle Multimedia 12.1.0.2.0                                                     VALID
Oracle XML Database 12.1.0.2.0                                               VALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                      VALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                      VALID

Kernel of proper version is not found on node "test" [Expected = "2.6.39" ; Found = "2.6.32-300.3.1.el6uek.x86_64"]

The following error occurred while installing Oracle E-Business Suite 12.2.7 (Database pre-install checks failed).

Error:

ERROR: [Result.addErrorDescription:624]  Kernel of proper version is not found on node "test" [Expected = "2.6.39" ; Found = "2.6.32-300.3.1.el6uek.x86_64"]

Fix:

The following are the steps needed to install the rpm using public-yum, with sample output from an Oracle Linux 6 installation:

1. As an authorized user (such as root), retrieve the file that configures repository locations:
# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo
For Oracle Linux 5 or 7, users should replace 'public-yum-ol6.repo' in the command above with 'public-yum-el5.repo' or 'public-yum-ol7.repo' respectively.

2. Using a text editor, change the field 'enabled=0' to 'enabled=1' for the repositories corresponding to the machine's operating system while also enabling the 'addons' channel. Here's an example of a repo file's entries:

[ol6_latest]

name=Oracle Linux $releasever Latest ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1


[ol6_addons]

name=Oracle Linux $releasever Add ons ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1



[ol6_UEK_latest]

name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/latest/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1


3.Update all packages and then install the pre-install rpm by running the following yum commands:

# yum update


# yum install oracle-ebs-server-R12-preinstall 
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracle-ebs-server-R12-preinstall.x86_64 0:1.0-3.el6 will be installed
--> Processing Dependency: libstdc++-devel for package: oracle-ebs-server-R12-preinstall-1.0-3.el6.x86_64
--> Processing Dependency: libXp(x86-32) for package: oracle-ebs-server-R12-preinstall-1.0-3.el6.x86_64
..
Transaction Summary
================================================================================
Install     176 Package(s)
Upgrade      11 Package(s)
Total download size: 168 M
Is this ok [y/N]: y
Downloading Packages:
..

Dependency Updated:
  db4.x86_64 0:4.7.25-18.el6_4                                                 
  db4-utils.x86_64 0:4.7.25-18.el6_4                                           
  glib2.x86_64 0:2.26.1-3.el6                                                 
  glibc.x86_64 0:2.12-1.132.el6                                               
  glibc-common.x86_64 0:2.12-1.132.el6                                         
  libblkid.x86_64 0:2.17.2-12.14.el6                                           
  libgcc.x86_64 0:4.4.7-4.el6                                                 
  libstdc++.x86_64 0:4.4.7-4.el6                                               
  libuuid.x86_64 0:2.17.2-12.14.el6                                           
  nss-softokn-freebl.x86_64 0:3.14.3-9.el6                                     
  util-linux-ng.x86_64 0:2.17.2-12.14.el6                                     
Complete!
 
Versions listed above are an example - actual versions may be different based on updates to the packages.

Detailed log and backup files are available under /var/log/oracle-ebs-server-R12-preinstall.

libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory at java.lang.ClassLoader$NativeLibrary.load(Native Method)

This error occur while trying to  ./rapidwiz-version in Oracle linux 6, and resulting the Wrong package version.

Issue:

Rapid Install Wizard will now launch the Java Interface.....

Exception in thread "main" java.lang.UnsatisfiedLinkError: /u01/Stage122/startCD/Disk1/rapidwiz/jre/Linux_x64/1.6.0/lib/i386/xawt/libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.load0(Unknown Source)
at java.lang.System.load(Unknown Source)
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.<clinit>(Unknown Source)
at java.awt.Component.<clinit>(Unknown Source)
Could not find the main class: oracle.apps.ad.rapidwiz.RIWizard.  Program will exit.

Fix:

First we need to remove the old package using that resulting the issue :

rpm -e package name

[root@11111111 yum.repos.d]# rpm -qa | grep -i libaio*
libaio-devel-0.3.107-10.el6.x86_64
libaio-0.3.105-2.x86_64
libaio-0.3.107-10.el6.x86_64
[root@11111111 yum.repos.d]# rpm -e libaio-0.3.105-2.x86_64

Install the required package from the below link :

http://rpm.pbone.net/index.php3/stat/4/idpl/5543656/dir/startcom_5/com/libXi-1.0.1-3.1.i386.rpm.html

http://rpm.pbone.net/index.php3/stat/4/idpl/8118337/dir/startcom_5/com/libXi-1.0.1-3.1.x86_64.rpm.html

If Still issue is not resolved follow the below steps:

1. copy repo from https://public-yum.oracle.com/public-yum-ol6.repo to /etc/yum.repos.d/

2. enable addons channel in .repo file.

3. yum install oracle-ebs-server-R12-preinstall -y

above steps will cover dependencies and let you install EBS.

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

RMAN backup failed with the below error message and its require archive log files to complete the RMAN backup.

Error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/24/2018 03:35:09
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/arch/1_282613_769193473.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

This issue will happen If in case archive logs was deleted or corrupted.

Fix:

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

OR

If you are using catalog database to maintain RMAN repository instead of controlfile, you can try following command:

RMAN> resync catalog;

Run the backup again.

How to Rotate or Purge Listener Log Data to Avoid Large listener.log File?

How to rotate or backup or rename listener.log or purge listener log data to avoid large listener.log file?

Below are the possible ways to rotate/rename listener.log files.

1) Stop the listener on a regular basis then you can simply rename the file at the OS level, then on next listener startup, a new one will be created.

OR

2) If you have a 24X7 environment and the listener can not be stopped, then you can use the following steps to rename / move the file without having to restart it:

LSNRCTL>
LSNRCTL> set current_listener <listenername>
LSNRCTL> set log_status off

In another window rename the log file. Then return to previous window

LSNRCTL> set log_status on

which will create a new and empty listener.log file which will continue logging. 


 OR

 3) Follow the document here:

Note 135063.1   How To Change the Listener Log Filename Without Stopping the Listener

How to move RAC Database ORACLE_HOME from one location to another location

Below are the steps to move the RAC database ORACLE_HOME from one location(filesystem) to another location.

This post for database home only and does not apply to CRS or GI home.

Execute all the following as database user:

Solution 1:

1. Install a new set of RAC binaries with OUI into a new home.
2. Apply all patches that have been installed in the old home, use "opatch lsinventory" to compare the old and new home once all patches are applied.
3. Copy over init{ORACLE_SID}.ora in dbs directory from the old home to the new home on all nodes.
4. Copy over network files (listener.ora, tnsnames.ora, and sqlnet.ora) in network/admin directory from the old home to the new home on all nodes.
5. Shutdown the database with srvctl from the old database home: srvctl stop database -d <dbname>
6. Modify the database resource as with srvctl from the old database home: srvctl modify database -d -o <new-database-home>
7. Start the database with srvctl from the new database home: srvctl start database -d <dbname>


Solution 2: (Clone)

1. Copy existing RAC binaries to a new location and keep the same ownership/permission on any node: cp -rp <old-database-home> <new-database-home>
2. Execute:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2

$ perl $ORACLE_HOME/clone/bin/clone.pl '-O"CLUSTER_NODES={<comma separated listed of hostnames>}"' '-O"LOCAL_NODE=<this host’s hostname>"' ORACLE_BASE=<your ORACLE_BASE> ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=<your inventory name> '-O-noConfig' "

Refer to: https://docs.oracle.com/cd/E11882_01/rac.112/e41960/clonerac.htm

3. Extend the new home to other nodes: <new-database-home>/oui/bin/addNode.sh
4. Go to Step3 of "Solution 1"

ORA-00304: requested INSTANCE_NUMBER is busy

ORA-00304: requested INSTANCE_NUMBER is busy

On starting upon instance  in 2 Node RAC Database,We got the below error message.

SQL> startup;
ORA-00304: requested INSTANCE_NUMBER is busy

We have identified the background sessions are still hanging at the OS level.

Solution:

Solution is to Kill the hanging sessions at the os level and restart the instance.

ps -ef | grep -i DATABASE_SID

kill -9 pid1 pid2

after killing the hanging session , we are able to startup the instance without any issues.

Growth of oracle database size in day, week and month wise

Below query to get the database size growth in day, week and month wise.

Query:

set linesize 210
set linesize 210
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.K / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Growth MONTH",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Growth MONTH in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS K FROM dba_free_space) FREE
GROUP BY FREE.K;

Primary database error ORA-07217: sltln: environment variable cannot be evaluated

Error:

Primary database throw below error while trying to transport archive log to standby database

ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;
Errors in file /oracle/diag/rdbms/<oracle_sid>/trace/<oracle_sid>arc2_2894.trc:
ORA-07217: sltln: environment variable cannot be evaluated.
ARC2: FAL archive failed with error 7217. See trace for details
Errors in file /oracle/diag/rdbms/mdmprd/mdmprd/trace/<oracle_sid>_arc2_2894.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

Fix:

$ORACLE_SID needs to be replaced by proper value

Modify log_archive_format to correct value and restart the redo transport.

ORA-04024: self-deadlock detected while trying to mutex pin cursor

Error:

Database errors when trying to open with the following:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x2CD828600

Fix:

Apply the below patch to resolve this issue.

Patch 21372829.

IO Error: Got minus one from a read call vendor code 17002

When we are trying to connect database by using Sql developer, we got below error.

Error:

IO Error: Got minus one from a read call vendor code 17002

Fix:

You can follow these steps to avoid the error:

1. Go to directory $ORACLE_HOME/network/admin
2. Make sure you have backup of sqlnet.ora
3. Modify sqlnet.ora file with the parameter: (tcp.validnode_checking = no)
4. If you don't want to disable this, you can put the machine names as follows:  tcp.invited_nodes=(machine1, machine2)
5. Bounce the listener or reload the listner (lsnrctl reload)