How to change SYS / SYSTEM password

1) Backup the pwd file or folder :$ORACLE_HOME/dbs/orapw$ORACLE_SID


2) Run the below command as Oracle User   give the correct path and the file name... 
      orapwd file=<path>orapwSID password=oracle entries=5


3) Login sys as sysdba and check the new password.






Some Info...........


Create a new password file:
orapwd file=orapwSID password=oracle entries=5


If the password file already exists:
orapwd file=orapwSID password=oracle entries=5 FORCE=Y


Default location and file name:


The default location for the password file is:$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix
and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

How to see the uptime for an Oracle Database?

Run the below query in the SQL prompt..

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;

You can see the startup time from the Oracle EM home page also..

Scheduler Jobs - Error!!!

When trying to execute a scheduled job you may get below error sometimes in Oracle EM. ERROR

java.sql.SQLException: ORA-02800: Requests timed out ORA-06512: at "SYS.DBMS_ISCHED", line 150 ORA-06512: at "SYS.DBMS_SCHEDULER", line 441 ORA-06512: at line 2 


This is a bug in Oracle. Most of the times, restarting Oracle database may fix the issue.
Basically, an existing bug needs the oracle database restarts in a regular basis.
Restarting oracle database will flushes the database buffer cache.

Initialization parameter JOB_QUEUE_PROCESSES

The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance.
However, we should not assume that all job queue processes are available for job execution.

The coordinator process is not started if the initialization parameter JOB_QUEUE_PROCESSES is set to 0.
 The maximum number of processes that can be specified is 1000.

The JOB_QUEUE_PROCESSES initialization parameter is dynamic and it can be modified by an ALTER SYSTEM statement. 

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

SYSDBA / SYSOPER Privileged Users

Use below query to find out SYSDBA / SYSOPER Privileged Users in your Oracle DB.



select * from v$pwfile_users

Get the value which is before the MAX value (Use of RANK function)

When come to a scenario where we want to get the value which is just before the MAX value we cant use rownum.It will work for only 1st row.
But we can use RANK function to rank the ordered values and then select the desired rank.In this case rank 2.


Eg:

select UserID,Value
from (select UserID,Value,
      rank() over (partition by UserID order by Value Desc) RNK
      from t)
where rnk=2