Error: ORA-65096: invalid common user or role name in oracle 19c database

Dear DBA-Mates, Happy New Year to you all!!!

Hope you all doing good.

Here, we would like to share user creation error ORA-65096: invalid common user or role name in 19c database.

How to create database user in 19c oracle database because when you create database user in oracle 19c database you may get an ORA error like ORA-65096 as shown below in details.

So, to avoid that user creation error either we can set one hidden parameter to true or else put pre-fix word as c##.

For kind information without alter of the hidden parameter or pre-fix word c## you can’t create the user in oracle 19c database like previous version of the oracle database.

Please find the below details:

Error:

SQL> show user

USER is "SYS"

SQL> create user ogg identified by ogg account unlock;

create user ogg identified by ogg account unlock

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL>

 

Solutions:

1.   Here, we have one hidden parameter which needs to be set as True as shown in below command:

SQL> alter session set "_ORACLE_SCRIPT"=true;

 Session altered.

 SQL> show user

USER is "SYS"

SQL>

 SQL> create user ogg identified by ogg account unlock;

 User created.

 SQL>

 

SQL> GRANT CONNECT, RESOURCE, DBA TO ogg;

 Grant succeeded.

 SQL>

SQL> conn ogg/ogg

Connected.

SQL> show user

USER is "OGG"

SQL>

2.   As discussed, there is another method also which can be used here but it will be different like pre-fix word as shown below:

 

SQL> create user C##OGGS identified by oggs;

 User created.

 SQL> create user c##OGGS identified by oggs container=all;

 User created.

SQL>

19c DB aler log path and details:

[oracle@linux trace]$ view alert_orcl.log

[oracle@linux trace]$ pwd

/u01/19cDB/diag/rdbms/orcl/orcl/trace

[oracle@linux trace]$

 

SQL> select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

ORCL      READ WRITE

 

SQL>

SQL> select BANNER_FULL from v$version;

 

BANNER_FULL

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL>

Some more important links:

Oracle Database 19c Features

Cloud Service Models in cloud computing

What is PDB and CDB in oracle 12c

How to improve oracle database performance tuning

How to change sysadmin password FNDCPASS in R12.1.3

Oracle Database 19c Features

Dear DBA-Mates,

We would like to share one of most important information as all the customer are upgrading to 19c.

Hope his will be useful as reason why we need to upgrade 19c database from previous version of database specially 12c which I am seeing/doing. So be Ready!!!

Reasons to upgrade 19c:

1.Oracle lifetime support policy only follows their latest version

2. OS compatibilities as per DB versions

3. Security purpose as compared to old version. 

Some Older version History:

12c (2014): Oracle designed to work on the Cloud, the most notable features are the pluggable databases and multitenant architecture.

 

18c (2018): autonomous database, this self-driving database is self-securing and self-repairing, available for the Oracle cloud or on-site premises.

 

19c (Jan 2019): will be supported through 2023, extended till 2026.

latest version-19.3

 

Oracle Version 19C

The latest Oracle version, the 19C, was released in early January 2019. It's been noted as the long- term release for the 12.2 product family of Oracle databases. This particular version will be supported through 2023, with extended support available until 2026.

 

Some of the newest features in the 19C release include:

Query Quarantine -

Unlike with previous Oracle versions, 19C solves the problem of overloading system resources with query requests. This version provides automatic query quarantine to ensure that specific queries aren't running over and over.

 

JSON Support -

This version has simplified the syntax for JSON functions and allows users the capability to do partial updates of JSON. In addition, it provides SODA APIs for Node.js, C, Python, and Java.

 

Automatic Indexing -

19C offers a machine learning algorithm feature that provides constant index adjustment. This algorithm leads to enhanced system performance and more cost savings. After just a short amount of use, this database version will build indexes that allow for more efficient plans for each dataset.

 

Hybrid Partitioned Tables -

This 19C feature permits database administrators to manage tables both inside and outside the database with ease. The partitions inside the database can easily be managed alongside partitions that are in read-only data stores outside of the database on the premises or on the Cloud.

 

Active Data Guard -

This new feature works to prevent downtime and data loss during database repairs and upgrades. It allows for incidental updates to be run while a synchronized physical replica or standby is maintained.

Some more useful link:

How to change database DB name using NID utility

Stuck Thread in WebLogic Error and solution

What is PDB and CDB in oracle 12c

ORA-01110 data file 1: system01.dbf recovery

Xclock issue: Error: Can't open display Fixed

Dear DBA-Mates,

Hope you all are Good and Safe, also may have taken Covid-19 Vaccination which is very important.

We would like to share Xclock issue: Error: Can't open display xclock display error which we need to fix for display during Oracle Database/Application software installation as GUI.

Below are some important points and steps which may useful to all. This is very easy and simple steps but sometimes it will be headache to us…

Xclock issue: Error: Can't open display

XCLOCK Fix:

Below are the Pre-checks/pre settings:

1.   We need to start Xming software.

2.   We need to enable X11 in putty as shown below path:

Open putty à SSH à X11 à X11 Forwarding (Tick Mark “Enable X11 forwarding”)

X display location : “:0.0”

3.   Echo $DISPLAY – should give output

4.   Below rpms should be installed:

1. libXaw-1.0.13-4.el7.x86_64.rpm

2. xorg-x11-apps-7.7-7.el7.x86_64.rpm

5. If still not working then we can add xauth list as shown below:

[oracle@linux dbhome_1]$ xclock

Error: Can't open display:

[oracle@linux dbhome_1]$ echo $DISPLAY

 

[oracle@linux dbhome_1]$ export DISPLAY=localhost:11.0

[oracle@linux dbhome_1]$ xclock

PuTTY X11 proxy: Authorisation not recognised

Error: Can't open display: localhost:11.0

[oracle@linux dbhome_1]$ xauth list

linux.oracle.com/unix:16  MIT-MAGIC-COOKIE-1  ea8443005780edfb76f6930b20d4802c

linux.oracle.com/unix:11  MIT-MAGIC-COOKIE-1  80eab308a9f45535b261e1bf999d475e

linux.oracle.com/unix:13  MIT-MAGIC-COOKIE-1  3bbf11afe24f078bae5f55ae9145ca64

linux.oracle.com/unix:14  MIT-MAGIC-COOKIE-1  abc18e54fd548ccec81649f300a6a1af

linux.oracle.com/unix:15  MIT-MAGIC-COOKIE-1  2a03c3a4cc9c52f41b6fd13878194c0e

linux.oracle.com/unix:12  MIT-MAGIC-COOKIE-1  e7b5657526c2481dbfee1e0599496013

linux.oracle.com/unix:10  MIT-MAGIC-COOKIE-1  1ee8aec7489a11e0cb89a93734ccc19c

[oracle@linux dbhome_1]$ pwd

/u01/DB18C/oracle/product/18.0.0/dbhome_1

[oracle@linux dbhome_1]$

[oracle@linux dbhome_1]$ echo $DISPLAY

localhost:11.0

Note: If xclock is working with root but not working with oracle user then also we can take xauth list from root user and export the display as per root user and add the xauth list in oracle user from root user.

Below is the step to add the xauth list:

[oracle@linux dbhome_1]$ xauth add localhost.localdomain/unix:10  MIT-MAGIC-COOKIE-1  15779b706bb1daf925ad02323df4fda3

[oracle@linux dbhome_1]$ xauth add localhost.localdomain/unix:11  MIT-MAGIC-COOKIE-1  542175afd02f0cc7d5f9fe27c43321d4

[oracle@linux dbhome_1]$ xauth add linux.oracle.com/unix:10  MIT-MAGIC-COOKIE-1  94eb4e7f6c702d2e962a3e2cbf727dc8

[oracle@linux dbhome_1]$ xauth add linux.oracle.com/unix:11  MIT-MAGIC-COOKIE-1  21ad871e3931d34b963988874a232d0d

[oracle@linux dbhome_1]$ xclock

Warning: Missing charsets in String to FontSet conversion

^C

[oracle@linux dbhome_1]$

Hope above steps are easy and helpful. Kindly let us know for any concerns and will try to give some more inputs.

How to improve oracle database performance tuning

SQL Tuning Advisor steps and details

What is PDB and CDB in oracle 12c

Stuck Thread in WebLogic Error and solution

Concurrent Manager Performance Tuning Tips in Oracle EBS R12

How to improve oracle database performance tuning

Dear DBA-Mates,

Hope you all are doing Good and fantastic. We are back with one of the most important questions as Oracle Database performance Tuning which will be useful here. And would like to share with you all.

In every interview question is asked as What to do if Oracle Database Performance issue occur?

Below are some points which are very important and useful to check the performance and will help to troubleshoot the Database performance issue.

1. DB and listener status:

Database and listener status is the basic check confirmation.

2. Mount points like archive destination and others:

We have check for the all-mount points and no mount point should be 100%. And specially archive mount point which should be below 70 to 80%.

3. Alert log and Trace file: which helps in getting some issue related information

4. top activity: 

OEM monitoring tool (memory uses, CPU uses)

5. Blocking session and deadlock: 

We can check blocking sessions and deadlock if occur any. Please click on blocking sessions and deadlock for more details.

6. User session (active, inactive, ideal etc):

We can check the number of Inactive sessions and if there is more number from long time then we can check details and kill them like form sessions related sessions and other accordingly.

7. Increase memory, CPU, resources if number of users increased, check DB growth – We can check and compare the details from previous to current like DB growth and user growth, so in case of DB and number of users have increased then of course we need to increase the memory and number of CPU to increase the performance.

8. Optimize the query:

We can optimize the query also to improve query.

9. DB Backup should run in non-business hours:

As we all know DB backup process uses resources which may also decrease the performance of database. So, always should run in non-hours business.

10. AWR, ash and addm reports:

We can also take awr, ash and addm reports which can help in analysis of the resource, sql etc... uses and can help in to improve the database performance.

11. Wait events and their types:

Here, we can check the what are events occurred in AWR report which can be analysis and take action accordingly.

12. Oracle bug: 

After above checks if still now helpful then we can check for if there is any oracle bug. For this we need to raise the Oracle Support and may need to database apply patches like quarterly or security patches etc. as per suggested by oracle support.

13. Table Scan check:

We can check if the query is scanning full table or not. Also, can check for GSS, last analyzed table/schema.

Hope this may useful, there are some more details which will backup with some more details.

Thank you all!!!

Ora-data Team

How to get sql query output in xml format from putty session

Dear DBA-Mates, How to get sql query output in xml format.

Hope you all are doing well and Safe at Home!!!

We would like to share one of very important and useful query for How to get SQL Query output in xml/xls readable format.

This is very headache if you are running in Putty Session.

Even we also use this same format for such queries. So, we thought to share to you all and it will be easy for us also for references in future.

This type of request will be send by the developer or functional team which they may need for some analysis or monthly reports generation.

Whatever if they asked then as a DBA we need to provide them, even though angry inside Hahahah.

And the worst things those queries will be running 2 to 3 hrs and rows may be 2 to 3 lakhs, so can imagine it.

$sqlplus / as sysdba

SQL> SET LINESIZE 10000

SQL> SET VERIFY OFF

SQL> SET FEEDBACK OFF

SQL> SET PAGESIZE 999

SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF

SQL> SPOOL <Output_Filename>.xls

SQL> set time on   ## To Capture query start and end time

SQL> @<Filename>.sql

SQL> spool off;

Optional:

SQL>SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON

SQL>SET LINESIZE 2000 VERIFY ON FEEDBACK ON

OR

SQL> set feed off markup html on spool on

SQL> SET PAGESIZE 50000

SQL> SPOOL <output_filename>.xls

SQL> <your_query like select * from dual;>

SQL> SPOOL OFF;

OR

SQL> set pagesize 50000

SQL> set heading on

SQL> set echo off

SQL> set colsep ','

SQL> set heading on

SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK OFF

SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF

SQL> spool workflow.xls

SQL> <Your query>

SQL> Spool off;

OR

Save Query output as HTML file from SQLPLUS

As alternative you may use below block of code from the sqlplus only to save the output as HTML file.

SQL> set linesize 4000

SQL> set verify off

SQL> set feedback off

SQL> set pagesize 9999

SQL> set markup html on entmap on spool on preformat off

SQL> spool /tmp/query_output.html

SQL> --Your query--

SQL> set markup html off

SQL> spool off;

Hope this ma useful and helpful for everyone here.

Some more useful link:

How to change database DB name using NID utility

Stuck Thread in WebLogic Error and solution

How to create weblogic user

What is PDB and CDB in oracle 12c

ORA-01110 data file 1: system01.dbf recovery

Regards,

How to change database DB name using NID utility

Hi DBA-Mates, Hope you all are doing Well!!!

We are back with how to change database DB name using NID utility.

Thank you all a lot for yours email as I was away for times because of some personal works.

It is very easy and simple but we are sure that very few people may have performed this activity, so it will be useful for those who has only read.

Note: If not sure about sys password, it can be set as either orapwd file or below command:

$sqlplus '/as sysdba'

SQL> alter user sys identified by sys;

User altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             356518584 bytes

Database Buffers           92274688 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      MOUNTED

SQL>

Command:

[oracle@localhost bin]$ nid TARGET=sys/<sys password>@<Service/old_dbname> DBNAME=<DB New Name> SETNAME=YES

Query:

[oracle@localhost bin]$ nid TARGET=sys/sys@ORCL DBNAME=ORCLNEW SETNAME=YES

DBNEWID: Release 11.2.0.2.0 - Production on Sat Feb 1 22:41:13 2020

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

Connected to database ORCL (DBID=1229390655)

Connected to server version 11.2.0

Control Files in database:

    /home/oracle/app/oracle/oradata/orcl/control01.ctl

    /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ORCL to ORCLNEW? (Y/[N]) => Y

Proceeding with operation

Changing database name from ORCL to ORCLNEW

    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified

    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified

    Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/tbs1.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/tbs2.db - wrote new name

    Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name

    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name

    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name

    Instance shut down

Database name changed to ORCLNEW.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

[oracle@localhost bin]$

Change db name in pfile:

[oracle@localhost dbs]$ vi initorcl.ora

*.db_name='ORCLNEW'

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             356518584 bytes

Database Buffers           92274688 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;

Database altered.

SQL>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCLNEW   READ WRITE

Hope this may helpful and useful.

Regards,

ORA-01110 data file 1: system01.dbf recovery

Hello DBA-Mates,

We are back after a long time with ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'. Got so many messages and comments as why stopped writing but we didn’t stopped , there was some bad time which slowly passing away and we came back.

Thank you all for your supporting and loving.

Recently, one of our friend faced this below issue and they were not able to recover the database. So, friends before playing with database take the backup always either online or offline.

Here, we have just shown as if we missed system datafile or deleted, how can we recover it and start the database.

1.

Take the offline backup.

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE  LOG_MODE     FLASHBACK_ON

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

ORA-DATA3      READ WRITE ARCHIVELOG   NO

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

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

Make some transactions inorder to generate archive logs

(if account is locked then):

SQL> alter user scott identified by tiger account unlock;

SQL> conn scott/tiger

Connected.

SQL> create table student(name varchar2(50)) tablespace users;

Table created.

SQL> begin

  2  for i in 1..300000 loop

  3  insert into student values('10g DBA Training and R12 Apps DBA Training student');

  4  end loop;

  5  commit;

  6  end;

  7  /

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

sql>conn /as sysdba;

SQL>  select * from v$recover_file;

no rows selected

SQL> column name format a40

SQL> select name,file#,status from v$datafile;

NAME                                          FILE# STATUS

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

/u01/oracle/oradata/ORA-DATA3/system01.dbf             1 SYSTEM

/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf            2 ONLINE

/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf             3 ONLINE

/u01/oracle/oradata/ORA-DATA3/users01.dbf              4 ONLINE

SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS

[oracle@sku ORA-DATA3]$ clear

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

[oracle@sku ORA-DATA3]$ ls -l syst*

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:37 system01.dbf

[oracle@sku ORA-DATA3]$ ls

control01.ctl      control04.ctl      redo01.log   redo05a.log  sysaux01.dbf

control01.ctl_old  control04.ctl_old  redo02a      redo05.log   system01.dbf

control02.ctl      example01.dbf      redo02a.log  redo06a.log  temp01.dbf

control02.ctl_old  indx01.dbf         redo02.log   redo06.log   undotbs01.dbf

control03.ctl      redo01a            redo03a.log  redo07a.log  users01.dbf

control03.ctl_old  redo01a.log        redo03.log   redo07.log

[oracle@sku ORA-DATA3]$ mv system01.dbf system01.dbf_old

[oracle@sku ORA-DATA3]$

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

SQL> shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

SQL>

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_

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

ERROR                                                                CHANGE#

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

TIME

---------

         1 ONLINE  ONLINE

                                                                      535300

15-SEP-11

Restore the system01.dbf from Offline Backup

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

$cp /u01/oracle/offline_backup/system01.dbf .   ##(This will also work if you restore system01.dbf from online backup)

[oracle@sku ORA-DATA3]$ ls -l *dbf

-rw-r----- 1 oracle oinstall 104865792 Sep 14 23:50 example01.dbf

-rw-r----- 1 oracle oinstall   4202496 Sep 14 23:50 indx01.dbf

-rw-r----- 1 oracle oinstall 251666432 Sep 14 23:50 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:56 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Sep 14 20:12 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 Sep 14 23:50 undotbs01.dbf

-rw-r----- 1 oracle oinstall  10493952 Sep 14 23:50 users01.dbf

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

[oracle@sku 2011_09_14]$ pwd

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15

[oracle@sku 2011_09_15]$ ls -alrt

total 33708

drwxr-x--- 3 oracle oinstall     4096 Sep 15 02:47 ..

-rw-r----- 1 oracle oinstall 34460160 Sep 15 02:47 o1_mf_1_1_7726hlvy_.arc

-rw-r----- 1 oracle oinstall     1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc

drwxr-x--- 2 oracle oinstall     4096 Sep 15 02:48 .

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

   

SQL> recover database;

ORA-00279: change 680758 generated at 09/14/2011 20:31:38 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc

ORA-00280: change 680758 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 681408 generated at 09/14/2011 20:45:01 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc

ORA-00280: change 681408 for thread 1 is in sequence #10

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.

arc' no longer needed for this recovery

ORA-00279: change 681427 generated at 09/14/2011 20:53:03 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc

ORA-00280: change 681427 for thread 1 is in sequence #11

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_

.arc' no longer needed for this recovery

ORA-00279: change 684925 generated at 09/14/2011 22:00:07 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc

ORA-00280: change 684925 for thread 1 is in sequence #12

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_

.arc' no longer needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS

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

         1 /u01/oracle/oradata/ORA-DATA3/system01.dbf    SYSTEM

         2 /u01/oracle/oradata/ORA-DATA3/undotbs01.dbf   ONLINE

         3 /u01/oracle/oradata/ORA-DATA3/sysaux01.dbf    ONLINE

         4 /u01/oracle/oradata/ORA-DATA3/users01.dbf     ONLINE

===============================================

IF

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

Some more useful links:

Sharing: A Company's Experience

How to check and get details about patch applied

Important DBA Scripts

R12.2 ADOP Patching Phases Finalize and Cutover

TKPROF Concepts with Syntax and Real time example

Why We are not Rich