Rename Oracle DataBase

Some times we may need to rename our database. Controlfile is the file which will have the database name. This controlfile is read when database is mounted and instance comes to know  the database name and mounts the database with this name.So if we recreate the controlfile and we keep new database name in controlfile recreation script then the database will have new name.

Follow the below steps to rename the database

 SQL> select name from v$database;

NAME                                                                           
---------                                                                      
ORCL                                                                           

SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      orcl                          


1. Take the controlfile backup as ASCII file using below statement

SQL>alter database backup controlfile to trace;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Contents of controlfile backup as trace with modified values

CREATE CONTROLFILE set DATABASE "ORCLNEW" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'H:\ORCL\ORCL\REDO01.LOG'  SIZE 50M,
  GROUP 2 'H:\ORCL\ORCL\REDO02.LOG'  SIZE 50M,
  GROUP 3 'H:\ORCL\ORCL\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'H:\ORCL\ORCL\SYSTEM01.DBF',
  'H:\ORCL\ORCL\UNDOTBS01.DBF',
  'H:\ORCL\ORCL\SYSAUX01.DBF',
  'H:\ORCL\ORCL\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

 3.Modify the parameter file and give name to db_name parameter(highlited in red).

orcl.__db_cache_size=134217728

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=58720256

orcl.__streams_pool_size=0

*.audit_file_dest='H:\oracle\product/admin/orcl/adump'

*.background_dump_dest='H:\oracle\product/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='H:\orcl\orcl\control01.ctl','H:\orcl\orcl\control02.ctl','H:\orcl\orcl\control03.ctl'

*.core_dump_dest='H:\oracle\product/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ORCLNEW'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=h:\orcl\arch'

*.log_archive_format='ARC%S_%R.%T'

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=209715200

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='H:\oracle\product/admin/orcl/udump'


4.Startup database in nomount and execute the script

SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes                                      
Fixed Size                  1248116 bytes                                      
Variable Size              67110028 bytes                                      
Database Buffers          134217728 bytes                                      
Redo Buffers                7139328 bytes                                      
SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      ORCLNEW                       

SQL> @h:\rename.trc

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME                                                                           
---------                                                                      

ORCLNEW                                                                        

Migration of Non ASM oracle database to ASM databsae

MIGRATION FROM NON ASM TO ASM

1 First we need to set below parameter in parameter file for controlfile.

NOTE: I have two disk group here I am using "DATA" disk group.

control_files='+data'

Step: 2 Now we need to restart the database so that new parameter value will take effect.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Step:3 We will restore controlfile on ASM file system .

]$rman target /

RMAN> restore controlfile from ‘/disk1/oradata/test/CONTROL01.ctl';

Starting restore at 10-dec-11using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK

channel ORA_DISK_1: copied control file copyoutput file name=+data/test/controlfile/current.256.652270419Finished restore at 10-dec-11

Step:4 Mount Oracle Database and take backup of database.

RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_1

RMAN> backup as copy database format '+data';

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.257.652270565"

datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.258.652270761"

datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.259.652270927"

datafile 4 switched to datafile copy "+DATA/test/datafile/users.261.652270989"

Step:5 Now you can open the database .

SQL> alter database open resetlogs;

Database altered.

Step:6 Drop old temporary tablespace  and create new temporay tablespace

SQL> create temporary tablespace temp2 tempfile '/disk1/oradata/test/temp2.dbf'

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME FILE_NAME BYTES

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

TEMP2                              +DATA/test/tempfile/temp2.dbf

Step:7 Recreate All redolog group on ASM diskgroup

SQL> select group#, member from v$logfile

GROUP# MEMBER

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

3           /DISK1/ORADATA/TEST/REDO03.LOG

2           /DISK1/ORADATA/TEST/REDO02.LOG

1           /DISK1/ORADATA/TEST/REDO01.LOG

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 CURRENT

SQL>alter database add logfile group 4 (‘+data/redolog4.log’) size 10m;

SQL>alter database add logfile group 5 (‘+data/redolog5.log’) size 10m;

SQL>alter system switch logfile;

SQL>alter system switch logfile;

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3 ;

Database altered.

Key Differences in Oracle 9i and Oracle 10g

Following are the key differences in Oracle 9i and Oracle 10g

1. There are more implementation differences. In terms of architecture, 9i is based on Internet technology while 10g is grid computing based one. Many DBA features like Automated Storage Management (ASM), Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM) were introduced 10g version of oracle.

2. For developers, 10g is more stable than 9i. All the earlier bugs reported were fixed in 10g release.

3. Performance wise, 10g is more impressive. It has increased the data chunk size during I/O between the SQL and PL/SQL engines. Use of collections is recommended in 10g.
PLS_INTEGER, a new data type was added which enhances performance. ANYDATA data type was introduced to hold a data of variant feature.

4. FLASHBACK option was made more stronger like TABLE FLASHBACK was introduced. Oracle Recyclebin was introduced. The dropped objects can be flashed back like tables.

5. Support for bigfile tablespaces that is up to 8 Exabytes in size has been introduced
 
6. Data Pump has been introduced for faster data movement with expdp and impdp utilities.

7. In Oracle 9i after drop we can't rollback the drop operation but we can do it in 10g.
 
Moreover, 10g has additional 149 features than 9i.

Transportable Tablespace

Transportable tablespace is technique by which we can transport a tablespace from one database to another database.
Source Database => This is the database from where we will transport the database.
Target Database  => This is the database where transported tablespace wiil be imported.

We need to follow some steps in order to transport the tablespace.

Create a tablespace  to be transported

SYS>CREATE TABLESPACE tts DATAFILE ‘/disk1/oradata/tts.dbf’ SIZE 4m;

On source database
There are a number of steps to be done on the source site. Respectively,

1. Validating the self containing property of the migration tablespace.
2. Altering the migration tablespace to read only mode.
3. Exporting the meta data

1.Check Self Containing Property

SYS>exec dbms_tts.transport_set_check(‘TTS’);

This is going to populate a table if TTS is not self contained.

SYS>select  *  from transport_set_violations;

No row selected

If it were not self contained you should remove the dependencies by dropping them.

2.ALTER Tablespaces to READ ONLY Mode

SYS>alter tablespace tts read only;

3.Export the Metadata

We will export the metadata of the tablespace with export utility and will transfer datafile and dumpfile to target server using FTP. Since we export metadata only hence operation will be fast.

]$ exp file=tts.dmp log=tts.log transport_tablespaces=y tablespaces=tts

Target Database

On target database we need to do-

1. Copy the datafiles and export file to target server via FTP
2. Create the necessary schemas
3. Import the export file
4. make tablespace read write.
   

2. Create all users on target database who were working on tts tablespace on source database.

3.Now import the tablespace

]$ imp file=tts.dmp log=tts.log transport_tablespaces=y datafiles=/disk2/oradata/tts.dbf

4. Make tablespace read write 

SYS>alter tablespace tts read write;

 

Hashing in Oracle

What is Hashing?
A hash function returns a value for an input, and the output is generally shorter or more compact than the input. For a relatively simple hash function, there is no guarantee that inputs map one to one for outputs, that is, two different inputs can have the same hashed output value.
Before getting more into where hashing takes place in Oracle, let’s look at some examples of how hashing is used.The table of names takes the sum of the ASCII values of each letter, sums them, and then lists the mod 13 remainder. The table is reproduced below with an additional column to help illustrate what is taking place.

create table hash_example
(lastname varchar2(12),
  ascii_sum number,
  hashed_value number);
insert into hash_example (lastname) values ('Brecker');
insert into hash_example (lastname) values ('Corea');
insert into hash_example (lastname) values ('Davis');
insert into hash_example (lastname) values ('Hancock');
insert into hash_example (lastname) values ('Harris');
insert into hash_example (lastname) values ('Marsalis');
insert into hash_example (lastname) values ('Parker');
commit;

declare
  cursor c is
  select lower(lastname) lastname 
  from hash_example 
  for update;
  l_name hash_example.lastname%type;
  l_sum hash_example.ascii_sum%type := 0;
  l_tmp number := 0;
begin
  for r in c loop
    for i in 1..length(r.lastname) loop
      l_tmp := ascii(substr(r.lastname,i,1));
      l_sum := l_sum + l_tmp;
    end loop;
    update hash_example
    set ascii_sum = l_sum,
        hashed_value = mod(l_sum,13)
    where current of c;
    l_tmp := 0;
    l_sum := 0;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from hash_example;

LASTNAME      ASCII_SUM HASHED_VALUE
------------ ---------- ------------
Brecker             734            6
Corea               522            2
Davis               535            2
Hancock             727           12
Harris              649           12
Marsalis            860            2
Parker              645            8

Overall, this was a pretty simple example. Given that you can pass in a value and get an output based on the hashing algorithm, and that the output is like a fingerprint of the original data.

Hashing in a Database

How does Oracle know if a SQL statement is new or already parsed? A parsed statement has a hash value that is by and large unique enough (collisions, or same output for different inputs can happen, but are rare). Shown below is an extract from a trace file, and the lines show each statement’s identifying information.

1	PARSING IN CURSOR #1 len=46 dep=0 uid=5 hv=810835083 ad='1d9cd494' select ename from scott.emp where empno = 7369
2	PARSING IN CURSOR #2 len=46 dep=0 uid=5 hv=1290101743 ad='1d9cca00' select ename from scott.emp where empno = 7499
3	PARSING IN CURSOR #1 len=46 dep=0 uid=5 hv=1589217292 ad='1d9cc7c4' SELECT ENAME FROM SCOTT.EMP WHERE 7499 = EMPNO
4	PARSING IN CURSOR #2 len=46 dep=0 uid=5 hv=810835083 ad='1d9cd494' select ename from scott.emp where empno = 7369

Statements 1, 2 and 3 are different, so each has its own hashed value (the hv value). Statements 1 and 4 are the same, and note both have the same hv value. One of the more desirable characteristics of a hashing function is that it should be quite fast and inexpensive to perform. If your statement has already been parsed (has a hash ID value), then a comparison is all that is needed. As pointed out in numerous other sources, hard parsing is undesirable. Re-use of statements can be accomplished two ways. One is via the use of bind variables, and the other is to embed commonly used statements in functions or procedures.

Where else is hashing used?

Hash partitioning is useful for data that doesn’t cleanly fall into categories such as range or list. Composite partitioning such as range-hash can be used to further categorized and distribute data. In these examples, the indexing feature or benefit is being used.

SOME USEFUL RMAN COMMANDS AND STATEMENTS

1. How to list backup of database done in particular time

RMAN>list backup of archivelog all completed after '30-SEP-11';

RMAN>list backup of database completed after '30-SEP-11';

Here in place of after you can also give 'before' or 'between startdate and enddate'

2.How to list archive logs generated in particular time interval
  Suppose i want to find out that on 5-nov-11 how many archive logs are generated between noon 12 to evening 6 o'clock

select name,completion_time,to_char(completion_time,'hh24:mi') as time from v$archived_log where to_char(completion_time)='05-NOV-11' and to_char(completion_time,'hh24:mi') between '12:00' and '18:00'

3.Check RMAN Backup Status
Run these queries on database not in catalog

Report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


Report all on full and incremental backups, not archivelog backups -
col STATUS format a9
col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

RMAN - Recovery Manager

- What is Rman
- Why use Rman
- Understanding The Rman Architecture
- Taking Backup in Non archive Backup Mode
- Taking Backup in archive Mode
- Enhancement in 10g For Rman

What is Rman?
Recovery Manager is a tool that manages the process of creating backup and also
manages the process of restoring and recovering them.
In addition to RMAN repository records, the recovery catalog can also hold RMAN stored
scripts, sequences of RMAN commands for common backup tasks. Centralized storage
of scripts in recovery catalog can be more convenient than working with command files

Why use Rman ?

1. No Extra Costs.. It is available free.
2. RMAN introduced in Oracle 8 it has become simpler with new version and easier than user managed backups.
3. Proper Security
4. You are 100% sure your database has been backed up .
5. It contains details of backup taken in the central repository
6. Facility of Testing validity of backups also command like cross check to check the status of backup.
7. Oracle 10g has got further optimized incremental backups with has resulted in improvement of performance during backup and recovery time
8. Parrallel operation are supported
9. Better Querying facility for knowing different details of backup.
10. No Extra redo generated when backup is taken. compared to online backup
11. Without rman.which results in saving of space in hard disk.
12. RMAN is an intelligent tool
13. Maintains repository of backup metadata.
14. Remembers backup locations
15. Knows what needs backup set locations
16. Knows what needs to be backed up
17. Knows what is required for recovery
18. Know what backups are redundant
19. It handles database corruptions

Understanding The Rman Architecture

An Oracle Rman comprises of

1. RMAN Executable - This could be present and fired even from client side
2. Target Databases - This is the database which needs to be backed up
3. Recovery Catalog - Recovery Catalog is optional otherwise backup backup details are stored in target database control file.It is a repository of information queried and updated by Recovery Manager. It contains information on Physical schema of Target Database data file and archive log, backup sets and pieces

Recovery Catalog is a must in following scenarios
- In order to store scripts
- For Table space Point in time recovery
Media Management Software
Media management software is a must if you are using RMAN for storing backup in
tape drive directly
Backups in Rman
Oracle backup in RMAN are the following type

These backup are of RMAN proprietary nature.

1. RMAN complete backup
2. RMAN incremental backup
3. Image copy

The advantage of image copy is it is not in RMAN proprietary format
Backup Format
RMAN backup is not in oracle format but in RMAN format. Oracle backup comprises of backup sets and consists of backup pieces Backup sets are logical entity. In Oracle 9i it gets stored in default locations
There are two type of backup sets

1. Datafile backup sets, 
2. Archivelog Backup sets

One more important point of data file backup sets is it do not include empty blocks,
A backup set will contain many backup pieces. A single backup piece consists of physical files which are in RMAN proprietary format.

Backups in Rman
Oracle Backup in RMAN are the following type

1. Backup sets ;
2. Image Copy

Example of Using Backup Using Rman.
Taking RMAN backup
In non archive mode in $ prompt type

]$ RMAN
- You get the RMAN prompt

RMAN > Connect Target /
Connect to target database : dharam < Dbid= 129283912>
Using target database controlfile instead of recovery catalog
Let us take a simple backup of database in Non Archive Mode

RMAN > Shutdown immediate; - Shutdown The database;

RMAN > Startup mount;

RMAN > Backup database; -- This starts backing the database

RMAN > sql 'Alter database open' ;

We can fire the same commands in archive log mode And whole of datafiles will be backed
RMAN > Backup database plus archivelog;

Restoring Database
Restoring database has been made very simple in 9i .
It is just 

RMAN >restore database;
Rman is intelligent to identify which dataifiles has to be restored and the locations of backed up
file.
Oracle Enhancement for Rman in 10g

1. Flash Recovery Area
2. Incrementally Updated Backups
3. Faster Incremental Backups
4. SWITCH DATABASE COMMAND.
5. Binary Compression
6. Global Scripting
7. Duration Clause
8. Configure This
9. Oracle Enhancement for Rman in 10g
10. Automatic Channel Failover
11. Compress Backup Sets
12. Recovery Through Reset Logs
13. Cross Backup Sets

Flash Backup and Recovery Area
With ever decreasing cost of disk in the market place ,disk based backups are more feasible. A The major advantage of flash backup and recovery area is once when the hard disk gets filled Automatically obslete backups are deleted reducing the need of DBA to do such tasks Or backups whose archive logs have been already been backed up are automatically deleted.The flash Recovery area notifies the administrator when the disk space consumption is nearing the defined quota and there is no more files to purge.

Incrementally Updated Backups
You can apply incremental backups to your datafile image copies when you use the RMAN. This takes much less time then performing a full image copy of datafiles every day.
This is applied through two phases.

1. Apply the incremental backup to datafile image copies .This is done at database block level.
2. Then apply the archive logs since the last incremental backup only .This is done at transaction level (Slower than previous phase)

Below you can see the example of incrementally updated Backups
RMAN > Backup incremental level 0 tag = weekly database; -- This takes a full database
backup .

The remaining days .. The below script works which creates a incremental backup
(Note the speed of incremental backup could be increased by using block change
tracking file which is mentioned below ) and the recover copy with tag weekly database
is used to add the incremental to the full backup .

RMAN > Backup incremental level 1
For recover of copy
With tag weekly database;

RMAN > Recover copy of database
With tag weekly;

Faster Incremental Backup
RMAN > Sql alter database enable block change tracking using file ‘c:\cpisolution\bij.dbf’ ;

Automatic Channel Failover
In Oracle 10g the behaviour of RMAN changes with regard to the failure of a channel During a RMAN backup . In Oracle Database 10g if a channel fails. The backup process On that channel fails and will not be restarted. However backups on remaining channels Will continue to run. Once the backup process is complete RMAN will report errors that Occurred during the backup process.

Compressed BackupSets

RMAN > Backup as compressed backupset database;
Note that only backupsets can be compressed ( eg database,tablespace and datafile
backups) image copies cannot be compressed.

Rman > configure device type disk backup type to compressed
backupset;

Binary compression technique reduces backup space usage 50-75 %

Recoverey Through ResetLogs
Prior to Oracle Database 10g it is recommended practice to make a full database backup following resetlogs. This is no longer required since backup can be used before resetlogs.

Some RMAN parameters

RMAN > Configure Retention Policty To Recovery Window of 5 days 

Retention Policy determines the length of time a backup is retained for use in a potential restore. A recovery window is a period of time that begins with the current time and extends backward in time to point of recoverability.
RMAN > Configure Retention policy to redundancy 3;
All backups older than the most recent backup that satisfied the above conditions are obsolete.
The recovery window always keeps in pace with the current time.
Specifying redundancy by using the CONFIGURE RETENTION POLICY TO REDUNDANCY command:
The REDUNDANCY parameter specifies that any number of backups or copies beyond a specified number does not need to be retained.
For example, the CONFIGURE RETENTION POLICY TO REDUNDANCY 3; command specifies that a maximum of three backups or copies of a data file or control file can be retained. Redundancy is the default type of retention policy.

RMAN > Configure Backup Optimization On
Read only tablespace or datafiles are backed only once per recovery window

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Configure RMAN to back up the control file after each backup .By default, RMAN automatically names control file backups and stores them in the flash recovery area.
To configure RMAN to write control file backups to the /test directory: ( %F will generate a unique filename)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/test/cf%F';

Block Media Recovery
Allows specified blocks to be recovered without affecting the entire datafile.. It is only intended for use where a known and limited number of blocks are affected. This results in a reduced mean time to recover (MTTR) and higher availability as only the affected blocks are offline during operation
The information regarding which block has to be recovered is found in The alert log file, Trace File,Analyze Table Command, Dbverify utility,V$Backup_Corruption & V$Copy_Corruption command.

Example of Using Block Recovery in RMAN.

RMAN> BLOCK RECOVER DATAFILE 3 BLOCK 121;

Trial Recovery
You can determine the outcome of a recovery before actually doing it for real by appending the work "test" to any recover command. The following command will cause Oracle to perform a dry-run of a recovery without actually changing any data:

SQL> recover database until cancel test;

Oracle Enterprise Manager has also been configured in such a manner that it makes it easy to manage all rman
backups and restore operations. It has buttons for quick crosschecks, showing current backups, implementing fast recovery ,changing basic rman configuration ,etc

Archive Log Failover
Archive log failover allows RMAN can take advantage of multiple archive logs destinations such that, if a corrupt log is detected it can read the log from the alternate destination
Obselete
RMAN> Delete obselete; will delete all obselege backups.
Expired are those backups which donot exist in the media.

Auto Backp of Control file
Whenever there is a structure change Aditonally It Supports

1.  Backup of Server Parameter File
2.  Parrallel Operation Supported
3.  Extensive Reporting Available
4.  Scripting
5.  Duplex Backup sets
6.  Corrupt Block Detection
7.  Backup archive Logs