Monday 13 October 2014

DROP DATABASE INCLUDING RMAN BACKUPS IN ORACLE



Example - Deleting a Database

In this example, you want to delete a test database called
test1 that is registered in the recovery catalog. You start the RMAN client, connect to database test1 as TARGET, and connect to the recovery catalog. You then run the following commands to delete the target database files, as well as all backups, copies, and archived logs associated with the database:
RMAN> CONNECT TARGET SYS@test1

target database Password: password
connected to target database: TEST1 (DBID=3952556153453)

RMAN> STARTUP FORCE MOUNT

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;


I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!


Friday 10 October 2014

MOVE ALL DATAFILES INCLUDING SYSTEM TO DIFFERENT LOCATION


STEPS:

1) Shutdown database after creating pfile

2) Copy/move datafiles, control files to new directory at OS level

3) Startup database in mount.

4) Rename / relocate datafile at database level

5) Open the database

STEP1: SHUTDOWN DATABASE AFTER CREATING PFILE.

SQL> select name from v$datafile;

NAME

——————————————————————————–

/u04/oradata/TEST/system01.dbf

/u04/oradata/TEST/undotbs01.dbf

/u04/oradata/TEST/sysaux01.dbf

/u04/oradata/TEST/users01.dbf

SQL> select name from v$tempfile;

NAME

——————————————————————————–

/u04/oradata/TEST/temp01.dbf

STEP 1: SHUTDOWN THE DATABASE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP2: COPY DATAFILE CONTROLFILES TO NEW LOCATION AT OS LEVEL

cpsoa1:/u04/oradata/TEST> cp -p system01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p undotbs01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p sysaux01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p users01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p temp01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> pwd

/u04/oradata/TEST

STEP3: STARTUP DATABASE IN MOUNT STAGE.

SQL> startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 2085296 bytes

Variable Size 163581520 bytes

Database Buffers 352321536 bytes

Redo Buffers 6299648 bytes

Database mounted.

STEP4: RENAME OR RELOCATE DATAFILE AT DATABASE LEVEL

SQL> Alter database rename file ‘/u04/oradata/TEST/system01.dbf’ to ‘/u02/oradata/TEST/system01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/undotbs01.dbf’ to ‘/u02/oradata/TEST/undotbs01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/sysaux01.dbf’ to ‘/u02/oradata/TEST/sysaux01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/users01.dbf’ to ‘/u02/oradata/TEST/users01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/temp01.dbf’ to ‘/u02/oradata/TEST/temp01.dbf';

Database altered.

STEP5: OPEN THE DATABASE

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME

——————————————————————————–

/u02/oradata/TEST/system01.dbf

/u02/oradata/TEST/undotbs01.dbf

/u02/oradata/TEST/sysaux01.dbf

/u02/oradata/TEST/users01.dbf

SQL> select name from v$tempfile;

NAME

——————————————————————————–

/u02/oradata/TEST/temp01.dbf

 

Wednesday 8 October 2014

REBUILDING THE TABLE BY SHRINKING ITS FREE SPACE


Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a short table level lock takes place to adjust the HWM (Highwater Mark) of the table.

And it's easy!!
Just enable 'row movement' and start shrinking tables:

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;


This enables row movement of the table. This is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;

This shrinks the used space of the table, but does not update the HWM. This is useful if you don't want to have a table-level lock during business hours.

SQL> ALTER TABLE MYTABLE SHRINK SPACE;

This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;

This command shrinks the contents of the table and all dependent objects like indexes.

High Water Mark:

Now we have mentioned the High Water Mark (HWM) a couple of times. A short explanation:
The HWM is the pointer to that location of a table where on any point in history the table has grown to. If a lot of rows are deleted from a table, a lot of free space exists before the HWM. Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
This is wasted time and is worsening the performance of getting data from that table.
So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.

Triggers and indexes:

In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.

Wasted space:

How to determine if a table is a candidate for a reorganization ?

Use the below script to determine if a table has a lot of wasted space.
If it substantial in relation to the total size of the table, then a reorganization ( shrink ) is recommended.

set lines 200;
column owner format a15;
column segment_name format a30;

select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;


The above script is only working if the tables involved have statistics gathered on them

PCTFREE


An extra remark about PCTFREE.
Tables with a high value of PCTFREE show also a higher value of wasted space.
The space reserved for PCTFREE in a block can not be shrinked. So even after a shrink of a table a lot of wasted space can remain.

The effect of PCTFREE also seen before the shrink is executed when looking at the wasted space using the above script.

In an example we have three identical tables with the following structure:

SQL> CREATE TABLE T1 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 40;
SQL> CREATE TABLE T2 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 10;
SQL> CREATE TABLE T3 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 50;


All tables are loaded with the same data, approx. 5.5 million rows.
When running the above script it shows:


SEGMENT_NAME
SEGMENT_TYPE
MBS
WASTED
-----------------------
------------------
----------
----------
T1
TABLE
5064
2348
T2
TABLE
3333
617
T3
TABLE
6144
3428


Just because of the variation in the value of PCTFREE, the total size and wasted space are different per table.

The effect of PCTFREE after the shrink is that not all "wasted space" is released.
The term "wasted space" therefore is not really valid with a high PCTFREE. It is wasted space + the reserved space directly available for updates. Of course above, just after table creation there is no wasted space. It is only reserved (pctfree) space. Therefore - for this example - we are going to delete approx 30% of the rows of all tables.

SQL> delete from t1 where length(val)< 300;
SQL> delete from t2 where length(val)< 300;
SQL> delete from t3 where length(val)< 300;

As soon as the delete is finished we compute stats on these tables in order for the wasted-space script to have a valid calculation base.

Running the script again now shows:

SEGMENT_NAME
SEGMENT_TYPE
MBS
WASTED
-----------------------
------------------
----------
----------
T1
TABLE
5064
2789
T2
TABLE
3333
1058
T3
TABLE
6144
3869

After this we are going to 'shrink' the available wasted space, using:

SQL> ALTER TABLE T1 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T2 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T3 SHRINK SPACE CASCADE;

The result is:
SEGMENT_NAME
SEGMENT_TYPE
MBS
WASTED
-----------------------
------------------
----------
----------
T1
TABLE
4245
1970
T2
TABLE
2779
505
T3
TABLE
5292
3018

This test clearly shows that even if Oracle or its tools like ADDM inform you about wasted space in a table, it might be that this is because you have a high value for PCTFREE. Shrinking these table might afterwards look like if nothing happened.
Check in that case if PCTFREE is high and if the value of 'wasted space' divided by the 'total size' is equal to that value:
Example:

Table T1: PCTFREE = 40% ==> 1970 / 4245= 46%
Table T3: PCTFREE = 50% ==> 3018/5292 = 57%

So, actually the 'wasted space' of these tables is respectively 6% and 7 %


I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!