DBNEWID Utility

Prior to the introduction of the DBNEWID utility alteration of the internal DBID of an instance was impossible and alteration of the DBNAME required the creation of a new controlfile. The DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler. Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. Alteration of the BID in the cloned instance removes this restriction.
* DBID And DBNAME
* DBNAME Only
* DBID Only

DBID And DBNAME

• Backup the database.
• Mount the database after a clean shutdown:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT

• Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
  

  nid TARGET=sys/password@TSH1 DBNAME=TSH2

  Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:
  

  C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
  DBNEWID: Release 9.2.0.3.0 - Production
  Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
  
  Connected to database TSH1 (DBID=1024166118)
  
  Control Files in database:
    C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
  
  Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y
  
  Proceeding with operation
  Changing database ID from 1024166118 to 1317278975
  Changing database name from TSH1 to TSH2
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
    Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\DRSYS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\EXAMPLE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\INDX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\ODM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\XDB01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name
  
  Database name changed to TSH2.
  Modify parameter file and generate a new password file before restarting.
  Database ID for database TSH2 changed to 1317278975.
  All previous backups and archived redo logs for this database are unusable.
  Shut down database and open with RESETLOGS option.
  Succesfully changed database name and ID.
  DBNEWID - Completed succesfully.

• Shutdown the database:
  

  SHUTDOWN IMMEDIATE

• Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE

• Create a new password file:
  

  orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

• Rename the SPFILE to match the new DBNAME.
• If you are using Windows you must recreate the service so the correct name and parameter file are used:
  

  oradim -delete -sid TSH1
  oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora

  If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
  

  ORACLE_SID=TSH2; export ORACLE_SID

• Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
  

  lsnrctl reload

• Open the database with RESETLOGS:
  

  STARTUP MOUNT
  ALTER DATABASE OPEN RESETLOGS;

• Backup the database.

DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility:

nid TARGET=sys/password@TSH2 DBNAME=TSH3 SETNAME=YES

The SETNAME parameter tells the DBNEWID utility to only alter the database name.

When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.

DBID Only

• Backup the database.
• Mount the database after a clean shutdown:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT

• Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:
  

  nid TARGET=sys/password@TSH3

• Shutdown and open the database with RESETLOGS:
  

  SHUTDOWN IMMEDIATE
  STARTUP MOUNT
  ALTER DATABASE OPEN RESETLOGS;

• Backup the database.

Schema Rename in Oracle

I have tested by updating the schema name in user$ table. It was worked, but I didn't tried on production box so i am not sure whether below method is correct or not.

Method 1)

I want to rename schema name from SATISH to SATISH1 so used below command.

SQL> conn / as sysdba

SQL> update user$ set name='SATISH1' where name = 'SATISH';

Method 2)

I trusted always below method is safe.

1. create user new_user...
2. grant ... to new_user;
3. execute dbms_tts.transport_set_check(...);
4. lock old user;
5. exp old user
6. imp  fromuser=old_user touser=newuser
7. [drop user old_user cascade;]

resize redo log files

Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .



1. First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf


3. Now drop the log group 1 and recreate it with increased size

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;

4. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;


SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log

SQL> alter system switch logfile;

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;


SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;

7. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT

** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this

SQL> ALTER SYSTEM CHECKPOINT;

** FOR safety reason please take full backup

AWR REPORT

AWR Features

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
   retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
   interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
 DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
   low_snap_id  => 22,
   high_snap_id => 32);
END;
/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.create_baseline (
   start_snap_id => 210,
   end_snap_id   => 220,
   baseline_name => 'batch baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN
 DBMS_WORKLOAD_REPOSITORY.drop_baseline (
   baseline_name => 'batch baseline',
   cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Smore More Example Scripts:
Display a list of snapshots
set lines 100 pages 999
select snap_id
, snap_level
, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1
/
Produce a report:
@?/rdbms/admin/awrrpt.sql
To see the snapshot interval and retention period
col snap_interval format a30
col retention format a30
select snap_interval
, retention
from dba_hist_wr_control
/
Change the snapshot interval:
Note. This example changes it to 30 minutes
exec dbms_workload_repository.modify_snapshot_settings (interval => 30)
Change the retention period
Note. This example changes it to two weeks (14 days)
exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)
Manually take a snapshot
exec dbms_workload_repository.create_snapshot
List all baselines
set lines 100
col baseline_name format a40
select baseline_id
, baseline_name
, start_snap_id
, end_snap_id
from dba_hist_baseline
order by 1
/
Create a baseline
exec dbms_workload_repository.create_baseline (, ,'')
Remove a baseline
exec dbms_workload_repository.drop_baseline('')
Enable/Disable automatic snapshotsNote. This job is enabled by default
exec dbms_scheduler.enable('GATHER_STATS_JOB')
and to disable...
exec dbms_scheduler.disable('GATHER_STATS_JOB')
Time model queries
System time model
set lines 100 pages 999
select stat_name
, value
from v$sys_time_model
order by value desc
/
Session time model
set lines 100 pages 999
select stat_name
, value
from v$sess_time_model
where sid = '&sid'
order by value desc
/

Table Fragmentation

How to find table fragmentation?

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

Table size (with fragmentation)

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 72952kb

Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes

2. export / truncate / import

3. create table as select ( CTAS)

4. dbms_redefinition

Option: 1 "alter table ... move + rebuild indexes"

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes

2 where table_name = 'BIG1';

STATUS INDEX_NAME

-------- ------------------------------

UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes

2 where table_name = 'BIG1';

STATUS INDEX_NAME

-------- ------------------------------

VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 30727.37kb

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 68986.97kb

SQL> select status from user_indexes

2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.

Option: 3 "export / truncate / import"

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

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:\big1.dmp tables=big1

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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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 BIG1 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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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 big1;

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:\big1.dmp ignore=y

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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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 "BIG1" 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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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 = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'BIG1';

TABLE_NAME size

------------------------------ ------------------------------------------

BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS

--------

VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 "dbms_redefinition"

SQL> create table TABLE1 (

2 no number,

3 name varchar2(20) default 'NONE',

4 ddate date default SYSDATE);

Table created.

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

Table altered.

SQL> begin

2 for x in 1..100000 loop

3 insert into table1 ( 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_table1

2 after insert on table1

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)

----------

100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

------------------------------ ------------------------------------------

TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

------------------------------ ------------------------------------------

TABLE1 822.69kb

SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg

SQL> --First check table is condidate for redefinition.

SQL>

SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-> 'TABLE1',-> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --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>

SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab

le.

SQL>

SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)

SQL> alter table TABLE2

2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2

2 after insert on table2

3 begin

4 null;

5 end;

6 /

Trigger created.

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

s.

SQL>

SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

------------------------------ ------------------------------------------

TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';

TABLE_NAME size

------------------------------ ------------------------------------------

TABLE1 841.4kb

SQL> select status,constraint_name

2 from user_constraints

3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME

-------- ------------------------------

ENABLED PK_NO1

SQL> select status ,trigger_name

2 from user_triggers

3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME

-------- ------------------------------

ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

Select Any Table

Select Any Table

•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.

•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.

•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.

•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.

•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.

ROLE MANAGEMENT

1) How to create a role

Create role  role_name

            Create role satish_role

2) Create Password Protected Role

             Create role role name identified by  password

3) How to Assign Privilege To A Role

             GRANT privilege_name TO role_name;

             GRANT create session TO role_name

     You can give grant role to role

            Grant  to  

4) How to Assigning Roles To Users

            GRANT  roles_name  TO  user_name;

5) How to Revoking Privileges From Roles

REVOKE FROM ;

6) How to Revoke a role from a user

REVOKE  role_name  FROM  user_name;

7) How Activating A Role

SET ROLE  role_name;

8) How Activating A Password Protected Role

SET  ROLE  role_name  IDENTIFIED BY role_password;

9) How to Activating All Roles

SET ROLE all;

10) How to Activating All Roles Except One

SET ROLE all EXCEPT  role_name;

11) How to Deactivating A Role

Can not be done on an individual basis

12) How Deactivating All Roles

SET ROLE none;

13) How to Dropping A Role

DROP ROLE  role_name;

14) How to Creating And Assigning The PLUSTRACE Role Used By AUTOTRACE

            This role must be created by SYS and grants SELECT on the following v_$ views:

·         V$SESSTAT

·         V$STATNAME

·         V$MYSTAT

SQL> @c:\oracle\product\ora10\sqlplus\admin\plustrce.sql
GRANT plustrace TO uwclass;

15) User$

In this user$ table we can see all roles in our database

16) role_sys_privs

In this role_sys_privs table we can see which Privileges Granted To A Role

17) dba_role_privs

In this dba_role_privs  we can see which Roles Granted To Schemas

18) session_roles

In this session_roles we can see the present session have which roles

19) session_prives

In this session_prives table we can see the present session have which privelags

20) dba_roles

            In this dba_roles table we can see the dba user contain which roles

21) dba_sys_privs

In this dba_sys_privs table we can see all the system privileges

ORA-01045) this error will be come due to insufficient privs

 

all_tab_privs_made  in this table you can see all tables privs

user_tab_privs_made

Upgrade Oracle Database 10.2.0.1 to 10.2.0.2

If you are installing Content DB, the db version has to be 10.2.0.2 in Windows Platform.
The upgrade was fairly simple process. But it was loooongggg...The patchset to download is 4547817. Login http://metalink.oracle.com and search for this patch.
Download the 570 + MB patch, unzip it to temp folder and run the installer.
It helps to go through the patch note, but it was fairly straight forward if you are not using RAC.
Stop all services in the 10.2.0.1 home. Run the setup. Select the home of the 10.2.0.1 database.
Once you install the new 10.2.0.2, follow the patch note to perform the post installation steps. In short they are to
-Start the listener
-Start the database in the upgrade mode (startup upgrade, once you have logged in as sys)
-Run the catupgrd.sql script (in rdbms\admin folder) (plan for a couple of hours !!)
-Shutdown and Start the database-Run the utlrp to compile all invalid objects (optional step)-
- Some more steps if you are using the recovery manager catalog.

difference between the sysdba and sysoper

sysdba user have all privilages of sysoper in addition to that
sysdba has following privilages
1) perform incompleate recovery
2) create database
3) see all user objects

upgrading database rhel3 to rhel4

upgrading database from rhel3 to rhel4

The steps to be followed if you are upgrading already existing OS without formatting OS partition.

1. Upgrade the OS from RHEL 3 to 4.

2. Install the required RPMs. Please refer to Metalink Note 169706.1 for the list of required RPMs to be installed. This is because the list of packages required for RHEL 3 and 4 are different.

3. Relink Oracle binaries. For more information about how to relink Oracle binaries, please refer to Metalink Note 131321.1

4. Here there is no need to configure the kernel parameters and Oracle user's environment because this will be preserved while upgrading the OS without formatting OS partition.

The steps to be followed if you are upgrading to RHEL 4 by formatting OS partition and re-installing RHEL 4.

1. Take backup of the files /etc/oraInst.loc and /etc/oratab files. Note down the Oracle database user(oracle) and groups (oinstall, dba) ids.

2. Install RHEL 4. While installing make sure that you have selected default RPM installation. This is mandatory requirement to install Oracle database.

3. Install all other required packages from Metalink Note 169706.1.

4. Create Oracle database user(oracle) and groups (oinstall, dba). Make sure that you have these Oracle users and groups with the same ids and names that you had in RHEL 3.

5. Configure the kernel parameters and Oracle user's environment as per installation guide.

6. Restore the files /etc/oraInst.loc and /etc/oratab files that you have taken backup in step 1.

7. Relink Oracle binaries. For more information about how to relink Oracle binaries, please refer to Metalink Note 131321.1.

8. Run $ORACLE_HOME/root.sh script that creates dbhome, oraenv and coraenv scripts in the directory /usr/local/bin.

Recovery in Archive log Mode

Recovery in Archive log Mode
1.Closed Database Recovery
2.Open Database Recovery with database initially open
3.Open Database Recovery with database initially closed
4.Datafile Recovery with no datafile backup


1.Closed Database Recovery
Situations where this mode is applicable
1.When the system datafile is missing
2.When the undo datafile is missing
3.Large number of datafiles are missing

SQL> shut abort
Now physically copy the required datafiles from hot backup
SQL> startup mount
Check which datafile needs recovery by v$recover_file
SQL> recover database
SQL>alter database open

2.Open Database Recovery with database initially open
Situations where this mode is applicable
1.other than system undo data files are missing

Database is open stage
Offline the particular table space
Physically copy the missed files from hot backup

Recover data file name or number
Make table space online

SQL> alter tablespace tablespacename offline immediate
Copy the particular datafile which is missing from hotbackup

SQL> recover datafile ‘datafile path or number’
SQL>alter tablespace online

3.Open Database Recovery with database initially closed
Situations where this mode is applicable
1.other than system undo data files are missing
2.when the database is in the closed in abnormal state due to hardware issue

SQL>startup mount
SQL>alter database datafile ‘ path’ offline
SQL>alter database open
Copy the particular datafile which is missing from hotbackup
SQL>recover datafile ‘path or number’
SQL>alter datafile ‘path’ online

4.Datafile Recovery with no datafile backup

Situations where this mode is applicable
Normal Datafile is missing if no back up is there
SQL> startup mount
SQL> alter database create datafile ‘path’
SQL> recover datafile ‘path’
SQL>alter database open;

Or
If database is in open stage
SQL>alter datafile ‘file’ offline
or
SQL>alter tablespace t.name offline
SQL>alter database create datafile ‘filepath’
SQL>recover datafile ‘file path’
SQL>alter datafile ‘file path’ online

password file creation

What is password file
Oracle provides a password utility, orapwd, to create a password file. When you connect
using SYSDBA privilege, you are connecting as SYS schema and not the schema associated
with your username. For SYSOPER, you are connected to the PUBLIC schema.
What is the use of password file
By using password file you can create a user with sysdba privilages
and you can connect remotly with sysdba privilages
Creating a password file
Go to dba directory and create a password file by usein orapwd utility
[satish@host dbs]$ pwd
/oracle/satish/9i
[satish@host dbs]$ orapwd file=orapwsatish password=welcome
Then password file will be created
after set the parameter
remote_login_passwordfile=exclusive in pfile or spfile
then password file will be ready
after you can create a user with sysdba privilages and you can connect with sysdba prives
creating a user with sysdba privilages
SQL>show user
sys
SQL>create user satish identified by welcome
user created
SQL>grant sysdba to satish
Grant succeeded.

then normal user is ready with sysdba privilages
connect user with sysdba privilages
SQL> conn satish/welcome as sysdba

Note:-
Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE or SHARED
where:
EXCLUSIVE indicates that only one instance can use the password file and that
the password file contains names other than SYS. Using an
EXCLUSIVE password file you can grant SYSDBA or
SYSOPER privileges to individual users.
SHARED indicates that more than one instance can use the password file.
The only user recognized by the password file is SYS. You
cannot add users to the SHARED password file.

TABLE SPACE MANAGEMENT

A small database might need only the SYSTEM tablespace; however, Oracle recommends that
you create additional tablespaces to store user data, user indexes, undo segments, and
temporary segments separate from data dictionary.

The DBA can create new tablespaces, resize data files, add data files to tablespaces, set and
alter default segment storage settings for segments created in a tablespace, make a tablespace
read-only or read-write, make a tablespace temporary or permanent, and drop

tablespaces.

• The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
  as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.

Table spaces are two types

1)system tablespace 2)nonsystem tablespace

SYSTEM tablespace

*Created with the database
*Contains the data dictionary
*Contains the SYSTEM undo segment

Non-SYSTEM tablespaces

*Separate segments
*Ease space administration
*Control amount of space allocated to a user

Space Management in Tablespaces

Table spaces are managed by two types

1)locally managed 2)Dictionary managed

locally managed:-The extents are managed with in the tablespace via bitmaps

*Free extents recorded in bitmap
*Each bit corresponds to a block or group of blocks
*Bit value indicates free or used

Dictionary managed:-the extents are managed by the data dictionary segments in dictionary managed tablespace can have customized storage.

*Free extents recorded in data dictionary tables

syntax for creation of tablespaces

SQL>create tablespace svs datafile '$ORACLE_HOME/oradata/svs.dbf ' size 10m extent management local;

SQL>create tablespace svs1 datafile '$ORACLE_HOME/oradata/svs1.dbf' size 20m extent management local autoallocated;

SQL>create tablespace svs2 datafile '$ORACLE_HOME/oradata/svs2.dbf ' size 30m extent management local uniform size 10k;

SQL>create tablespace svs3 datafile '$ORACLE_HOME/oradata/svs3.dbf ' size 10m extent management dictionary;

SQL>create tablespace svs4 datafile '$ORACLE_HOME/oradata/svs4.dbf ' size 10m extent management dictionary default storage (initial 1m next 1m pct increase0);

we can give storage parameters in three levels

1)server level

2)tablespace level

3)segment level

Configure database noarchive to archive log mode

Configure 9i database noarchive to archive log mode

create a directory for archives
[satish@host satish]$cd 9i/oradata
[satish@host oradata ]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$mkdir archs
then open the parameter file and add this parameters
[satish@host oradata]$cd $ORACLE_HOME/dbs
[satish@host dbs]$vi initsatish.ora
#add this lines
log_archive_start=true
log_archive_dest='/oracle/satish/9i/oradata/archs'
log_archive_format=%s%t
#save and exit
connect to sql promt with sysdba
[satish@host satish]$sqlplus /'as sysdba'
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
then database will be open with archive log mod
if you want to check your database is archive mod or noarchive mod then use this command

SQL>archive log list

--satish

MAINTAINING REDO LOG FILES

REDO LOGFILES

Redo log files provide the means to redo transaction in the event of a database failure

Online redo log file groups

• A set of identical copies of online redo log file is called an online redo log file group
• The LGWR background process concurrently writes the same information to all online redo log files in a group
• The oracle server needs a minimus of two online redo log file groups for the normal operation of a database

Online redo log file member

• each online redo log file in a group is called a member
• each member in a group has identical log sequence number and of the same size
• the corrent log sequence number is stored in the control file and in the header of all datafiles

Log switch

When the current online redo logfile group filled LGWR begins writing to the next group

this is called a log switch

this command is showing the redolog groups

SQL>select * from v$log;

this command is showing the redolog files

SQL>select * from v$logfile;

adding online redo log file group

SQL>alter database add logfile group3('/oracle/satish/9i/oradata/redo3a.log','/oracle/satish/9i/oradata/redo3b.log') size 5m;

adding online redo logfile member

SQL>alter database add logfile member '/oracle/satish/9i/oradata/redo3c.log' to group 3;

Relocation or renaming online redo log files

tow wayes is there

1)alter database

SQL>alter database rename file 'path1' to 'path2'

2)add new member and drop old member

Drop online redo log file groups

SQL>alter database drop logfile group 3;

• An instance require atleast two groups of online redo log files
• An active or current froup can not be dropped

Droping online redo log file members

SQL>alter database drop logfile member '/oracle/satish/9i/oradata/redo3a.log';

• we can not drop the last member
• we can not drop the current member
• if the databse is running in archive mode and the log file group to which the member belong is not archived then the member can not be dropped

Resize the redo log file

we can not resize the redo log file

just we can create another file and delete oldfile.

Forcing log switch

SQL>alter system switch logfile;

--satish

multiflexing of control files

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.


Multiplexing the Control File using pfile

1)shutdown the database
2)By using OS command copy the oldfile to newfile
3)In initsid.ora file change this control_file=(oldfilepath,newfilepath')
4)startup the database

SQL>shut immediate
SQL>!
[satish@host oradata]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$ls
control.ctl redo1.log redo2.log system.dbf undo.dbf
[satish@host oradata]$cp control.ctl control1.ctl
[satish@host oradata]$cd /oracle/satish/9i/dbs
[satish@host dbs]$vi initsatish.ora
#change this parameter
control_files=(/oracle/satish/9i/oradata/control.ctl,oracle/satish/9i/oradata/control1.ctl)
#save and exit
[satish@host dbs]$ cd
[satish@host satish]$sqlplus /'as sysdba'
SQL>start up
then database will be start with two control files
if you want check youer control files use this command
SQL>show parameter control_files


Multiplexing the Control File using spfile

SQL>alter sysytem set control_files='$ORACLE_HOME/oradata/control.ctl','$ORACLE_HOME/oradata/control1.ctl','$ORACLE_HOME/oradata/control2.ctl' scop=spfile;
SQL>shut immediate
SQL>exit
[satish@host satish]$
[satish@host satish]$cd $ORACLE_HOME/oradata/
[satish@host oradata]$pwd
/oracle/satish/9i/oradata
[satish@host oradata]$ cp control.ctl control2.ctl
[satish@host oradata]$cd
[satish@host satish]$sqlplus /'as sysdba'
SQL>startup
then database will be started with 3 control files
if you want see the control file then
SQL>show parameter control_files
or
SQL>select * from v$control_file

--satish

SPFILE CREATION

SPFILE CREATION

If you know your database is using pfile or spfile like this
SQL>show parameter spfile
then if it will be displayed strig value is null then your database is running by using pfile
other wise it is running by using spfile
create spfile
SQL>create spfile from pfile;

then spfile will be created default location is $ORACLE_HOME/dbs
spfile file name is spfilesatish.ora means spfile.ora

after shout down and startup the database
defult the database is startup by using spfile
if spfile is there but you want to start the database by using pfile then use this command
SQL>startup pfile=$ORACLE_HOME/dbs/initsatish.ora

what is the use of spfile
By using spfile you can change parameter values dynamically. That means if you want to change parameter value
no need to shutdown the database
offcourse you can chnge some parameters dynamically by using pfile but thsoe parameter values are not permanent

By using spfile you can change parameter value this type
SQL>alter system set parameter=(parametername) scope=memory or spfile or both;

database creation on 9i

DATABASE CREATION ON 9I

Go to oracle s/w directory
I installed s/w in /oracle/satish/9i
So go to that directory and create 2directoryes like
[satish@host 9i]$ pwd
/oracle/satish/9i
[satish@host 9i]$ mkdir oradata dump
go to dump directory and create bdump udump cdump directoryes like
[satish@host dump]$ pwd
/oracle/satish/9i/dump
[satish@host dump]$ mkdir bdump cdump udump
wile instalation time in ORACLE_HOME/dbs init.ora file will be come
then copy that file to initsid.ora like
[satish@host dbs]$ pwd
/oracle/satish/9i/dbs
[satish@host dbs]$ cp init.ora initsatish.ora
after open initsatish.ora by using vi editor and make changes like
db_name=satish
control_file=(/oracle/satish/9i/oradata/control.ctl)
#and add this lines
background_dump_dest='$ORACLE_HOME/dump/bdump'
user_dump_dest='$ORACLE_HOME/dump/udump'
core_dump_dest='$ORACLE_HOME/dump/cdump'
compatible=9.2.0
undo_management=auto

save and exit
after connect to sql promp with sysdba like

[satish@host satish]$ sqlplus /'as sysdba'
after sql prompt will be come then nomount the database like

sql> startup nomount
then data base will be go to nomount stage after go to os and create a file by using vi editor and after run that file in sql promt like this

sql> !
[satish@host satish]$ vi createdb.sql

create database satish
logfile
group 1('/oracle/satish/9i/oradata/redo1.log') size 5m;
group 2('/oracle/satish/9i/oradata/redo2.log') size 5m
datafile
'$ORACLE_HOME/oradata/system.dbf' size 500m
undo tablespace undotabs
datafile '/oracle/satish/9i/oradata/undo.dbf' size 300m
charecte set us7ascii
;

#save and exit
after connect to sql prompt with sysdba
and take database with no mount stage
and run that script like
SQL>sqlplus 'as sysdba'
SQL>startup nomount
SQL> @/oracle/satish/createdb.sql
then database will be created and default database in open stage
then run this fallowing scripts


SQL>@/oracle/satish/9i/rdbms/admin/catalog.sql
SQL>@/oracle/satish/9i/rdbms/admin/catproc.sql
after connect system/manager run the pupbld.sql script like
SQL>conn system/manager
SQL>@oracle/satish/9i/sqlplus/admin/pupbld.sql

then database will be ready


best of luck
--satish

instalation oracle 9i on RHEL3.0

required Redhat packages compat-db,copat-gcc,compat-gcc-c++,compat-libstdc++,openmotif,setarch
those packges are available in redhat os cds
install that packages
[ root@host packlocation] # rpm -ivh compat-db* --aid --force
means rpm -ivh --aid --force
install all packages by using above command

check package is installed or not
[ root@host packlocation] # rpm -qa comepat-db*
means rpm -qa (packname>


after run this commands

[root@host root]#mv -i /usr/bin/gcc /usr/bin/gcc323
[root@host root]#mv -i /usr/bin/g++ /usr/bin/gcc
[root@host root]#ln -s /usr/bin/g++296 /usr/bin/g++

required patch 3006854
down load that pach from metalink and unzip that and applay that patch
note-: after apply the patch restart the system

add group

[root@host root]#groupadd dba
add the user with home directory /oracle and group dba

[root@host root]#useradd -d /oracle/satish -g dba satish
hear /oracle is local mount point you can give any mount point name

how to check your mount points

[root@host root]#df -h
set password for user

[root@host root]#passwd satish
give new password and retype new password
then password will be set
set ora inst location

[root@host root]#vi /etc/oraInst.loc
inventory_loc=/oracle/satish/oraInventory

save and exit

check graphicaly mod is exist or not
[root@host root]#xclock

go to created user

[root@host root]#su - satish

set the bashrc in satish user

[satish@host satish]$vi .bashrc
#come to last line and writ it
export ORACLE_SID=satish
export ORACLE_BASE=/oracle/satish
export ORACLE_HOME=/oracle/satish/9i
export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:/$ORACLE_HOME/bin:.
#save and exit

run the bashrc

[satish@host satish]$ . .bashrc

run the instalation

go to the 9i software path and run

[satish@host Disk1]./runInstaller


after select what ever packeges you need and install

you can download the software from this site

http://www.oracle.com/technology/software/products/oracle9i/index.html

best of luck
--satish