How to change user's password in Oracle 11g and change back it to original

How to change user's password in Oracle 11g and change back it to original

Imp points
(1) Note down current password found in data dictionary.
(2) Modify password
(3) Do required tasks
(4) Reset the password


Get current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','TEST') FROM dual;

   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

Password is in encrypted format.

Now change password temporarily:-
SQL> alter user TEST identified by 1234;

Login to the user with new password:-
SQL> conn test/1234
Connected.

Once the job is done we can change password back to the original :-

SQL> alter user TEST identified by values 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';


DONE

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

ERROR :

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Solution:-

Create System Environment variable in that PC with following name and value :

Name : JAVA_TOOL_OPTIONS
Value : -Djava.vendor="Sun Microsystems Inc."

Table Lock

>> TABLE LOCK

select * from dba_waiters;

select * from dba_blockers;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S where SID='< >';

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

++++++++++++++++++++++++++++++++++++++++++++++++

>> Table Lock

1)     Run the Following Script to Get Locked Tables in Session
BEGIN
   DBMS_OUTPUT.enable (1000000);

   FOR do_loop IN (SELECT session_id,
                          a.object_id,
                          xidsqn,
                          oracle_username,
                          b.owner owner,
                          b.object_name object_name,
                          b.object_type object_type
                     FROM v$locked_object a, dba_objects b
                    WHERE xidsqn != 0 AND b.object_id = a.object_id)
   LOOP
      DBMS_OUTPUT.put_line ('.');
      DBMS_OUTPUT.put_line ('Blocking Session : ' || do_loop.session_id);
      DBMS_OUTPUT.
       put_line (
            'Object (Owner/Name): '
         || do_loop.owner
         || '.'
         || do_loop.object_name);
      DBMS_OUTPUT.put_line ('Object Type : ' || do_loop.object_type);

      FOR next_loop
         IN (SELECT sid
               FROM v$lock
              WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
      LOOP
         DBMS_OUTPUT.put_line ('Sessions being blocked : ' || next_loop.sid);
      END LOOP;
   END LOOP;
END;

2)     Get SID & Serial Number to Kill Session

SQL> select * from v$session where sid in 'SID,SERIAL#'
SQL> alter system kill session 'SID,SERIAL#'

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

NOTE:848387.1 - With NUMA Enabled, Database Fails To Open With ORA-600[ksbmoveme4], ORA-27300, ORA-27301, ORA-27302 Errors Reported
NOTE:300956.1 - Ora-27302: sskgxpsnd1 - Starting Instance
NOTE:314179.1 - Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302
NOTE:3411021.8 - Bug 3411021 - Oracle process may die due to ORA-27300/ORA-27301/ORA-27302 at skgxppost1
NOTE:746888.1 - ORA-27302: Failure Occurred at: skgxpvfymmtu Signalled in the Alert.log
BUG:7620133 - HIGH NUMBER OF DBW* PROCESSES SPAWNED IN 11.1.0.7
NOTE:458442.1 - 10.2.0.3 PMON Crashes on Startup on AIX 5L 5.3 ML05 -- Works on ML06
NOTE:277399.1 - DBMS_SCHEDULER Fails To Execute Jobs With Program_type EXECUTABLE On HP-UX
NOTE:392006.1 - Ora-27300 OS system dependent operation:fork failed with status: 11
NOTE:295832.1 - Unable To Create Database Ora-00603, ORA-27300
NOTE:1252265.1 - ORA-27300 ORA-27301 ORA-27302: failure occurred at: skgpalive1
NOTE:438205.1 - ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash
NOTE:453959.1 - Cannot Connect As "/ as sysdba" ORA-27140 ORA-27300 ORA-27301 ORA-27302 And ORA-27303 In Trace File
NOTE:356640.1 - ORA-27300, ORA-27301, ORA-27302: Failure Occurred At: Skgpalive1 During 'Shutdown'
NOTE:465002.1 - Database Crash With Error ORA-00490
NOTE:466370.1 - ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: OS failure message: Not enough storage ORA-27300 ORA-27302
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:371074.1 - ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
BUG:7232946 - ORA-600[KSKRECONFIGNUMA2] CAUSES INSTANCE CRASH
NOTE:580552.1 - Database Crashes With ORA-04030 ORA-07445 ORA-27300 ORA-27301 ORA-27302
NOTE:557153.1 - ORA-27370 ORA-27301 (Permission denied) When Running Job Of Type EXECUTABLE
BUG:3411021 - LMD PROCESS CRASH DUE TO ORA-27300/ORA-27301/ORA-27302 AT SKGXPPOST1
NOTE:560309.1 - Database Cannot Start Due to Lack of Memory
BUG:6991131 - ORA-27300, ORA-27301 AND ORA-27302 HAPPENED DURING SHUTDOWN USING SRVCTL
NOTE:364353.1 - ORA-00603 ORA-27504 ORA-27300 ORA-27504 in the Alert Log
NOTE:1274030.1 - Startup Instance Failed with ORA-27140 ORA-27300 ORA-27301 ORA-27302 and ORA-27303 on skgpwinit6
NOTE:949468.1 - Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28
NOTE:6629265.8 - Bug 6629265 - Intermittent ORA-27504 / ORA-27300 ioctl error 11
BUG:9059053 - ORA-00603 ORA-27504 ORA-27300 ORA-27301 ORA-27302 IN ALERT.LOG
NOTE:1038058.6 - LIST OF UNIX ERRORS AND EXPLANATIONS
NOTE:297498.1 - Resolving Instance Evictions on Windows Platforms
BUG:10010310 - ORA-27300: INVALID_PROCESS_ID FAILED WITH STATUS: 0; ORA-27302: SKGPALIVE1
NOTE:6441119.8 - Bug 6441119 - Instance crash due to ORA-27300 / ORA-27152

Create a new control file without backup

Create a new control file without backup

Start the database in NOMOUNT mode. For example, enter:

STARTUP NOMOUNT

Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option . The following example assumes that the character set is the default US7ASCII:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1 (
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
     GROUP 2 (
       '/diska/prod/sales/db/log2t1.dbf',
       '/diskb/prod/sales/db/log2t2.dbf'
     )  SIZE 100K,
DATAFILE
     '/diska/prod/sales/db/database1.dbf',
     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):

RECOVER DATABASE

Open the database after recovery completes (RESETLOGS option not required):

ALTER DATABASE OPEN;

Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;