Oracle ~ exp

Source DB: TSTDUMP1 (Contents the data which needs to be exported).
Destination DB: TSTDUMP2 (Fresh or Populated DB)

STEP 01-> Create a folder in the source DB server.
* Windows : “C:\ORACLE\DUMP\”
* Linux: “/home/oracle/dump”

 

STEP 02-> Get the dump
exp userid=system/******@TSTDUMP1 file=testdump1.dmp log=testdump1.log full=y statistics=none

**According to the requirement, the whole, partial DB can be exported.
full=y ~ This enables to export the complete Source DB.
owner=testuser ~ This enables to export only the schema “testuser”
tables=testuser.students,testuser.names ~ This enables to export tables “students” & “names” of “testuser” schema

 

STEP 03-> check the tablespaces in both to check whether they are similar
select tablespace_name from dba_tablespaces;
if not similar, need to make the necessary table spaces in Destination DB

* Windows : create tablespace importtest_ts datafile ‘C:\ORACLE\TSTDUMP1\importtest_ts01.dbf’ size 100m autoextend on;
* Linux : create tablespace importtest_ts datafile ‘/home/oracle/TSTDUMP1/importtest_ts01.dbf’ size 100m autoextend on;

 

STEP 04-> check the users at Destination DB and drop them

sqlplus sys/******@TSTDUMP2 as sysdba
select username from dba_users;

 

STEP 05-> check the users at Source DB and create in Destination DB

create user importtest identified by password default tablespace importtest_ts quota unlimited on importtest_ts temporary tablespace temp;

 

STEP 06-> Then grant the users with the required priviledges
grant create session, resource, create view, create synonym, create public synonym to importtest;

 

STEP 07-> Then simply import the dump into the Destination DB

* Windows :  C:\ORACLE\DUMP>imp userid=system/******@TSTDUMP2 file=testdump1 log=imptestdump1.log fromuser=(importtest_ts) touser=(importtest_ts) ignore=y commit=y resumable=y statistics=none
* Linux:  C:\ORACLE\DUMP>imp userid=system/******@TSTDUMP2 file=testdump1 log=imptestdump1.log fromuser=importtest_ts touser=importtest_ts ignore=y commit=y resumable=y statistics=none

 

STEP 08 -> Applying any special grants to the imported users in the Destination DB

 

STEP 09-> Compile the Destination DB and compare for invalid objects.

Advertisement

Add a sequence to an existing table

There are situations where a sequence needs to be added to an existing table’s primary key. In order to achieve this task, we can create a sequence dynamically getting the “START WITH” value from the primary column of the table.

e.g:
Table Name: students
Primary Column: student_no (integer)

DECLARE maxid INTEGER;
sql_stmt VARCHAR2 (200);
BEGIN
SELECT MAX(student_no)+1 INTO maxid FROM students;
sql_stmt := ‘CREATE SEQUENCE seq_students_no INCREMENT BY 1
START WITH ‘||maxid|| ‘
MINVALUE ‘||maxid||’
MAXVALUE 9999999999999999
NOCYCLE
NOORDER
CACHE 20′;
EXECUTE IMMEDIATE sql_stmt;
END;
/

Restore point ~ Oracle

Restore Points can be created either as a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

Database is in OPEN mode and FLASH BACK enabled.

* Log as SYS:

* Create a restore point :
SQL>create restore point INITDB guarantee flashback database;

In case, you are required to Flash back to the created restore point, following steps need to be followed.

* Shutdown the database :
SQL> shutdown immediate

* Statup the database (mount) :
SQL> startup mount

*You can view the created restore point :
SQL> select * from v$restore_point;

* Flashback to the restore point
SQL> flashback database to restore point INITDB;
Flashback complete.

* You can view the created restore point :
SQL>select * from v$restore_point;

Note: Check my note on Oracle flash back.

Flashback Database ~ Oracle

Introduction

* Flashback Database is an alternative to a point in time recovery (PITR). datafiles are brought to their contents they had at a past time.
* Old database block images are stored in a flash recovery area which allow fast rollbacks of database (as no online redo logs are required.)

* Relevant parameters:
o db_recovery_file_dest
o db_recovery_file_dest_size
o db_flashback_retention_target

* Relevant Commands:
o alter database flashback on | off
o alter tablespace flashback on | off

STEPS

* Log as SYS:
CMD>set oracle_sid=DB01
CMD>sqlplus / as sysdba

* Database should be operated in spfile mode :
SQL>show parameter spfile;

* Set the Archive Log Destination:
SQL>alter system set log_archive_dest_1=’location=C:\oracle\SAMPLE\archivefiles’;

* Change the archive log format:
SQL>alter system set log_archive_format=’arch_%t_%s_%r.log’ scope=spfile;

* Check the recovery file destination parameters:
SQL>show parameter recover

It should show the parameters like db_recovery_file_dest, db_recovery_file_dest_size, recovery_parallelism

* Change the parameter values : Ex:
SQL>alter system set db_recovery_file_dest_size=3G scope=both;
SQL>alter system set db_recovery_file_dest=’C:\oracle\SAMPLE\archivefiles\recoveryarea’ scope=both;

* Check the retention parameters:
SQL>show parameter reten

It should show the parameters like db_flashback_retention_target, undo_retention

* Change the parameter values : Ex:
SQL>alter system set db_flashback_retention_target=14400 scope=both;

* Check the lag parameters:
SQL>show parameter lag

It should show the parameters like archive_lag_target

* Change the parameter values : Ex:
SQL>alter system set archive_lag_target=3600 scope=both;

* Then do a checkpoint :
SQL>alter system checkpoint;

* Shutdown the database :
SQL>shutdown immediate;

* Statup the database (mount) :
SQL>startup mount;

* Execute following statements to enable flashback :

SQL>alter database archivelog;
SQL>alter database force logging;
SQL>alter database flashback on;

* Shutdown the database :
SQL>shutdown immediate;

* Statup the database :
SQL>startup

Note: Check my note on Oracle restore point to create and work with flash back.

ORACLE ~ Locks

Normally deadlock generates dump file and automatically is released by oracle system process:

1) check v$session
sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

2) To kill a locked session, first need to find sid, serial and use
sqlplus>alter system kill session ‘sid, serial#’;
*** you need have dba priviledge to kill sessions

3. To find which SQL has lock wait
sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

V$LOCK -> Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS-> Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS -> Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS-> Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS-> Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK-> Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL-> Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch

Oracle Sessions

Some situations rise where we need to find out the current sessions in the database and this can be viewed through v$session SYS view.

> select * from v$session

This tables gives you loads of information, schema name, application, session id etc.

By retrieving the session id, unwanted sessions can be removed (disconnected) from the DB.

>ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Monitoring SQL 2005 Mirroring

We as Database Administrators have to work with loads and loads of data, data availability is very important even in situations like hardware failures, network failures and hence, data replication plays an important part.

The production setup I am working with, has SQL 2005 mirroring replication, snapshots can be used to make sure SQL mirroring is taking place.

Database Name: SECONDARYDB

CREATE DATABASE snapshot1 ON ( NAME = ‘SECONDARYDB_DATA’, FILENAME ='<any disk location>\snapshot1.ss’ ) AS SNAPSHOT OF SECONDARYDB;

After creating a snapshot (snapshot1) , you can query for the latest changes done in the PRIMARYDB in the created snapshot.

Create a new UNDO tablespace

There can be situations were UNDO tablespace grows continuously and there is a requirement to clear this space. We cannot simply remove this tablespace and create a new, we will need to follow the below steps:

Steps

1) Switch log files

2) Create a new UNDO tablespace

CREATE UNDO TABLESPACE “UNDOTBS2” DATAFILE ‘<path>/undotbs201.dbf’ SIZE 100M AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;

3) Assign the newly created tablespace as the UNDO tablespace

alter system set undo_tablespace = UNDOTBS2;

4) Drop the old UNDO tablespace

drop tablespace “UNDOTBS” INCLUDING CONTENTS AND DATAFILES;

Note:  Use below query to check the current UNDO tablespace

select name,value from v$parameter where name in (‘undo_management’,’undo_tablespace’);

Oracle 11g password expiration

In Oracle 11g release, schema password is expired periodically, by default this is 180 days. However, if required we can alter this setting and make the password permanent. This change needs to be done to the profile of the schema, all schemas under this profile is affected.

select PROFILE from dba_users where username = ‘User you are interested in’;

ALTER PROFILE <Profile from ABOVE query> PASSWORD_LIFE_TIME UNLIMITED;