Notes

list archive log all shows all the archivelogs currently know to the controlfile.

list backup of archivelog all will show you the archives that have been backed up

there backup timestamp must be greater than the time when you started the recovery hence 5406,5407 and 5408  didn't recover as they were not backed up in the first place.

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

e.g. ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []

First Argument : kkqjpdpvpd: No join pred found.

Fast Start Failover, is avialable through dataguard broker which do automatic failover to the choosen primary database in case of the primary crash. It has a componenet called Observer which can be installed in a

seperate location other than primary and standby databases which takes very less amount of resources, Just we need oracle database s/w or Oracle Client s/w and tnsentry pointing to both primary and standby

databases. The observer will continuously monitor the primary database and due to any reason if primary is unavilable it starts failover to  choosen standby by waiting the ammount specified in FSFThreshold.

FSFLaglimit   - The maxminum ammount of time in Seconds for the permissible data loss. FSFO will runs in Max availability or Max Performance. In Max availibity no data loss where Max Permformance

            30 seconds of dataloss is permissible.

FSFThreshold - 30 Seconds, FSFO waits for 30 seconds before failing it over to the specified standby database

FSFPrmyshutdown - [TRUE|FALSE]. If the parameter is true then primary will be stalled for the FSFThreshold time and shutdowns.

FSPTarget - Using this parameter we will specify the db_unique_name of the standby database to where primary has to  failover.

FSFAutoReinitiate - [TRUE|FALSE]. If the parameter is true then former primary will start re-initiate after the fast start failover.

Fast-Start-Failover has a few restrictions:

We can not change the protection mode of the dataguard broker configuration and logshipping mode of the primary and standby databases.

We can not switchover/failover to the targets(standby databases) which are not part of the FSFTarget

The broker configuration cannot be removed, If FSFO is enabled

Standby database cannot be dropped/deleted.

FSFO is not possible if the observer is not running and.

If the database shutdown normally then FSFO will not perform failover, It will perform only if abort option is used.

Generating Oracle Query output to a csv file

--Set the linesize large enough to accommodate the longest possible line.
SET LINESIZE 9999
--Turn off all page headings.
SET PAGESIZE 20000
--Turn off feedback
SET FEEDBACK OFF
/*
-- It will not return how many number of rows were returned after executing the query.
*/
--Eliminate trailing blanks at the end of a line.
SET TRIMSPOOL ON

SET TERMOUT OFF
/*
-- The output upon running the command/script will not not returned on the standard output screen.
*/

/*
-- set your column seperator to a comma for CSV file

*/

SET COLSEP ','

SPOOL excel_readable_file.csv

Query

spool off;

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

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.