Q : What is the reason for this - PCTUSED ? Why they want to control that parameter ? Can I set it to 0 ? or 100 ?
Q: Is free list this pre-allocated ? Is this only use for PCTFREE ? Where does it store ?
Can I set it to 0 ? or 100 ?
This is an interesting question. I would like to share my experience.
In the last 3 months, I had interviewed 10 expert Oracle DBAs and asked the same question. Only one person answered correctly.
First I would like to bring the very simple definition and than further i would explain in more details :
********************
PCTFREE: The PCTFREE parameter specifies the percentage of space in each data block that is reserved for growth resulting from updates of rows in that data block.
This parameter has a default value of 10 percent.
Note : For Update only.
PCTUSED :The PCTUSED parameter represents the minimum percentage of the used space that the Oracle server tries to maintain for each data block of the table for insert.
This parameter has a default value of 40 percent.
Note : For Insert only.
*******************
Pctused and Pctfree are block storage parameters.
Pctused is used to find out how much percentage of the block will be used to store rows.
Pctfree is used to find out how much percentage of the block will be used to store rows resulting from further updates to the rows in the same datablock.
Eg. If u keep pctused to 40% and pctfree 20.so u can insert rows till 40 %.if the limit exceeds 40%,still also u can insert rows in the datablock till the limit reaches 80% (100%-20%) as u have kept pctfree to 20%.
Now if one goes on deleting the rows,the block is not said to be free unless and until pctused falls below 40%.
As soon as pctused falls below 40% from deleting the rows, that block can be used to insert the rows.In this way the cycle continous.
So it is recommended that u never sum up pctused+pctfree=100.Always have some gap between them this helps in reducing ur Oracle server for allocation and disallocation of freelists.
************
Both the parameters are applicable and used for each data block in the Database.
I hope an example will give you the right answer.
Consider 8K block size. The total bytes 8 x 1024 = 8196 bytes
Each block requires approximately 107 bytes for the header. Please note that the header size varies depending upon the block size.
The total available bytes for data = ( 8196 – 107) = 8089 bytes.
A table is created with PCTFREE 20 PCTUSED 50 .
PCTRFREE in bytes = 1615
PCTUSED in bytes = 4039
Now the data available for insert only = (8079 – (20 * 8079)/100 ) = 6463 Bytes.
Now user can insert new rows into this block as long as the old rows’ total bytes + new row’s total byte is less than or equal to 6463 bytes.
If the new row’s total byte cannot be put into this block, then Oracle will get the next block from the free list, and inserts into it.
When a row is updated and the row’s data is expanded, then PCTFREE come into play. The updated row’s data is placed into PCTFREE’s area, provided the updated row’s new data can be fit into PCTFREE area.
If it is not fit into that area, another new block will be obtained from the Freelist, and the row will be migrated. But the original row info (pointer) is kept in the old block. For subsequent access to this row involves 2 read I/O. That is why row migration should be avoided because of excessive I/Os.
ROW DELETION:
The PCTUSED parameter value (in this example 50 %) is the threshold limit for the old block to be added in the FREELIST.
To understand better, let us assume that a block is of full data. Now the user starts deleting rows from the block. When a row is deleted, Oracle does not put the block into the FREELIST because it requires many recursive calls to update the FREELIST.
The PCTUSED % (50) determines when the block should be added into FREELIST. When the total bytes in the block is less than or equal to 4039 bytes, then the block will be added into FREELIST.
Note :
1 >If a table has high inserts and high deletion, then you should decrease the PCTUSED value in order to minimize the frequent update of FREELIST.
2> If pctfree is high then basically we are wasting hard drive space since only 10% of block is used for inserts, of course if you consider that you will update the rows so often and fill the 80% up then it´s up to you but I think it´s pretty rare.
I say 10% but it can be less since pctfree+pctused cant be more than 100 and if we set pctused 20 and pctfree 80 most probably we will face perfomance issues because the block has to be put on freelist and taken off free list all the time.
3>When PCTFREE is set to 0, then every update on a row requires a row migration.
When PCTUSED is set to 0 , then after deleting all rows in the block, it will be added into FREELIST.
4> We can not set PCTFREE =100 or PCTUSED=100 ,The value 100 is invalid value.
Also sum of PCTUSED and PCTFREE can not exceed 100.
SQL> create table test
2 (no number)
3 pctused 0
4 pctfree 100;
pctfree 100
*
ERROR at line 4:
ORA-02211: invalid value for PCTFREE or PCTUSED
SQL> create table test(no number)
2 pctused 60
3 pctfree 41;
create table test(no number)
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot exceed 100
Tuesday, December 14, 2010
Monday, December 13, 2010
How to adjust the High Watermark in ORACLE 10g – ALTER TABLE SHRINK
The High Watermark is the maximum fill-grade a table has ever reached.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.
First let’s have a look at the question when space is allocated
- when you create a table at least one extent (contiguous blocks) is allocated to the table
- if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
- if you have not specified MINEXTENTS then exactely one extent
will be allocated (we will look at extent sizes later in another post).
Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.
When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.
Now let us take a look at when space is released again from a segment like a table or index:
Let’s asume that we have filled a table with 100’0000 rows.
And let’s asume that we deleted 50’000 rows afterwards.
In this case the high watermark will have reached the level of 100’000 and will have stayed there.
Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this:
it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements …) .
Furthermore the physical addresses of the deleted row get recycled by new rows.
These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:
1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;
2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;
3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl … direct=y (default is direct=n)
All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.
So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.
So how can we release unused space from a table?
There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.
With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.
Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM,
in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!
ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.
After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;
This statement will procede in two steps:
- The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.
Table shrinking…
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:
- ALTER TABLE emp SHRINK SPACE; – only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well
- ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)
)
How are the indexes maintained?
In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment.
In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement.
Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.
Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.
The following restrictions apply to table shrinking:
1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs
The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.
This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.
You can use the built in package DBMS_SPACE to run the advisor manually as well.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.
First let’s have a look at the question when space is allocated
- when you create a table at least one extent (contiguous blocks) is allocated to the table
- if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
- if you have not specified MINEXTENTS then exactely one extent
will be allocated (we will look at extent sizes later in another post).
Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.
When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.
Now let us take a look at when space is released again from a segment like a table or index:
Let’s asume that we have filled a table with 100’0000 rows.
And let’s asume that we deleted 50’000 rows afterwards.
In this case the high watermark will have reached the level of 100’000 and will have stayed there.
Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this:
it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements …) .
Furthermore the physical addresses of the deleted row get recycled by new rows.
These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:
1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;
2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;
3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl … direct=y (default is direct=n)
All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.
So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.
So how can we release unused space from a table?
There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.
With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.
Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM,
in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!
ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.
After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;
This statement will procede in two steps:
- The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.
Table shrinking…
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:
- ALTER TABLE emp SHRINK SPACE; – only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well
- ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)
)
How are the indexes maintained?
In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment.
In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement.
Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.
Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.
The following restrictions apply to table shrinking:
1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs
The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.
This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.
You can use the built in package DBMS_SPACE to run the advisor manually as well.
Migrate Database to ASM Using RMAN
We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.
Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;
Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;
Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;
Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';
Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;
Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';
Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.
Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;
Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;
Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands
=
Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;
Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;
Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;
Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';
Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;
Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';
Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.
Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;
Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;
Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands
=
Saturday, December 11, 2010
Cloning A Database On The Same Server Using Rman Duplicate From Active Database
To clone a database using Rman we used to require an existing Rman backup,
on 11g we can clone databases using the “from active” database option.
In this case we do not require an existing backup, the active datafiles will be used as
the source for the clone.
In order to clone with the source database open it must be on archivelog mode.
Otherwise we can make the clone mounting the source database, as shown in
this example.
These are the steps required to complete the clone:
Configure The Network:
Create A Password File For The New Database:
Create An Init.Ora For The New Database:
Create The Admin Directory For The New Database:
Shutdown And Startup Mount The Source Database:
Startup Nomount The New Database:
Connect To The Target (Source) And Auxiliary (New Clone) Databases Using RMAN:
Execute The Duplicate Command:
Remove The Old Pfile:
Check The New Database:
1>>
Configure The Network
We need to configure the network so that there is connectivity to the target
(source) database and to the database we want to create (the clone)
Listener file:
# listener.ora Network Configuration File:
/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.
ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = redpanda)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = redpanda)
)
(SID_DESC =
(GLOBAL_DBNAME = rmancat)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = rmancat)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = redpanda))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = rmancat))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle/app
Tnsnames file:
redpanda =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = redpanda)
)
)
rmancat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmancat)
)
)
****************************************************
2>>>
Create A Password File For The New Database:
[oracle@lnx1 dbs]$ orapwd file=orapwredpanda password=oracle
**********
3>>>
Create An Init.Ora For The New Database
Create the pfile using create pfile from spfile from the source database, then
edit it changing all occurrences of old database name to new name
*.audit_file_dest='/oracle/app/admin/redpanda/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='’
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oradata'
*.db_domain=''
*.db_name='redpanda'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=redpandaXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
************************************************
4>>
Create The Admin Directory For The New Database:
mkdir –p /oracle/app/admin/redpanda/adump
Shutdown And Startup Mount The Source Database:
This is required if the source database is in no archivelog mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Startup Nomount The New Database:::
SQL> startup nomount
ORACLE instance started.
Connect To The Target (Source) And Auxiliary (New Clone)
Databases Using Rman
[oracle@lnx1 dbs]$ Rman target sys/oracle@rmancat auxiliary
sys/oracle@redpanda
Recovery Manager: Release 11.2.0.1.0 - Production on Sat
Mar 20 17:00:45 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: RMANCAT (DBID=3799896238, not
open)
connected to auxiliary database: REDPANDA (not mounted)
Execute The Duplicate Command :::::::::
RMAN> DUPLICATE TARGET DATABASE TO REDPANDA FROM ACTIVE
DATABASE;
Starting Duplicate Db at 20-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =''REDPANDA'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
restore clone controlfile to
'/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9rs
v97_.ctl' from
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''REDPANDA''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
Starting backup at 20-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_rmancat.f
tag=TAG20100320T170124 RECID=3 STAMP=714157285
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-MAR-10
Starting restore at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-MAR-10
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_system_5t9jpq6z_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_sysaux_5t9jpqfm_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_undotbs1_5t9jpqj4
_.dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_users_5t9jpqlz_.d
bf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-MAR-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
recover
clone database
noredo
delete archivelog
;
}
executing Memory Script
Starting recover at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
Finished recover at 20-MAR-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: alter system set db_name = ''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REDPANDA"
RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_.d
bf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_.dbf
",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bcs_.
dbf",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.dbf
";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/oracle/app/oradata/REDPANDA/datafile/o1_mf_temp_%u_.tmp in
control file
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf RECID=1 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf RECID=2 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf RECID=3 STAMP=714157503
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAR-10
*******************************************************
Remove The Old Pfile :
The duplicate database created an spfile that has the new controlfile
names in it, the old pfile, without controlfiles can be removed:
[oracle@lnx1 dbs]$ rm initredpanda.ora
Check The New Database
ORACLE_SID=redpanda
ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
[oracle@lnx1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 20
17:31:46 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
SQL> show sga
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 209718700 bytes
Database Buffers 50331648 bytes
Redo Buffers 2252800 bytes
on 11g we can clone databases using the “from active” database option.
In this case we do not require an existing backup, the active datafiles will be used as
the source for the clone.
In order to clone with the source database open it must be on archivelog mode.
Otherwise we can make the clone mounting the source database, as shown in
this example.
These are the steps required to complete the clone:
Configure The Network:
Create A Password File For The New Database:
Create An Init.Ora For The New Database:
Create The Admin Directory For The New Database:
Shutdown And Startup Mount The Source Database:
Startup Nomount The New Database:
Connect To The Target (Source) And Auxiliary (New Clone) Databases Using RMAN:
Execute The Duplicate Command:
Remove The Old Pfile:
Check The New Database:
1>>
Configure The Network
We need to configure the network so that there is connectivity to the target
(source) database and to the database we want to create (the clone)
Listener file:
# listener.ora Network Configuration File:
/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.
ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = redpanda)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = redpanda)
)
(SID_DESC =
(GLOBAL_DBNAME = rmancat)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = rmancat)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = redpanda))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = rmancat))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle/app
Tnsnames file:
redpanda =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = redpanda)
)
)
rmancat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmancat)
)
)
****************************************************
2>>>
Create A Password File For The New Database:
[oracle@lnx1 dbs]$ orapwd file=orapwredpanda password=oracle
**********
3>>>
Create An Init.Ora For The New Database
Create the pfile using create pfile from spfile from the source database, then
edit it changing all occurrences of old database name to new name
*.audit_file_dest='/oracle/app/admin/redpanda/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='’
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oradata'
*.db_domain=''
*.db_name='redpanda'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=redpandaXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
************************************************
4>>
Create The Admin Directory For The New Database:
mkdir –p /oracle/app/admin/redpanda/adump
Shutdown And Startup Mount The Source Database:
This is required if the source database is in no archivelog mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Startup Nomount The New Database:::
SQL> startup nomount
ORACLE instance started.
Connect To The Target (Source) And Auxiliary (New Clone)
Databases Using Rman
[oracle@lnx1 dbs]$ Rman target sys/oracle@rmancat auxiliary
sys/oracle@redpanda
Recovery Manager: Release 11.2.0.1.0 - Production on Sat
Mar 20 17:00:45 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: RMANCAT (DBID=3799896238, not
open)
connected to auxiliary database: REDPANDA (not mounted)
Execute The Duplicate Command :::::::::
RMAN> DUPLICATE TARGET DATABASE TO REDPANDA FROM ACTIVE
DATABASE;
Starting Duplicate Db at 20-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =''REDPANDA'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
restore clone controlfile to
'/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9rs
v97_.ctl' from
'/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl';
sql clone "alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'
',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv87_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv8j_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''RMANCAT'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''REDPANDA''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
Starting backup at 20-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_rmancat.f
tag=TAG20100320T170124 RECID=3 STAMP=714157285
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-MAR-10
Starting restore at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-MAR-10
sql statement: alter system set control_files =
''/oracle/app/oradata/REDPANDA/controlfile/o1_mf_5t9rsv91_.ctl'',
''/oracle/app/flash_recovery_area/REDPANDA/controlfile/o1_mf_5t9r
sv97_.ctl'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_system_5t9jpq6z_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_sysaux_5t9jpqfm_.
dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_undotbs1_5t9jpqj4
_.dbf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/oracle/app/oradata/RMANCAT/datafile/o1_mf_users_5t9jpqlz_.d
bf
output file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf tag=TAG20100320T170146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-MAR-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=714157476 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
recover
clone database
noredo
delete archivelog
;
}
executing Memory Script
Starting recover at 20-MAR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
Finished recover at 20-MAR-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: alter system set db_name = ''REDPANDA'' comment=
''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 205524396 bytes
Database Buffers 54525952 bytes
Redo Buffers 2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REDPANDA"
RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_.d
bf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_.dbf
",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bcs_.
dbf",
"/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.dbf
";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/oracle/app/oradata/REDPANDA/datafile/o1_mf_temp_%u_.tmp in
control file
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf RECID=1 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf RECID=2 STAMP=714157503
cataloged datafile copy
datafile copy file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf RECID=3 STAMP=714157503
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=714157503 file
name=/oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAR-10
*******************************************************
Remove The Old Pfile :
The duplicate database created an spfile that has the new controlfile
names in it, the old pfile, without controlfiles can be removed:
[oracle@lnx1 dbs]$ rm initredpanda.ora
Check The New Database
ORACLE_SID=redpanda
ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
[oracle@lnx1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 20
17:31:46 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_system_08l92b7r_
.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_sysaux_09l92bag_
.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_undotbs1_0al92bc
s_.dbf
/
oracle/app/oradata/REDPANDA/datafile/o1_mf_users_0bl92bd3_.
dbf
SQL> show sga
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 209718700 bytes
Database Buffers 50331648 bytes
Redo Buffers 2252800 bytes
Friday, December 10, 2010
Undo Segments are Marked as Needing Recovery .
Scenario :
1>
The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery.
2>
This goal walks you through changing the default undo tablespace from UNDOTBS1 to another undo tablespace. At the end of the exercise, you will drop the existing undo tablespace and datafiles and be left with a new undo tablespace in place.
This procedure requires shutting down the database.
This note was written because of a reoccurring problem with a fractured block(reported in v$backup_corruption) during RMAN backups.
Live example on my production box :******
An issue occured on a 9.2.0.8 DB with one of the datafile which is part of the UNDO tablespace needing recovery due to an underlying file system IO issue.
Restore of the datafiles involved did not work due to issues with the backup media
and the RMAN setup on that server.
So it was not feasible to troubleshoot a restore from a backup in the time frame given to fix the issue:
The alert log reports errors such as the below:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 2 (block # 686)
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27072: skgfdisp: I/O error
SVR4 Error: 5: I/O error
Additional information: 685
Mon Mar 15 02:25:45 2010
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
***********
Impact :
The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery
ERROR
-------------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
*************************************************************
Solution employed was to
(a) Create a new UNDO tablespace
(b) Switch to manual UNDO management
(c) Offline the undo segments needing recovery
(d) Dropping the old UNDO tablespace
(e) Switching back to automatic UNDO management
1. Determine the size of the datafile(s) for your current undo tablespace "UNDOTBS1":
SQL> select bytes, name from v$datafile where name like '%UNDO%';
BYTES NAME
-------------- ----------------------------------------------------
314572800 /ora/data4/BATR1SI/BATR1SIundots01.dbf
2.
Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTS2
datafile '/ora/data4/BATR1SI/BATR1SIundots02.dbf' size 500M autoextend on;
3.
Edit your init.ora file and change the parameter "undo_tablespace=" so it points to the newly created tablespace. You may need to create a pfile first:
SQL> create pfile='/ora/data4/BATR1SI/pfileorcl2.ora' from spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA';
File created.
Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL
Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database (specify the pfile if you created one in step 3.)
SQL> startup pfile='/ora/data4/BATR1SI/pfileorcl2.ora';
6. Confirm the new tablespace is in use:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------- -------------- -----------------
undo_tablespace string UNDOTBS2
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
----- --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE
....etc.
If the old segments are online, then they must be taken offline:
SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;
This should be executed for all online rollback segments in the old tablespace.
8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
9. Recreate your spfile with the new undo_tablespace value and change undo_management to AUTO:
undo_management='AUTO'
undo_tablespace='UNDOTBS2'
SQL> create spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA' from pfile='/ora/data4/BATR1SI/pfileorcl2.ora';
File created.
10. Shutdown the database (shutdown immediate) and restart it with the spfile.
1>
The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery.
2>
This goal walks you through changing the default undo tablespace from UNDOTBS1 to another undo tablespace. At the end of the exercise, you will drop the existing undo tablespace and datafiles and be left with a new undo tablespace in place.
This procedure requires shutting down the database.
This note was written because of a reoccurring problem with a fractured block(reported in v$backup_corruption) during RMAN backups.
Live example on my production box :******
An issue occured on a 9.2.0.8 DB with one of the datafile which is part of the UNDO tablespace needing recovery due to an underlying file system IO issue.
Restore of the datafiles involved did not work due to issues with the backup media
and the RMAN setup on that server.
So it was not feasible to troubleshoot a restore from a backup in the time frame given to fix the issue:
The alert log reports errors such as the below:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 2 (block # 686)
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27072: skgfdisp: I/O error
SVR4 Error: 5: I/O error
Additional information: 685
Mon Mar 15 02:25:45 2010
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
***********
Impact :
The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery
ERROR
-------------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
*************************************************************
Solution employed was to
(a) Create a new UNDO tablespace
(b) Switch to manual UNDO management
(c) Offline the undo segments needing recovery
(d) Dropping the old UNDO tablespace
(e) Switching back to automatic UNDO management
1. Determine the size of the datafile(s) for your current undo tablespace "UNDOTBS1":
SQL> select bytes, name from v$datafile where name like '%UNDO%';
BYTES NAME
-------------- ----------------------------------------------------
314572800 /ora/data4/BATR1SI/BATR1SIundots01.dbf
2.
Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTS2
datafile '/ora/data4/BATR1SI/BATR1SIundots02.dbf' size 500M autoextend on;
3.
Edit your init.ora file and change the parameter "undo_tablespace=" so it points to the newly created tablespace. You may need to create a pfile first:
SQL> create pfile='/ora/data4/BATR1SI/pfileorcl2.ora' from spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA';
File created.
Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL
Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.
5. Startup the database (specify the pfile if you created one in step 3.)
SQL> startup pfile='/ora/data4/BATR1SI/pfileorcl2.ora';
6. Confirm the new tablespace is in use:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------- -------------- -----------------
undo_tablespace string UNDOTBS2
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
----- --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE
....etc.
If the old segments are online, then they must be taken offline:
SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;
This should be executed for all online rollback segments in the old tablespace.
8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
SQL>drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
9. Recreate your spfile with the new undo_tablespace value and change undo_management to AUTO:
undo_management='AUTO'
undo_tablespace='UNDOTBS2'
SQL> create spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA' from pfile='/ora/data4/BATR1SI/pfileorcl2.ora';
File created.
10. Shutdown the database (shutdown immediate) and restart it with the spfile.
Renaming / Moving Data Files, Control Files, and Online Redo Logs
Contents
1. Overview
2. Moving Datafiles while the Instance is Mounted
3. Moving Datafiles while the Instance is Open
4. Moving Online Redo Log Files
5. Moving Control Files
Overview:
Once a data file has been created in the database, it may be necessary to move it in order to better manage its size or I/O requirements. This article will provide several methods used by DBAs for moving datafiles, online redo log files and control files. In all of these methods, operating system commands are used to move the files while the Oracle commands serve primarily to reset the pointers to those files.
There are two methods for moving / renaming physical database files within Oracle. The first is to shut the database down, move (or rename) the file(s) using O/S commands, and finally, use the ALTER DATABASE command to reset the pointers to those files within Oracle.
The second method can be done while the database is running and uses the ALTER TABLESPACE command. The tablespace will need to be taken offline during the time the file(s) are being moved or renamed. Once the files are moved (or renamed), use the ALTER TABLESPACE command to reset the pointers within Oracle and finally, bring the tablespace back online. This method only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments.
Following is an example of how to manipulate datafiles in a tablespace using both the alter database method and the alter tablespace method. All examples will use an Oracle9i databse (9.2.0.5.0) running on Sun Solaris 2.9.
XXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Mounted:
Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move or rename the files(s).
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> startup mount
SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Open
Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running. A summary of the steps involved follows:
1. Take the tablespace OFFLINE.
2. Use operating system commands to move or rename the file(s).
3. Use the ALTER TABLESPACE command to rename the file within the database.
4. Bring the tablespace back ONLINE.
NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments.
% sqlplus "/ as sysdba"
SQL> alter tablespace INDX offline;
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to bring the tablespace back online!
SQL> alter tablespace INDX online;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Online Redo Log Files
Online redo log files may be moved while the database is shutdown. Once renamed (or moved) the DBA should use the ALTER DATABASE command to update the data dictionary. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move the datafile.
3. Mount the database and use ALTER DATABASE to rename the log file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log
SQL> startup mount
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03a.log' to '/u03/app/oradata/ORA920/redo_g03a.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to '/u04/app/oradata/ORA920/redo_g03b.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to '/u05/app/oradata/ORA920/redo_g03c.log';
Or
Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database redo log files.
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
*******************************************************
Live example in our environment in production box:
1> Requirement :
Redo files are created in rd002 and rd003 for instance 5, once we have the rd005 file available all we need to do is
move /ora/rd003/SCRM01P/SCRM01P5redo*.log files to /ora/rd005/SCRM01P
In order to move redo to new file system
1. Check the redolog file group is not current and no longer active
2. Alter system drop logfile group (??)
3. Delete OS files.
4. Add redo making sure the rd003 replaced with rd005.
****************
Important : No Downtime Require for the below action.
Solution :
1. Run the following SQL to locate the current redo log groups and their members:
select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Note : Check the redolog file group is not current and no longer active, Than drop the redologfile from Thread#=5(Group 33 -40).
Steps 2 :=====>>>>> Drop the all inactive redolog from Thread#5(Group 33-40):
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 33 ‘/ora/rd005/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 34 '/ora/rd005/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 35 '/ora/rd005/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 36 '/ora/rd005/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo37b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 37 '/ora/rd005/SCRM01P/SCRM01P5redo37b.log';
Note : Status is giving active so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo38b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 38 '/ora/rd005/SCRM01P/SCRM01P5redo38b.log';
Note : Status is giving Current so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 39 '/ora/rd005/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo40b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 40 '/ora/rd005/SCRM01P/SCRM01P5redo40b.log';
Steps 3 =>>>> To check the new values of redolog files mirroring mount point for thread 5 with below sql statement.
SQL> select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Steps 4 ==> Remove the old redolog files(mirroring) for Thread 5 under mount point '/ora/rd003/SCRM01P/'.
Login in server nus986pc ==>>
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo33b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo34b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo35b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo36b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo37b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo38b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo39b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo40b.log
select
a.ksppinm name,
b.ksppstvl value,
a.ksppdesc description
from
x$ksppi a,
x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm = '_ksmg_granule_size';
NAME VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------
_ksmg_granule_size 16777216 granule size in bytes
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Another Live schenario :
Requirement to rename the datafile without downtime:
vus252:MNCP:/ora/admin>. oraenv
ORACLE_SID = [MNCP] ?
vus252:MNCP:/ora/admin>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 15:17:41 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Before shutdown the database you can do the all below action ==>>>
Check the alert log file.
Take the tablespace MNC_BULK_DATA and datafile(MNCPmnc_bulk_data_06.dbf) location from below query.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
SQL > ALTER TABLESPACE MNC_BULK_DATA OFFLINE;
cp /ora/data/da001/MNCPmnc_bulk_data_06.dbf /ora/archive/MNCPmnc_bulk_data_06.dbf
SQL> ALTER TABLESPACE MNC_BULK_DATA RENAME DATAFILE '/ora/data/da001/MNCPmnc_bulk_data_06.dbf' TO '/ora/archive/MNCPmnc_bulk_data_06.dbf’;
SQL > ALTER TABLESPACE MNC_BULK_DATA ONLINE;
Check the new location of datafile for Table space MNC_BULK_DATA from below command.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
Once everything goes well, Remove the files from old location
rm /ora/data/da001/MNCPmnc_bulk_data_06.dbf
You can check the space in /ora/data/da001/ it should be around 4% available capacity( df -k /ora/data/da001/).
1. Overview
2. Moving Datafiles while the Instance is Mounted
3. Moving Datafiles while the Instance is Open
4. Moving Online Redo Log Files
5. Moving Control Files
Overview:
Once a data file has been created in the database, it may be necessary to move it in order to better manage its size or I/O requirements. This article will provide several methods used by DBAs for moving datafiles, online redo log files and control files. In all of these methods, operating system commands are used to move the files while the Oracle commands serve primarily to reset the pointers to those files.
There are two methods for moving / renaming physical database files within Oracle. The first is to shut the database down, move (or rename) the file(s) using O/S commands, and finally, use the ALTER DATABASE command to reset the pointers to those files within Oracle.
The second method can be done while the database is running and uses the ALTER TABLESPACE command. The tablespace will need to be taken offline during the time the file(s) are being moved or renamed. Once the files are moved (or renamed), use the ALTER TABLESPACE command to reset the pointers within Oracle and finally, bring the tablespace back online. This method only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments.
Following is an example of how to manipulate datafiles in a tablespace using both the alter database method and the alter tablespace method. All examples will use an Oracle9i databse (9.2.0.5.0) running on Sun Solaris 2.9.
XXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Mounted:
Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move or rename the files(s).
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> startup mount
SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Open
Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running. A summary of the steps involved follows:
1. Take the tablespace OFFLINE.
2. Use operating system commands to move or rename the file(s).
3. Use the ALTER TABLESPACE command to rename the file within the database.
4. Bring the tablespace back ONLINE.
NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments.
% sqlplus "/ as sysdba"
SQL> alter tablespace INDX offline;
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to bring the tablespace back online!
SQL> alter tablespace INDX online;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Online Redo Log Files
Online redo log files may be moved while the database is shutdown. Once renamed (or moved) the DBA should use the ALTER DATABASE command to update the data dictionary. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move the datafile.
3. Mount the database and use ALTER DATABASE to rename the log file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log
SQL> startup mount
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03a.log' to '/u03/app/oradata/ORA920/redo_g03a.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to '/u04/app/oradata/ORA920/redo_g03b.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to '/u05/app/oradata/ORA920/redo_g03c.log';
Or
Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database redo log files.
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
*******************************************************
Live example in our environment in production box:
1> Requirement :
Redo files are created in rd002 and rd003 for instance 5, once we have the rd005 file available all we need to do is
move /ora/rd003/SCRM01P/SCRM01P5redo*.log files to /ora/rd005/SCRM01P
In order to move redo to new file system
1. Check the redolog file group is not current and no longer active
2. Alter system drop logfile group (??)
3. Delete OS files.
4. Add redo making sure the rd003 replaced with rd005.
****************
Important : No Downtime Require for the below action.
Solution :
1. Run the following SQL to locate the current redo log groups and their members:
select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Note : Check the redolog file group is not current and no longer active, Than drop the redologfile from Thread#=5(Group 33 -40).
Steps 2 :=====>>>>> Drop the all inactive redolog from Thread#5(Group 33-40):
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 33 ‘/ora/rd005/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 34 '/ora/rd005/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 35 '/ora/rd005/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 36 '/ora/rd005/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo37b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 37 '/ora/rd005/SCRM01P/SCRM01P5redo37b.log';
Note : Status is giving active so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo38b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 38 '/ora/rd005/SCRM01P/SCRM01P5redo38b.log';
Note : Status is giving Current so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 39 '/ora/rd005/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo40b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 40 '/ora/rd005/SCRM01P/SCRM01P5redo40b.log';
Steps 3 =>>>> To check the new values of redolog files mirroring mount point for thread 5 with below sql statement.
SQL> select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Steps 4 ==> Remove the old redolog files(mirroring) for Thread 5 under mount point '/ora/rd003/SCRM01P/'.
Login in server nus986pc ==>>
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo33b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo34b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo35b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo36b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo37b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo38b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo39b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo40b.log
select
a.ksppinm name,
b.ksppstvl value,
a.ksppdesc description
from
x$ksppi a,
x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm = '_ksmg_granule_size';
NAME VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------
_ksmg_granule_size 16777216 granule size in bytes
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Another Live schenario :
Requirement to rename the datafile without downtime:
vus252:MNCP:/ora/admin>. oraenv
ORACLE_SID = [MNCP] ?
vus252:MNCP:/ora/admin>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 15:17:41 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Before shutdown the database you can do the all below action ==>>>
Check the alert log file.
Take the tablespace MNC_BULK_DATA and datafile(MNCPmnc_bulk_data_06.dbf) location from below query.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
SQL > ALTER TABLESPACE MNC_BULK_DATA OFFLINE;
cp /ora/data/da001/MNCPmnc_bulk_data_06.dbf /ora/archive/MNCPmnc_bulk_data_06.dbf
SQL> ALTER TABLESPACE MNC_BULK_DATA RENAME DATAFILE '/ora/data/da001/MNCPmnc_bulk_data_06.dbf' TO '/ora/archive/MNCPmnc_bulk_data_06.dbf’;
SQL > ALTER TABLESPACE MNC_BULK_DATA ONLINE;
Check the new location of datafile for Table space MNC_BULK_DATA from below command.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
Once everything goes well, Remove the files from old location
rm /ora/data/da001/MNCPmnc_bulk_data_06.dbf
You can check the space in /ora/data/da001/ it should be around 4% available capacity( df -k /ora/data/da001/).
Subscribe to:
Posts (Atom)