steps for Installation of oracle 11g R2 on Linux

Step 1 :-- Check the space 

[root@localhost ~]# df -kh
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G   14G  2.7G  84% /
/dev/sda1              99M   11M   83M  12% /boot
tmpfs                1003M     0 1003M   0% /dev/shm
Oracle11gR2           932G  821G  111G  89% /media/sf_Oracle11gR2
[root@localhost ~]# 



Step 2
add 2 groups "oinstall" and "dba"and 1 user "oracle"


[root@localhost ~]# groupadd oinstall
[root@localhost ~]# groupadd dba
[root@localhost ~]# useradd -g oinstall -G dba oracle
[root@localhost ~]# passwd oracle  

step 3
copy database dump in  linux 

[root@localhost home]# cp -r /media/sf_Oracle11gR2/database  /home/oracle/

Logout from Root user

loging as oracle user

step 4 
oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ls -ltr
total 8
drwxr-x--- 8 root   root     4096 Aug 25 01:05 database
drwxr-xr-x 2 oracle oinstall 4096 Aug 25 01:11 Desktop
[oracle@localhost ~]$ su - root
Password: 
[root@localhost ~]# pwd
/root
[root@localhost ~]# cd /home
[root@localhost home]# pwd
/home
[root@localhost home]# cd oracle/





[root@localhost oracle]# chown -R oracle:oinstall database
[root@localhost oracle]# ls -ltr
total 8
drwxr-x--- 8 oracle oinstall 4096 Aug 25 01:05 database
drwxr-xr-x 2 oracle oinstall 4096 Aug 25 01:11 Desktop
[root@localhost oracle]# EXIT

oracle@localhost ~]$ cd database/
[oracle@localhost database]$ ls
doc  install  response  rpm  runInstaller  sshsetup  stage  welcome.html
[oracle@localhost database]$ ls -ltr
total 36
drwxr-x--- 12 oracle oinstall 4096 Aug 25 01:02 doc
drwxr-x---  2 oracle oinstall 4096 Aug 25 01:02 sshsetup
-rwxr-x---  1 oracle oinstall 3226 Aug 25 01:02 runInstaller
drwxr-x---  2 oracle oinstall 4096 Aug 25 01:02 rpm
drwxr-x---  2 oracle oinstall 4096 Aug 25 01:02 response
drwxr-x---  4 oracle oinstall 4096 Aug 25 01:02 install
-rwxr-x---  1 oracle oinstall 5402 Aug 25 01:05 welcome.html
drwxr-x--- 14 oracle oinstall 4096 Aug 25 01:05 stage





oracle@localhost database]$ cd ..
[oracle@localhost ~]$ chmod -R 777 database 
[oracle@localhost ~]$ ls -ltr
total 8
drwxrwxrwx 8 oracle oinstall 4096 Aug 25 01:05 database
drwxr-xr-x 2 oracle oinstall 4096 Aug 25 01:11 Desktop

[oracle@localhost ~]$ cd database/
[oracle@localhost database]$ ./r
response/     rpm/          runInstaller  
[oracle@localhost database]$ ./runInstaller 
Starting Oracle Universal Installer...

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Most useful Dictionary and Views for oracle dba

Most useful Dictionary and Views for oracle dba .

Data Dictionary
Which users are in the database password file:
V$PWFILE_USERS

Where values set in the init.ora file can be viewed – all parameters:
V$PARAMETER

Script used to create the objects that comprise the data dictionary:
catalog.sql

To grant a special role to users so they can look at DBA views:
SELECT_CATALOG_ROLE

Information about all database objects in the database:
DBA_OBJECTS

Information about all tables in the database:
DBA_TABLES

Information about all indexes in the database:
DBA_INDEXES

Information about all views (including dictionary views) in the database:
DBA_VIEWS

Information about all sequences in the database:
DBA_SEQUENCES

Information about all users in the database:
DBA_USERS

Information about all constraints in the database:
DBA_CONSTRAINTS

Information about all table columns that have constraints on them:
DBA_CONS_COLUMNS

Information about all columns that have indexes on them in the database:
DBA_IND_COLUMNS

Information about all columns in all the tables in the database:
DBA_TAB_COLUMNS


Information about all the roles in the database:
DBA_ROLES

Information about all object privileges in the database:
DBA_TAB_PRIVS

Information about all system privileges granted to all users in the database:
DBA_SYS_PRIVS

Displays all PL/SQL source code in the database:
DBA_SOURCE

Information about all triggers in the database:
DBA_TRIGGERS

Information about object privileges granted to roles
ROLE_TAB_PRIVS

Information about system privileges granted to roles
ROLE_SYS_PRIVS

Information about roles granted to roles
ROLE_ROLE_PRIVS

Information about all tablespaces in the database:
DBA_TABLESPACES

Information about all profiles in the database:
DBA_PROFILES


For all parameters?
V$PARAMETER

General information about the database mounted to your instance:
V$DATABASE

Most information about the performance of the database is kept here:
V$SYSSTAT

Most information about the performance for individual user sessions is stored here:
V$SESSION , V$SESSTAT

Information about online redo logs (2)
V$LOG, V$LOGFILE

Information about datafiles
V$DATAFILE

Basic information about control files, and the two columns it has:
V$CONTROLFILE. STATUS / NAME

An object you can query to obtain a listing of all data dictionary objects (4)
CATALOG, CAT, DICTIONARY, DICT.

When the control file was created, Sequence Number, most recent SCN:
V$DATABASE

Information stored in different sections of the control file, Sequence Number:
V$CONTROLFILE_RECORD_SECTION

To see the names and locations of all control files in the db? (2)
V$PARAMETER. V$CONTROLFILE


Tablespace and Datafiles
Temporary Segments:
Name, tablespace location, and owner of temporary segments:
DBA_SEGMENTS

Size of temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. Space usage allocation for temporary segments:
V$SORT_SEGMENT

Types of sorts that are happening currently on the database
V$SORT_USAGE

To see the username corresponding with the session:
V$SESSION

Information about every datafile in the database associated with a temporary tablespace:
DBA_TEMP_FILES

Similar to DBA_TEMP_FILES, this performance view gives Information about every datafile in the database associated with a temporary tablespace:
V$TEMPFILE

Storage Structures
A summary view, contains all types of segments and their storage parameters, space utilization settings:
DBA_SEGMENTS

Tablespace quotas assigned to users:
DBA_TS_QUOTAS

Segment name, type, owner, total bytes of extent, name of tablespace storing the extent:
DBA_EXTENTS

The location and amount of free space by tablespace name:
DBA_FREE_SPACE

The location of free space in the tablespace that has been coalesced:
DBA_FREE_SPACE_COALESCED

Information about datafiles for every tablespace
DBA_DATAFILES

Performance view for information for datafiles for every tablespace
V$DATAFILE

To see the total amount of space allocated to a table?
DBA_EXTENTS

Table creation timestamp, information about the object ID:
DBA_OBJECTS

High water mark, all storage settings for a table, and statistics collected as part of the analyze (for row migration) operation on that table
DBA_TABLES

Information about every column in every table:
DBA_TAB_COLUMNS

To determine how many columns are marked unused for later removal?
DBA_UNUSED_COL_TABS

To find the number of deleted index entries ?
INDEX_STATS

To determine the columns on a table that have been indexed:
DBA_ID_COLUMNS

The dynamic view to show whether the index is being used in a meaningful way?
V$OBJECT_USAGE

To see whether a constraint exists on a particular column?
DBA_CONS_COLUMNS
To see the constraints associated with a particular table:
DBA_CONSTRAINTS

To find the username, ID number, (encrypted) password, default and temporary tablespace information, user profile of a user, password expiry date:
DBA_USERS

To all objects, which objects belong to which users, how many objects a user has created?
DBA_OBJECTS

Resource-usage parameters for a particular profile:
DBA_PROFILES

Identifies all resources in the database and their corresponding cost:
RESOURCE_COST

Identifies system resource limits for individual users:
USER_RESOURCE_LIMITS

Shows all system privileges:
DBA_SYS_PRIVS

Show all object privileges:
DBA_TAB_PRIVS

Shows all privileges in this session available to you as the current user:
SESSION_PRIVS

Views for audits currently taking place are created by this script:
cataudit.sql

a list of audit entries generated by the exists option of the audit command:
DBA_AUDIT_EXISTS

A list of audit entries generated for object audits:
DBA_AUDIT_OBJECT

A list of audit entries generated by session connects and disconnects:
DBA_AUDIT_SESSION

A list of audit entries generated by statement options of the audit command:
DBA_AUDIT_STATEMENT

A list of all entries in the AUD$ table collected by the audit command:
DBA_AUDIT_TRAIL

To determine the roles available in the database, the names of all the roles on the database and if a password is required to use each role:
DBA_ROLES

Names of all users and the roles granted to them:
DBA_ROLE_PRIVS

All the roles and the roles that are granted to them:
ROLE_ROLE_PRIVS

Which system privileges have been granted to a role:
DBA_SYS_PRIVS

All the system privileges granted only to roles:
ROLE_SYS_PRIVS

All the object privileges granted only to roles:
ROLE_TAB_PRIVS

All the roles available in the current session:
SESSION_ROLES

Which object privilege has been granted to a role:
DBA_TAB_PRIVS

To display the value of the NLS_CHARACTERSET parameter:
NLS_DATABASE_PARAMETERS







Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Transportable Tablespaces

Transportable Tablespaces

Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.

Setup
Source Database
Destination Database
Cross-Platform Tablespace Conversions
Related articles.

Oracle Data Pump in Oracle Database 10g (expdp and impdp)
Data Pump Enhancements in Oracle Database 11g Release 1
SQL Developer 3.1 Data Pump Wizards (expdp, impdp)
Cross-Platform Tablespace Conversion
Setup

For this example I'm going to create a new tablespace, user and table to work with in the source database.

CONN / AS SYSDBA

CREATE TABLESPACE test_data
DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test_user IDENTIFIED BY test_user
DEFAULT TABLESPACE test_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON test_data;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
'Description for ' || level
FROM dual
CONNECT BY level <= 10000; COMMIT; Source Database For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check. CONN / AS SYSDBA EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL>
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.

SELECT * FROM transport_set_violations;

no rows selected

SQL>
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.

SQL> ALTER TABLESPACE test_data READ ONLY;

Tablespace altered.

SQL>
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.

The source tablespace can now be switched back to read/write mode.

ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log
Switch the new tablespace into read write mode.

SQL> ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
The tablespace is now available in the destination database.

SELECT tablespace_name, plugged_in, status
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_DATA';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TEST_DATA YES ONLINE

1 row selected.

SQL>
Cross-Platform Tablespace Conversions

If you are transporting tablespaces between platforms you need to perform Cross-Platform Tablespace Conversions.

For more information see:

Transporting Tablespaces Between Databases
Oracle Data Pump in Oracle Database 10g (expdp and impdp)
Data Pump Enhancements in Oracle Database 11g Release 1
SQL Developer 3.1 Data Pump Wizards (expdp, impdp)
Cross-Platform Tablespace Conversion
Hope this helps. Regards Tim...
















Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

How to see the database is open (mount or unmont ) state in 11g ?

select database_status from v$instance
select * from v$database

_______________________________________________________________________________________________


2. HOW MUCH SPACE IS AVAILABLE IN UNDO N HOW MUCH IS USED


SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+
u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
(select tablespace_name , sum(blocks)*8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select
UPPER(value)
from v$parameter where name = 'undo_tablespace');

3. How much active,expired and unexpired

=====================================



set linesize 152

col tablespace_name for a20

col status for a10

select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name;
(2:35:42 AM) vipul.pahuja@oracle.com: SQL> select * from t11;

SALARY
----------
12000

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 16 1208 .009216309

SQL> update t11 set salary=15000 where salary=12000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> update t11 set salary=20000 where salary=15000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> update t11 set salary=25000 where salary=20000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> commit;

Commit complete.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 EXPIRED 34 752 .005737305
UNDOTBS1 UNEXPIRED 15 1200 .009155273

SQL>






Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

How to unlock the user in oracle Sql

How to unlock the user in oracle 

check more videos on
http://www.youtube.com/channel

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav