Monday, April 19, 2021

Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps
  •  Alter the control_files parameter
ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;
  • Shutdown the database and copy or move the files to new location
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl
  • Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;


Follow the below steps to move or rename a Redo log file
  • Shutdown the Db and rename the file at operating system
SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo
  • Start the database in mount mode and ALTER DATABASE RENAME FILE
SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;
  • Open the database and verify
SQL> Alter database open;
SQL> SELECT member FROM v$logfile;


We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.
  • You can check the redo status from the V$log view
SQL> select group#, status from v$log;
---------- ----------------
  • Drop and recreate the redo log group with new location
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;
  • Switch the log file to change the current redo and recreate the other redo groups
SQL> Alter system switch log file;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards., Oracle ACE

Wednesday, April 14, 2021

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170
Bug 5523799
Bug 5637976

Thanks & Regards,, Oracle ACE

Thursday, March 25, 2021

ORA-39346: data loss in character set conversion for object TABLE_STATISTICS

When doing export/import using data pump encountered below error. This happens when invalid or corrupt characters are stored in the database.

When using export/import instead of data pump, no errors reported.

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLE1"
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLEMAT"
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Sun Apr 11 12:05:25 2021 elapsed 0 00:20:13

After research found that this is a known issue due to Bug 13696958 .

The fix for Bug 13696958 

added warning messages in 12.1 datapump import for characters that couldn't be converted. Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.


Needs to apply interim patch 21342624 
and execute post-install step:

$ datapatch

If the patch 21342624 installed then impdp log will report the objects that are experiencing data loss during characterset conversion. If the fix then character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.

That being said the fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database

In case of any further issues , patch questions or concerns, please contact Oracle Support.

Refer Oracle support notes: -


Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

Thanks & Regards,, Oracle ACE

Wednesday, March 10, 2021

How to reset SYS, SYSTEM or User password or grant SYSDBA, SYSOPER privilege

This article helps to reset SYS, SYSTEM or any other user password and also grant admin privileages. There are different methods to rest the password in Oracle.

As SYSDBA or DBA privilege - As long as you have SYSDBA or DBA privilege you should be able to reset any user password.

$ sqlplus / as sysdba
SQL> alter user <username> identified by <new password>;
For ex:- alter user sys identified by xxxaaxxx;
For ex: - alter user EMP identified by xxssxxxx;

Orapwd Utility – This is very useful utility to rest the password. This utility helps in granting SYSOPER, SYSDBA privileges. Creating orapwd enables users to connect with admin privileges. From Oracle 11g the passwords are case sensitive.

orapwd file=orapwd<SID> password=password entries=max_users

In order Oracle to check for password file the REMOTE_LOGIN_PASSWORDFILE must be set to either SHARED or EXCLUSIVE

SHARED - More than one database can use a password file. But only SYS user recognized by the password file
EXCLUSIVE - The password file can be used by only one database and the password file can contain names other than SYS.

SQL> alter system set remote_login_passwordfile =’EXCLUSIVE’;
SQL> show parameter password
----------------------------- ----------- ----------
remote_login_passwordfile string EXCLUSIVE

You can also grant SYSDBA, SYSOPER privileges to user by logging as SYSDBA. Once the privilege is granted, the users will be added to password file

SQL> grant sysdba to AdminUser;
SQL> grant sysoper to DBAUser;

To see who are all the users has sysdba, sysoper privilege run below command. You can also see the users listed in password file

SQL> select * from v$pwfile_users;

Oracle has listed several articles how to recover LOST password and resolve issues connecting as YSSDBA.
How to Set up the Oracle Password File MOSC Note: 1029539.6
Checklist for Resolving Connect AS SYSDBA Issues MOSC Note: 69642.1
How To Recover From Lost SYS Password MOSC Note: 805084.1

Thanks & Regards,

Wednesday, February 10, 2021

Patching Oracle Database Platform using Opatch or MOPatch

When applying the Oracle patches, make sure you follow the processes pre-checks, patch execution and post checks.

In SAP environment, MOPatch is very helpful and it has bundle patchs (SBP). Using this tool we can install multiple patches at single run, it will automate the oracle patching processes. However, note that MOPatch internally calls OPatch to install patches.

  • Make sure you have enough space in $ORACLE_HOME file system
  • Take a Tar or backup of $ORACLE_HOME
tar cvf - $ORACLE_HOME/12201 | gzip > 12201.101020.tar.gz
  • Install latest Opatch and MOpatch (in case of SAP)
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch-pre-SBP_12201201020_101020
$ unzip -qd $ORACLE_HOME OPATCH12201P_2008-70001555.ZIP -x SIGNATURE.SMF
$ unzip -qd $ORACLE_HOME/sapbundle SAP12201P_2011-70001555.ZIP 'SBP_12201201020_101020/MOPatch/*'
$ test -d $ORACLE_HOME/MOPatch && mv $ORACLE_HOME/MOPatch $ORACLE_HOME/MOPatch-pre-SBP_12201201020_101020
$ mv $ORACLE_HOME/sapbundle/SBP_12201201020_101020/MOPatch $ORACLE_HOME/MOPatch
  • check the version of MOPatch & Opatch version
$ORACLE_HOME/OPatch/opatch version

  • Stop all database applications
  • Backup the database and related files
  • Shut down the databases, listeners, and other processes running from the Oracle Home, use the fuser OS command to check for stale SQL*Plus sessions: $SBPFUSER $ORACLE_HOME/bin/oracle
  • Install the patch –
For SAP systems using MOPatch
env ORACLE_HOME=$ORACLE_HOME $ORACLE_HOME/MOPatch/ -v -s SAP12201P_2011-70001555.ZIP

You should see a message
MOPatch completed successfully
Overall Status : COMPLETE

For Other Oracle Systems using Opatch
$ cd /opt/patches/<patch number>
$ opatch apply

You should see a message
Opatch completed successfully
Overall Status : COMPLETE

  • Start the database and verify the database processes and logs. The logs should not have any error messages
  • Run the post patch update steps for SAP system 
  • Apply _FIX_CONTROL, EVENT parameters if required.
Where _FIX_CONTROL, initialization parameter will give control over specific features and bug fixes. However, it should be set under the direction of Oracle Corporation

Ex:- ALTER SYSTEM SET "_FIX_CONTROL"='4545454:ON','4547778:OFF',……. COMMENT='SAP_12201201020_101020' SCOPE=SPFILE;

Where EVENT, initialization parameter will be used to generate information in form of a trace file in the context of the event. The trace file is located in a directory specified by the parameter USER_DUMP_DEST

ALTER SYSTEM SET EVENT= '10027','10028', '10142' COMMENT='SAP_12201171017_101020' SCOPE=SPFILE;
  • Restart the database and Take a backup
  • Start the application and check the application
SAP Oracle Patches Reference
  • Known issues related to the installation of Database Release Update and the other patches contained in this SBP are described in SAP Note 2507228 Database: Patches for
  • Known issues related to the Oracle installation utilities, including MOPatch, OPatch, and the Oracle Universal Installer, are described in SAP Note 1915299 Troubleshooting Software Installation (12.1/12.2).
Oracle Patches reference

Please refer Oracle documentation
Oracle Help Center