Monday, March 21, 2016

password_verify_function

The Password_Verify_function will verify the password has been used ever. If it is enabled then we can not reuse the same password for alerting a user. There may be some situations still we need to reset the password to same password i.e older password. Here we go as below.
Since Oracle 11g has introduced new Password policies like PASSWORD_LIFE_TIME=180 days,PASSWORD_VERIFY_FUNCTION= VERIFY_FUNCTION_11G which was not there in Oracle 10g.

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty.


1) Identify the PROFILE assigned to the user you want to change the password.

select username,password,account_status,profile from dba_users where USERNAME='&USERNAME';

2) select * from dba_profiles where PROFILE_NAME='Profile name retrieved from step 1'; Identify the values assigned to resource name PASSWORD_VERIFY_FUNCTION

3) alter the profile with Password_Verify_function as null;

alter profile PROFILE_NAME limit password_verify_function null;

4) Now change the password.

alter user username identified by password; //If we know the old password

alter user username identified by values '******'; //If we don't know the password please use the below query or use the values found from step 1

e.g.

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' Query
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: remote_dba
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('remote_dba')

Query
--------------------------------------------------------------------------------
alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

SQL> alter user "REMOTE_DBA" identified by values 'F894844C34402B67'


5) change password_verify_function to older value.

ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT} //Found in step 2.


Data migration using database link

1) Connect to the target database and Create a user in target database

2) Grant select any table privilege to the user who has been created in step 1.

3) Create a Private database link from target to source using the username created in step 1

4) AS the user in target database,

CREATE TABLE new_table AS (SELECT * FROM owner.table_name@DB_LINK);
  insert into tablename select * from owner.source table_name@DB_LINK;
Gather schema/table stats
Verify the data with source database

Sunday, March 20, 2016

Enabling X11 Forwarding

Login to where you can access the GUI
Open the terminal
Click on SSH
Click on X11
Select “Enable X11 forwarding”
6          Login with individual ID
Hidden file namely Xauthority will be created under /home/<your user id>
Echo $DISPLAY
Run /usr/openwin/bin/xauth list, Will display the x11 session cookies.
Sudo su – oracle
/usr/openwin/bin/xauth add <Entire line from the above step)
Echo $DISPLAY
Export DISPLAY=<output of Step 9>
Echo $DISPLAY
Type xclock or /usr/openwin/bin/xclock

Killing a datapump job


1) select * from dba_datapump_jobs;

Here we need to identify the the job_name with status as EXECUTING.

2) expdp attach=EXPFULL  #JOB_NAME

3) Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Export>

4) select * from dba_datapump_jobs;

Query the above command and make sure there is no more the job found in step 1.

Truncating a table

Today we received a request to truncate the table having 0 records but truncate taking too long time.

Investigation went in the following way:

1) Identify if there are any locks on the table, and gather the session information holds the lock on the table or issue the truncate command and in another session check for the blocking sessions then we get the session information which is blocking truncate the table.

2) I found a session is active on the table and session is doing nothing by checking event column from v$session and session is two months older.

3) Since session was doing no productive work and got confirmation to kill the session. I have killed the session and got replied as session marked for kill and status of the session is killed but still couldn't truncate the table.

4) Waited for day, Still the same issue persists.

5) Identified SPID of the session and found still the os process exists in the server for the respective session.

6) Killed the os process and able to truncate the table.

Interview Questions

1) What are the precautions to be used before truncating a table?

A) We need to disable all the constraints associated to the table and need to drop the triggers on the table.

2) We have restored and recovered the database with 11.2.0.3 backup on 11.2.0.4 successfully, Can we open the database with reset logs?

A) No, We can not open the database with reset logs. We need to open the database as alter database open resetlogs upgrade and then upgrade the database to 11.2..4 by running running  catupgrd.sql. Then Startup the database and check the invalid objects in the database.

3) Are the password case sensitive or case in-sensitive in Oracle 11g?

A) The passwords from 11g are case sensitive which are controlled by sec_case_sensitive_logon initialization parameter, If the value is true then will enforce the case sensitivity. From 11g on wards the password column returns nothing.

4) Can we alter the user's password to same password?

A) Yes, We can do it from 11g by alerting the password_verify_function resource to null.

5) Do we take online backup using RMAN when database is in no archive log mode?

A) No, The database must and should running archivelog mode to take online backup using RMAN or hot backup.

6) Suppose I have a table with three columns say column A, Column B and Column C, among them Column A is primary key column and column B is not null column. Now I’m going to insert the data in to the table, Could you please let me know what are the columns must and should have some data in the insert statement?

A) Column A and Column B Since Constraints are imposed on the columns.

7) If I missed value for Column C, Will the insert statement will complete?

A) Yes

8) What value will be stored in Column C?

A) Nothing.

9) Are the database passwords are case sensitive from which version?

A) Oracle 11g


Unable to open alertlog with vi editior

When we open alertlog file or a file with huge size with vi we may get the following error

Out of memory saving lines for undo - try using ed

The following command would resolve the issue.

vi -y 9999999 alert_$ORACLE_SID.log

Tue Oct 26 13:59:12 2010
Starting ORACLE instance (normal)
sskgpgetexecname failed to get name
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
"alert_DB01.log" 612662 lines, 20459538 characters