ORA-01261: Parameter Db_Recovery_File_Dest Destination String Cannot Be Translated ORA-01262: Stat Failed On A File Destination Directory Linux-X86_64 Error: 2: No Such File Or Directory

December 3, 2016

By postgresdba

While I start my database with my initialization parameter it fails with oracle error

Solution :-

su oracle

run environment variable

sqlplus / as sysdba

SQL>create pfile from spfile;

Then remove or comment below line in pfile

db_recovery_file_dest

Save and exit

su oracle

run environment variable

sqlplus / as sysdba

SQL>startup mount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2166536 bytes

Variable Size 427819256 bytes

Database Buffers 624951296 bytes

Redo Buffers 14000128 bytes

it should became the mount stage then open the DB as follows

SQL>alter database open;

Database altered.

Change The Database Instance After Installation In Oracle Using Nid Utility

December 3, 2016

By postgresdba

1. Shut down the database

2. Startup mount database

3. Run the nid utility as below and also as below recommended please change the parameter file for the dbname parameter and you cna open up the database

nid target=sys/oracle dbname=NIJAM setname=YES

DBNEWID: Release 11.2.0.3.0 – Production on Wed Dec 24 09:09:19 2014

Connected to database BENZ (DBID=790652138)

Connected to server version 11.2.0

Control Files in database:

/u02/app/benz/control/control1.ctl

/u02/app/benz/control/control2.ctl

/u02/app/benz/control/control3.ctl

Change database name of database BENZ to NIJAM? (Y/ [N]) => Y

Proceeding with operation

Changing database name from BENZ to NIJAM

Control File /u02/app/benz/control/control1.ctl – modified

Control File /u02/app/benz/control/control2.ctl – modified

Control File /u02/app/benz/control/control3.ctl – modified

Datafile /u02/app/oracle/benz/system01.db – wrote new name

Datafile /u02/app/oracle/benz/sysaux01.db – wrote new name

Datafile /u02/app/oracle/benz/undotbs01.db – wrote new name

Datafile /u02/app/oracle/benz/user01.db – wrote new name

Datafile /u02/app/oracle/benz/apex01.db – wrote new name

Datafile /u02/app/oracle/benz/ggs_data01.db – wrote new name

Datafile /u02/app/benz/temp01.db – wrote new name

Control File /u02/app/benz/control/control1.ctl – wrote new name

Control File /u02/app/benz/control/control2.ctl – wrote new name

Control File /u02/app/benz/control/control3.ctl – wrote new name

Instance shut down

Database name changed to NIJAM.

Modify parameter file and generate a new password file before restarting.

Successfully changed database name.

DBNEWID – Completed successfully.

SQL> select name, open_mode, database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

——— ——————- —————-

NIJAM READ WRITE PRIMARY

4. Change db_name in spfile (or in pfile editing the file):

Alter system set db_name=newname scope=spfile;

5. Recreate password file:

orapwd file=orapwNIJAM password=oracle

6. Startup the database

Startup

7. Post rename steps:

Change SID in listener.ora
Correct tnsnames.ora
Remove old trace directories

Change /etc/oratab (UNIX) or rename windows service using oradim

Check The Oracle Database Version

December 3, 2016

By postgresdba

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Maximum Key Length (3215) Exceeded While Doing Rebuild Ora-00604,Ora-01450: In Oracle

December 3, 2016

By postgresdba

SQL> Alter index SAI.TESTVALUE_IDX rebuild online;

Alter index SAI.TESTVALUE_IDX rebuild online *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

Cause of the Problem

When creating an index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.

If 2K block size then maximum index key length=758

If 4K block size then maximum index key length=1578

If 8K block size then maximum index key length=3218

If 16K block size then maximum index key length=6498

How the maximum index key length is measured by?

Maximum index key length=Total index length (Sum of width of all indexed column + the number of indexed columns) +Length of the key (2 bytes) +ROWID (6 bytes) +the length of the rowid (1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead (Block Header, ROW Directory, Table Directory, etc.). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem

1) Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2) If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3) Rebuild the index without online clause. That is ALTER INDEX index_name REBUILD;

Because the online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

Ora-12542: Tns:Address Already In Use

December 3, 2016

By postgresdba

solution

D:\oracle\product\10.2.0\db_1\network\admin\listener.ora

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(PROGRAM = extproc)

)

LOG_DIRECTORY_LISTENER = G:\Listener_Trace

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1523))

)

And on client side, map net service names to recently added listener protocol address. i.e. 1523, as below:

NEWPASS_29 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1523))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NEWPASS)

)

after made above changes to listener.ora and tnsnames.ora, TNSPING was successful and able to create DB-link.

You may face mentioned error in following cases:

New installation
listener.ora file has been edited manually since the last listener restart
TCP port number is duplicated across the list of ADDRESS configurations in the listener.ora file.

Need Hr Job

December 3, 2016

By postgresdba

i have 10 years of exp in bpo(business analyst)

contact 7299547706

qulification B.com

Fragmentation Defragementation In Table Level

December 2, 2016

By postgresdba

The rows are split into more than one block is called fragmentation. it decrease the block accesses and it slow the database performance. fragmentation are two type one is table fragmentation and second is index fragmentation.

TABLE FRAGMENTATION:

Each table have the blocks and it stored in the data files. The data will store in the block level. When the data split into more than one blocks is called table fragmentation. The table get more update and delete statement means table will get fragmentation.

When table getting full table scan in the that time oracle will read the data upto HWM (HIGH WATER MARK LEVEL). HWM is using to find used block and free block in table.

ROW CHAINING:

When inserting the row, in that time the block does not have any space means oracle allocate the next block for that inserting its called the row chaining.

ROW MIGRATION:

When updating the row in block the update not fit into the existing block it go to another block is called row migration. it based on pctfree. Because pctfree it low means the block get row migration.

FINDING THE FRAGMENTATION IN TABLE:

Create one table and add 2 column in that table.
Insert max 1000000 records in that table and commit it.
Now do some delete operation in that table and analyze the table using estimate or compute statistics or dbms_space.space_usage package.
Now check the info in dba_tables using query

(SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;)

Its show the num_rows and chain_cnt, if u get any values in chain_cnt means fragmentation is accured.
Using dbms_space.space_usage

Steps to Check and Remove Table Fragmentation:-

1. Gather table stats:

To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

2. Check Table size:

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

3. Check for Fragmentation in table:

SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;

(or)

set pages 50000 lines 32767

select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’

/Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. DEFRAGMENTATIONS (or) remove fragemenation

1. move table to another tablespace

2. export and import the table(exp/imp)

3. shrink command (fron oracle 10g)

(shrink command is only applicable for tables which are tablespace with auto segment space management)

4. CTAS method

5.online redefinition

option: 1 move table to another tablespace

alter table

enable row movement;

alter table

move tablespace ;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

REBUILD ALL INDEXES:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

Here, value in status field may be valid or unusable.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

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

UNUSABLE INDEX_NAME

Here UNUSABLE status going to valid

SQL> alter index rebuild online;

Index altered.

Here, value in status field must be valid.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

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

VALID INDEX_NAME

Gather table stats:

SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);

PL/SQL procedure successfully completed.

Check Table size:

table size will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

Check for Fragmentation in table:

set pages 50000 lines 32767

Option: 2Export and Import the table(exp/imp)

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\benz.dmp tables=benz

Export: Release 10.1.0.5.0 – Production on Sat Jul 28 16:30:44 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …

. . exporting table BENZ 468904 rows exported

Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:12 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> truncate table benz;

Table truncated.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\benz.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:54 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SCOTT

. . importing table “BENZ” 468904 rows imported

Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:32:21 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BENZ’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42542.27kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’BENZ’,-

> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 3 Shrink command (fron Oracle 10g):-

Shrink command:

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

step to shrink

SQL> alter table

enable row movement;

Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

Part 1: Rearrange (All DML’s can happen during this time)

SQL> alter table

shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table

shrink space;

Table altered.

2. Directly reset the HWM:

SQL> alter table

shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Advantages are:

1. Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.

Option: 4 CTAS method

Examples:-

SQL> create table benz as select * from nijam;

Table created.

SQL> drop table nijam purge;

Table dropped.

SQL> rename benz to nijam;

Table renamed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 68986.97kb

SQL> select status from user_indexes

2 where table_name = ‘NIJAM’;

no rows selected

Note:- we need to create all indexes.

Option:5 online redefinition examples

SQL> create table NIJAM (

2 no number,

3 name varchar2(20) default ‘NONE’,

4 ddate date default SYSDATE);

Table created.

SQL> alter table nijam add constraint pk_no primary key(no);

Table altered.

SQL> begin

2 for x in 1..100000 loop

3 insert into nijam ( no , name, ddate)

4 values ( x , default, default);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_nijam

2 after insert on nijam

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> select count(*) from nijam;

COUNT(*)

———-

100000

SQL> delete nijam where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 822.69kb

Minimum Privs required “DBA” role or “SELECT” on dbms_redefinition pkg

First check table is condidate for redefinition.

SQL> exec sys.dbms_redefinition.can_redef_table(‘SCOTT’,-

> ‘NIJAM’,-

> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

After verifying that the table can be redefined online, you manually crea

te an empty interim table (in the same schema as the table to be redefined)

SQL> create table BENZ as select * from nijam WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

This procedure keeps the interim table synchronized with the original tab

SQL> exec sys.dbms_redefinition.sync_interim_table (‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> –Create PRIMARY KEY on interim table(BENZ)

SQL> alter table BENZ

2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_benz

2 after insert on benz

3 begin

4 null;

5 end;

6 /

Trigger created.

Disable foreign key on original table if exists before finish this proces

SQL> exec sys.dbms_redefinition.finish_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 841.4kb

SQL> select status,constraint_name

2 from user_constraints

3 where table_name = ‘NIJAM’;

STATUS CONSTRAINT_NAME

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

ENABLED PK_NO1

SQL> select status ,trigger_name

2 from user_triggers

3 where table_name = ‘NIJAM’;

STATUS TRIGGER_NAME

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

ENABLED TRI_BENZ

SQL> drop table BENZ PURGE;

Table dropped.

Tablespace Management In Oracle Version

December 1, 2016

By postgresdba

There are two type of tablespace management LMT and DMT.

LMT:(local managed tablespace)
It came from 9i. the block information(free or used) will maintain in datafile header as 0’s & 1’s.

DMT:(dictionary managed tablespace)
The extent information will maintain in data dictionary as FET$ and UET$.

DIFFERENCE BETWEEN LMT AND DMT:

No	DMT	LMT
1 2 3 4	free list maintain in data dictionary as FET$,UET$ high I/O process will happen manual coalesce more undo generated	free list maintain in data file header as 0’s & 1’s less I/O process will happen auto coalesce Less undo generated

EXTENT MANAGEMENT:
collection of blocks is called as extent.
using local managed tablespace the extent size can be managed like UNIFORM or

AUTOALLOCATE.
UNIFORM:
The extent size will allocated as same size for every new extent.

AUTOALLOCATED:
It take the default values base on storage parameters.

(STORAGE PARAMETERS IN EXTENT MANAGEMENT):

Intial   – first extent values
Next – next values in segments
Min Extents – minimum amount of extent get created at a time of table/segment.
Max Extents   – unlimited
Pct Increase – 50% of increase for next extent value.
Logging -it allow all sql statement to write in online redo logs except (select).
No-logging   – it opposite to logging and when giving the rebuild index,cts,insert into table from select statement this ill not write in online redo logs.
Blocksize – this parameter is using to specifies the block size .
Online –   it show the tablespcae in online .
Offline – after creation tablespace it show in offline.
Permanent   – it hold the permanent database objects.
Temporary   – it hold the temporary databasse objects.
(next extent/growth=prev.extent + (pct increase x prev.extent)—- this is formula for 3rd extent

In DMT Tablespace:

create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management dictionary default storage (intial 1m next 2m minextents 1 maxextents 10 pct increase 50%);

In LMT Tablespace:
create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management local uniform size 1m;

create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management local autoallocate;

SEGMENT MANAGEMENT:
Collection of extents is called as segment.
It using to managed the free and used space with in a segments. it can mention as

MANUAL or AUTO.

MANUAL:
mention the data block size for each segments using the space parameter.

AUTO:
It take automatic values based on data blocks.
It managed the free space automatically.

(SPACE PARAMETER IN SEGMENT MANAGEMENT):
Pct free – 10% for updates.
Pct used – to find the block where free or used.
Init trans – intial transaction for each block
Max trans – max transaction for each block

In DMT Tablespace (manual):
create table san_tab(id number(9),name varchar2(9) pctfree 20 pctused 50 inittrans 3 maxtrans 10;

In LMT Tablespace(auto/manual):
create table san_tab(id number(9),name varchar2(9));

VIEWS:

DBA_EXTENTS
USER_EXTENTS
DBA_DATA_FILES
V$DATAFILE
V$TABLESPACE
DBA_FREE_SPACE
DBA_SEGMENTS
USER_SEGMENTS
DBA_TABLESPACES
USER_TABLESPACES

ASSIGNMENT QUESTIONS:
1.increase the max extent.
2.find the 3rd extent size.
3.create LMT tablespace using storage parameter(nologging).
4.how will you change the extent size,it is possible or not.

INTERVIEW QUESTIONS:
1.find the num of extent in a table.
2.convert the dmt tablespace into lmt tablespace.
3.fine the tablespace where lmt or dmt.
4.diff between lmt and dmt.
5.how will u change the extent size.
6.when show error like (unable to create extent)what u do?.
7.find block size and extent size in particular table or segments.

Tablespaces Monitoring,Auditing,Add,Delete,Rename,Drop,Views Relocate -Oracle

December 1, 2016

By postgresdba

A database is divided into One or more logical storage units called tablespace.
collection of datafiles is called as tablespace.

TYPES OF TABLESPACE:

Permanent Tablespaces
Undo Tablespace
Temporary Tablespaces

1.PERMANENT TABLESPACE:

(Users data files and normally contains the system (data dictionary) and users data)

System

Is a permanent tablespace and contains the vital data dictionary (metadata about the database).
It created when database is created.
It always in online.
It stores tables that support the core functionality of the database such as the data dictionary tables.
Cannot rename or drop the system tablespace

Sysaux

Its introduce in oracle 10g.
Is an auxiliary tablespace to the system tablespace and contains performance statistics collected by the database.(ex:logminar,oracle streams)
It cannot be dropped or renamed.
Sysaux tablespace may be taken offline for performing tablespace recovery.

USER DEFINED TABLESPACES:

Is used to store organizational data in table accessed by application programs.
Flexibility in database administration.
Separate data by backup reruitrmrnts.
Separate dynamic and static data to enable database tuning.
Control space allocation for both applications and system users.
Reduce contention for input/output path access.

Create tablespace:

Syntax:

Create tablespace data_tbs datafile ‘/home/oracle/data/data_tbs.dbf’ size 200m;

Adding datafile :

Syntax:

Alter tablespace data_tbs add datafile ‘/home/oracle/data/data02_tbs.dbf’ size 200m;

Renaming tablespace:

Syntax:

Alter tablespace data_tbs rename to data_old_tbs;

Dropping tablespace:

Syntax:

Drop tablespace data_tbs;

Resize the datafile :

Syntax:

Alter database datafile ‘/home/oracle/data/data_tbs.dbf’ resize size 300m;

Relocating tablespace/datafile:

Syntax:

Step 1 shut the database

Step 2 use copy command to move the phy file to new location

Step 3 mount the database

Step 4 excute this command

Alter tablespcae data_tbs rename datafile ‘/home/oracle/data/data_tbs.dbf’ to ‘/home/oracle/king/data_tbs.dbf’;

Quotas on tablespces:

Syntax:

Alter user schema01 quota 50m on data_tbs;

Read only tablespace:

Syntax:

Alter tablespace data_tbs read only;

Offline/online tablespace:

Syntax:

Alter tablespace data_tbs offline;

Alter tablespace data_tbs online;

Assing tablespace to user:

Syntax:

Alter user schema01 default tablespace data_tbs;

Bigfile tablespace:

It is new feature of oracle 10g and it contain the single datafile and allocate large size up to 4g.

It reduce the disk I/O.

Syntax:

Create bigfile tablespace userdata datafile ‘/home/oracle/data/userdata.dbf’ size 10g;

Resize the bigfile tablespace:

Syntax:

Alter tablespace userdata resize 20g;

Tablespace creation in ASM:

syntax:

create tablespace sainora datafile ‘+dg1’ size 200m;

Adding datafile in ASM:

syntax:

alter tablespace sainora add datafile ‘+dg1’ size 200m;

Droping tablespace in ASM:

syntax:

drop tablespace sainora;

Drop datafile in ASM:

syntax:

alter tablespace sainora drop datafile ‘+dg1/sainora.dbf ‘;

VIEWS:

DBA_DATA_FILES

V$DATAFILE

DBA_SEGMENTS

DBA_USERS

DBA_FREE_SPACE

DBA_TABLESPACES

V$TABLESPACE

ASSIGNMENT QUESTIONS:

1.create tablespace size with 2m and insert the values above 10,00,000.

2.add the datafile in tablespace.

3.create tablespace without giving the datafile location.

4.rename the tablespace name give syntax.

5.find the default location of tablespace.

INTERVIEW QUESTIONS:

1.create the tablespace size with 1gb.

2.How to assign quota to more then one user for same tablespace.

3.How will u drop the system tablespace.

4.resize the sysaux tablespace give syntax.

5.find the tablespace size and status.

6.find the datafile location and size.

7.find how many datafile is there in a single tablespace.

8.how to check the used space of tablespace.

9.find the free space of tablespace.

10.use of bigfile tablespace.

AMM (Automatic Memeory Managment) 11g Oracle

December 1, 2016

By postgresdba

The basic memory structures associated with Oracle Database include:

System Global Area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes.

Program Global Area (PGA)

A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total PGA memory allocated for all background and server processes attached to an Oracle Database instance is referred to as the total instance PGA memory, and the collection of all individual PGAs is referred to as the total instance PGA, or just instance PGA.

It contains global variables and data structures and control information for a server process. example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.

The performance of complex long running queries, typical in a DSS environment, depend to a large extent on the memory available in the Program Global Area (PGA) which is also called work area.

Below figure illustrates the relationships among these memory structures.

Oracle Memory Structure

Memory management has evolved with each database release

Oracle 9i

Beginning with Oracle9i, the dynamic SGA infrastructure allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. Key features being

Dynamic Memory resizing
DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS
DB_nK_CACHE_SIZE for multiple block sizes
PGA_AGGREGATE_TARGET Introduction of Automatic PGA Memory management

Oracle Database 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g

Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

The most important SGA components are the following:

Database Buffer Cache:

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_nK_CACHE_SIZE

Redo Log Buffer:

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

LOG_BUFFER

Shared Pool:

The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

RESULT_CACHE_SIZE *

Large Pool:

Used for allocating session memory for shared server, Oracle XA, or parallel query buffers or for RMAN.

LARGE_POOL_SIZE

Java Pool :

Java pool memory is used in server memory for all session-specific Java code and data within the JVM.

JAVA_POOL_SIZE

Streams Pool :

The streams pool is used exclusively by Oracle Streams. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.

STREAMS_POOL_SIZE

RESULT_CACHE_MAX_SIZE is new component which has been introduced as part of 11g Memory architecture. The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.Results of queries and query fragments can be cached in memory in the SQL query result cache. The database can then use cached results to answer future executions of these queries and query fragments. Similarly PL/SQL Function Result can also be cached.

You have to use RESULT_CACHE_MODE initialization parameter which determines the SQL query result cache behavior. The possible initialization parameter values are MANUAL and FORCE.

You can use various memory management types from following:

For Both the SGA and Instance PGA – Automatic Memory Management

For the SGA – Automatic Shared Memory Management

For the Instance PGA – Automatic PGA Memory Management

1.Automatic Memory Management (SGA & PGA)

Oracle Database can manage the SGA memory and instance PGA memory completely automatically by setting 2 parameters, MEMORY_MAX_TARGET and MEMORY_TARGET. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.

Switching to Automatic Memory Management

1)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL> show parameter TARGET

NAME TYPE VALUE

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

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

parallel_servers_target integer 256

pga_aggregate_target big integer 1G

sga_target big integer 1G

SQL> show parameter cache_size

NAME TYPE VALUE

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

client_result_cache_size big integer 0

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 64M

db_4k_cache_size big integer 64M

db_8k_cache_size big integer 0

db_cache_size big integer 1520M

db_flash_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

Add the values of pga_aggregate_target and sga_target.

2)Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 808M

3)Change the parameter in initialization parameter file.

Using Spfile

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 8G SCOPE = SPFILE;

SQL>ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE = SPFILE;

SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;

SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

Using Pfile

If you have started the instance with Pfile, then edit the pfile and set the parameters manually

MEMORY_MAX_TARGET = 8G

MEMORY_TARGET = 8G

SGA_TARGET =0

PGA_AGGREGATE_TARGET = 0

In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. BUT If you only set MEMORY_MAX_TARGET the database will not be in AMM mode, till you set MEMORY_TARGET

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

MEMORY_MAX_TARGET is a static parameter i.e it cannot be changed Dynamically and Instance has to be bounced for modifying the value. So ensure that you have set it to appropriate value. I have changed values to the following for demonstration purpose.

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 8G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SGA_MAX_SIZE = 7G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SGA_TARGET = 7G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SHARED_POOL_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET JAVA_POOL_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET DB_CACHE_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET DB_CACHE_SIZE = 0 SCOPE=SPFILE

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero

If you get above error then try:

SQL> ALTER SYSTEM RESET DB_CACHE_SIZE SCOPE=SPFILE;

System altered.

ALTER SYSTEM SET DB_4K_CACHE_SIZE = 0 SCOPE=SPFILE;

ALTER SYSTEM SET DB_32K_CACHE_SIZE = 0 SCOPE=SPFILE;

I have to adjust the memory manual if I need to set the lower limit even when I am using AMM

4)Shutdown and startup the database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size 2214416 bytes

Variable Size 2281702896 bytes

Database Buffers 5167382528 bytes

Redo Buffers 31326208 bytes

SQL> show parameter target

NAME TYPE VALUE

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

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 8G

memory_target big integer 8G

pga_aggregate_target big integer 1G

sga_target big integer 7G

The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Monitoring and Tuning Automatic Memory Management

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

SQL> select * from v$memory_target_advice order by memory_size;

You can also use V$MEMORY_RESIZE_OPS which has a circular history buffer of the last 800 SGA resize requests.

2. Automatic Shared Memory Management – For the SGA

If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management.This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration.

In case you have enabled Automatic Memory Management , then to switch to Automatic Shared Memory Management , please follow below procedure

SQL>Alter system set MEMORY_TARGET=0 scope=both;

SQL>Alter system set SGA_TARGET=500M scope=both;

3. Automatic PGA Memory Management – For the Instance PGA

While using Automatic memory management , PGA memory is allocated based upon value of MEMORY_TARGET. In case you enable automatic shared memory management or manual shared memory management, you also implicitly enable automatic PGA memory management.

Automatic/Manual PGA memory management is decided by initialization parameter WORKAREA_SIZE_POLICY which is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO.

With automatic PGA memory management, you set a target size for the instance PGA by defining value for parameter named PGA_AGGREGATE_TARGET and sizing of SQL work areas is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. This feature is available from 9i.

At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

In case you wish to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join) then you can enable Manual PGA Memory management.

Set WORKAREA_SIZE_POLICY value to MANUAL and also specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.

Although the Oracle Database 11g supports this manual PGA memory management method, Oracle strongly recommends that you leave automatic PGA memory management enabled.