my technical article has been published in the latest October 2016 Oracle ACE newsletter. I would like to Thank Oracle ACE program and OTN for their support of young IT Professionals in Oracle Technology from around the world.
my technical article has been published in the latest October 2016 Oracle ACE newsletter. I would like to Thank Oracle ACE program and OTN for their support of young IT Professionals in Oracle Technology from around the world.
******* PROBLEM:
a data file was added on the primary database table space and the standby database was configured with
“MANUAL” file management:
SQL> show parameter standby
NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
this will lead to the following errors being thrown while applying archive logs (syncing the database with standby database):
ORA-01110: data file 2: ‘F:\ORACLE\PRODUCT\12.1.0\DB_1\DATABASE\UNNAMED00002’
ORA-01157:cannot identify/lock data file 2 – see DBWR trace file
ORA-01111: name for data file 2 is unknown – rename to correct file
******* SOLUTION:
execute the following query to find out the data file location on the primary database:
SQL> select file#,name from v$datafile where file#=2;
execute the following on the standby database:
SQL> alter database create datafile ‘F:\ORACLE\PRODUCT\12.1.0\DB_1\DATABASE\UNNAMED00002’ as ‘G:\DATAFILE\DBF2\ORAD12D01.ORA’ ;
then put the database on automatic file management mode on the standby (if you are using init.ora text file add the entry to ensure consistency in restarts):
SQL> alter system set standby_file_management=AUTO;
The following procedure is for Non-RAC environment and in the below example I am rolling back July 2016 PSU :
Double check that under $ORACLE_HOME/rdbms/admin
that there is a file with the naming convention catbundle_PSU_DBSID_ROLLBACK.sql
If this file doesn’t exist then you need to execute the following:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> @utlrp.sql
SQL> QUIT
Shutdown the database
SQL> shutdown immediate;
Execute opatch utility to rollback the patch:
opatch rollback -id 23054359
Remark: the patch number 23054359 is supplied with HTML document that comes with the patch downloaded.
cd $ORACLE_HOME/rdbms/admin
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU_ORAT772_ROLLBACK.sql
SQL> @utlrp.sql
SQL> QUIT
For Java component:
Shutdown the database
SQL> shutdown immediate;
Execute opatch utility to roll back the patch:
opatch rollback -id 23177551
cd $ORACLE_HOME/sqlpatch/23177551
SQL> CONNECT / AS SYSDBA
SQL> startup upgrade
SQL> @postdeinstall.sql
SQL> shutdown
SQL> startup
SQL> @utlrp.sql
SQL> QUIT