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/).
Tuesday, October 26, 2010
ORA-01536: SPACE quota exceeded for tablespace
ORA-01536: space quota exceeded for tablespace is a common error in Oracle Database.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.
It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.
Below example presents how we can generate ORA-01535 in Oracle database, and how we can resolve it.
We start from creating a user with default tablespace set to USERS. Then we grant create session and create table privileges, to enable the user to connect to database and create a table. We also grant initially 1 MB quota on tablspace users.
SQL> conn / as sysdba
Connected.
SQL> set linesize 160
SQL> create user user1 default tablespace users quota 1M on users temporary tablespace temp identified by pass1
2 /
User created.
SQL> grant create session, create table to user1
2 /
Grant succeeded
Then we try to create a table using CTAS (create table as select) based on ALL_OBJECTS view. ALL_OBJECTS view
contains a list of ALL_OBJECTS in database.
SQL> conn user1/pass1
Connected.
SQL> set linesize 160
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
The error occurred while we tried to create the table, due to the fact that table t1 would require around 7MB of space and user1 quote was set to just 1MB.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 1
To resolve the problem we enlarge user1 quota to 10MB.
SQL> alter user user1 quota 10m on users
2 /
User altered.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
Table created.
As we can see the table was created successfully. So the last thing to do is to check the quota usage.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10
It is worth to remember that instead of granting quota in megabytes, we can grant UNLIMITED QUOTA on tablespace for
a user. In my opinion it sometimes be a security whole, but on other ocasions it can be useful.
So let's start from dropping the table t1.
SQL> conn user1/pass1
Connected.
SQL> drop table t1
2 /
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- --------
recyclebin string on
After table drop you see that space used by the user has not been change.
You probably wondering why, the reson is simple, I used the statement DROP TABLE tableName; instead of DROP TABLE tableName PURGE; and I have RECYCLE_BIN ON. Below query allows be to confirm that my table is in RECYCLEBIN.
SQL> set linesize 160
SQL> column owner format a15
SQL> column original_name format a20
SQL> column can_purge format a10
SQL> column type format a10
SQL> select owner, object_name, original_name, operation, type, can_purge
2 from dba_recyclebin
3 where owner = 'USER1' and type = 'TABLE'
4 /
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
USER1 BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES
To purge the table from recyclebin we will login as user1 and execute PURGE TABLE statement.
SQL> conn user1/pass1
Connected.
SQL>select object_name, original_name, operation, type, can_purge
1 from user_recyclebin
2 where type = 'TABLE'
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES
SQL> purge table "BIN$2JQWhCy4RIiMi4/epuYlSQ==$0"
2 /
Table purged.
Confirmation of successful purge operation.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10
Please note that above behavior with RECYCLEBIN will only work is instance parameter recyclebin is set to on, and if you are working on Oracle 10g or later. The above experiment was performed on Oracle 11g.
We can now replay our initial test and check that UNLIMITED QUOTA WORKS.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10
SQL> alter user user1 quota 1M on users
2 /
User altered.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> conn / as sysdba
Connected.
SQL> alter user user1 quota unlimited on users
2 /
User altered.
Unlimited Quota is denoted by -1 in MAX_BYTES column of DBA_TS_QUOTAS view.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
2 decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"
3 from dba_ts_quotas
4 where tablespace_name = 'USERS' and username = 'USER1'
5 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 UNLIMITED
Finally we can run our CREATE TABLE statement.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
Table created.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.
It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.
Below example presents how we can generate ORA-01535 in Oracle database, and how we can resolve it.
We start from creating a user with default tablespace set to USERS. Then we grant create session and create table privileges, to enable the user to connect to database and create a table. We also grant initially 1 MB quota on tablspace users.
SQL> conn / as sysdba
Connected.
SQL> set linesize 160
SQL> create user user1 default tablespace users quota 1M on users temporary tablespace temp identified by pass1
2 /
User created.
SQL> grant create session, create table to user1
2 /
Grant succeeded
Then we try to create a table using CTAS (create table as select) based on ALL_OBJECTS view. ALL_OBJECTS view
contains a list of ALL_OBJECTS in database.
SQL> conn user1/pass1
Connected.
SQL> set linesize 160
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
The error occurred while we tried to create the table, due to the fact that table t1 would require around 7MB of space and user1 quote was set to just 1MB.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 1
To resolve the problem we enlarge user1 quota to 10MB.
SQL> alter user user1 quota 10m on users
2 /
User altered.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
Table created.
As we can see the table was created successfully. So the last thing to do is to check the quota usage.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10
It is worth to remember that instead of granting quota in megabytes, we can grant UNLIMITED QUOTA on tablespace for
a user. In my opinion it sometimes be a security whole, but on other ocasions it can be useful.
So let's start from dropping the table t1.
SQL> conn user1/pass1
Connected.
SQL> drop table t1
2 /
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- --------
recyclebin string on
After table drop you see that space used by the user has not been change.
You probably wondering why, the reson is simple, I used the statement DROP TABLE tableName; instead of DROP TABLE tableName PURGE; and I have RECYCLE_BIN ON. Below query allows be to confirm that my table is in RECYCLEBIN.
SQL> set linesize 160
SQL> column owner format a15
SQL> column original_name format a20
SQL> column can_purge format a10
SQL> column type format a10
SQL> select owner, object_name, original_name, operation, type, can_purge
2 from dba_recyclebin
3 where owner = 'USER1' and type = 'TABLE'
4 /
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
USER1 BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES
To purge the table from recyclebin we will login as user1 and execute PURGE TABLE statement.
SQL> conn user1/pass1
Connected.
SQL>select object_name, original_name, operation, type, can_purge
1 from user_recyclebin
2 where type = 'TABLE'
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES
SQL> purge table "BIN$2JQWhCy4RIiMi4/epuYlSQ==$0"
2 /
Table purged.
Confirmation of successful purge operation.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10
Please note that above behavior with RECYCLEBIN will only work is instance parameter recyclebin is set to on, and if you are working on Oracle 10g or later. The above experiment was performed on Oracle 11g.
We can now replay our initial test and check that UNLIMITED QUOTA WORKS.
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10
SQL> alter user user1 quota 1M on users
2 /
User altered.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> conn / as sysdba
Connected.
SQL> alter user user1 quota unlimited on users
2 /
User altered.
Unlimited Quota is denoted by -1 in MAX_BYTES column of DBA_TS_QUOTAS view.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
2 decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"
3 from dba_ts_quotas
4 where tablespace_name = 'USERS' and username = 'USER1'
5 /
TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 UNLIMITED
Finally we can run our CREATE TABLE statement.
SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
Table created.
Thursday, October 14, 2010
Why excessive redo logs and archive logs are getting generated when we start a hot backup of database ?
This question was asked always in all interview datafile get updated with online transaction during hot backup or not , if not than why ?
Most of people always failed to convenience interviewer with valid reason. The below comments will help out to understand the complete flow of data during hot backup.
Many of you must have heard or experienced that while taking hot backup of database LGWR process writes aggressively. Meaning that more redo data has been written to redo log file and consecutively more archive logs gets generated.
Here is the common misconception we have in our mind. If some one ask, why excessive redo logs and archive logs are getting generated when we start a hot backup of database ??????????
Quickly we answer .. Its simple, when we put tablespace in hot backup mode,
Oracle will take a check point of tablespace and data files belonging to this tablespace will be freezed.
Any user activity happening on objects belonging to this tablespace wont write data to these datafiles, instead it will write data to redo log files. So obviously there will be more redo log file generation.
Well, to some extent this is COMPLETELY WRONG !!!!!!!!!!!!!!!!!!
I will straight way come to the point and explain you what happens when we put the tablespace in hot backup mode.
When a tablespace is put into backup mode, the following three things happen:
1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.
2. The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.
3. Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block (the change vector), it will log the entire image of the block after the change. This is why the redologs grow at a much faster rate while hot backups are going on.
Your first assumption that datafiles belonging to the tablespace in hot backup mode is freezed is wrong.
Datafiles are not freezed, only the datafile headers will be freezed !!!!!!!
So simply imagine that when you put the tablespace in backup mode, Oracle will take a checkpoint and update the datafile headers with checkpoint SCN and there after it is freezed until we take tablespace out of backup mode.
Other datafile (other then header part) remains as normal and data changes happens continuously to this datafile.
Now you may want to ask me “do I mean to say that datafiles gets updated continuously even when we are coping the same to backup location ?”. The answer is YES. Never think that the datafile you are coping is “Consistent”. No, datafiles gets changed continuously !!!!!!!!!!!!!!
You might want to ask couple of more questions then.
1) If we say that backup file is not consistent and changes continuously, then how come Oracle is able to recover the database when we restore that datafile?
2) If the data changes are anyway happening continuously on data files, then why there is excess redo log generation ?
Thats it !! don’t ask me more then this. Let me explain answers to these questions.
Consider a typical case, where an Oracle database is installed on Linux platform. The standard Oracle block size if 8K and lets say that OS level data block size is 512K.
Now when we put the tablespace in “Begin Backup” mode checkpoint has happened and datafile header is freezed.
You found which are the files related to this tablespace and started copying using OS command. Now when you copy a datafile using OS command it is going to copy as per OS block size.
Lets say when you start copying it gave 8 blocks to you to copy – that means you are copying 4K (512K X 4) to backup location.
That means you are copying half of Oracle block to backup location. Now this process of copy can be preempted by Server CPU depending on load. Lets say when you started copying after copy of those 8 block (4K, half of Oracle block), your process get preempted by CPU and it has allocated CPU time to some other important process.
Mean while DBWR process changes that block that you have copied halfway (since datafile is not freezed and only header is freezed, continuous updates can happen to datafile).
After a while CPU returns back and gives you next 8 blocks to copy (rest of the halk Oracle block). Now here is the problem !!!!!!!!!!!
we copied half of the oracle block taken at time T0 and another half taken at time T1 and in-between the data block got changed.
Does this sounds consistent ? Not to me !!!!!!!!!!!!!!!!!!!!!!!!!
Such type of block is called “Fractured Block”.
Well, since Oracle copies files like this it should do some thing, so that during recovery it wont face any problem.
Usually in case of a normal tablespace (which is not in begin backup mode), when a transaction happens oracle generates redo information and puts in redo log file.
This is the bare minimum information that oracle generates in order to redo the information. It does not copy the complete block.
Where as in case of begin backup mode, if a transaction happens and changes any block FOR THE FIST TIME, oracle copies the complete block to redo log file. This happens only during first time.
If subsequent transaction updates the same block again, oracle will not copy the complete block to redo, instead it will generate minimum information to redo the changes. Now because oracle has to copy the complete block when it changes for the first time in begin backup mode, we say that excess redo gets generated when we put tablespace in begin backup mode.
Question arises, why Oracle has to copy the complete block to redo log files. As you have seen above that during copy of datafile, there can be many fractured blocks, and during restore and recovery its going to put those block back and try to recover.
Now assume that block is fractured and oracle has minimum information that it generates in the redo. Under such condition it wont be possible for Oracle to recover such blocks. So instead Oracle just copies the entire block back from redo log files to datafiles during recovery process. This will make the datafile consistent.
So recovery process is very important which takes care of all fractured blocks and makes it possible to recover a database.
I hope this explains above 2 questions.
Now you can easily explain why hot backup is not possible if database is in NOARCHIVELOG mode.
When you take a backup using RMAN, it does not generate excessive redo logs. The reason is simple. RMAN is intelligent. It does not use OS block for copying, instead it uses oracle blocks for copying datafiles so the files are consistent.
Hope this helps !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Most of people always failed to convenience interviewer with valid reason. The below comments will help out to understand the complete flow of data during hot backup.
Many of you must have heard or experienced that while taking hot backup of database LGWR process writes aggressively. Meaning that more redo data has been written to redo log file and consecutively more archive logs gets generated.
Here is the common misconception we have in our mind. If some one ask, why excessive redo logs and archive logs are getting generated when we start a hot backup of database ??????????
Quickly we answer .. Its simple, when we put tablespace in hot backup mode,
Oracle will take a check point of tablespace and data files belonging to this tablespace will be freezed.
Any user activity happening on objects belonging to this tablespace wont write data to these datafiles, instead it will write data to redo log files. So obviously there will be more redo log file generation.
Well, to some extent this is COMPLETELY WRONG !!!!!!!!!!!!!!!!!!
I will straight way come to the point and explain you what happens when we put the tablespace in hot backup mode.
When a tablespace is put into backup mode, the following three things happen:
1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.
2. The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.
3. Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block (the change vector), it will log the entire image of the block after the change. This is why the redologs grow at a much faster rate while hot backups are going on.
Your first assumption that datafiles belonging to the tablespace in hot backup mode is freezed is wrong.
Datafiles are not freezed, only the datafile headers will be freezed !!!!!!!
So simply imagine that when you put the tablespace in backup mode, Oracle will take a checkpoint and update the datafile headers with checkpoint SCN and there after it is freezed until we take tablespace out of backup mode.
Other datafile (other then header part) remains as normal and data changes happens continuously to this datafile.
Now you may want to ask me “do I mean to say that datafiles gets updated continuously even when we are coping the same to backup location ?”. The answer is YES. Never think that the datafile you are coping is “Consistent”. No, datafiles gets changed continuously !!!!!!!!!!!!!!
You might want to ask couple of more questions then.
1) If we say that backup file is not consistent and changes continuously, then how come Oracle is able to recover the database when we restore that datafile?
2) If the data changes are anyway happening continuously on data files, then why there is excess redo log generation ?
Thats it !! don’t ask me more then this. Let me explain answers to these questions.
Consider a typical case, where an Oracle database is installed on Linux platform. The standard Oracle block size if 8K and lets say that OS level data block size is 512K.
Now when we put the tablespace in “Begin Backup” mode checkpoint has happened and datafile header is freezed.
You found which are the files related to this tablespace and started copying using OS command. Now when you copy a datafile using OS command it is going to copy as per OS block size.
Lets say when you start copying it gave 8 blocks to you to copy – that means you are copying 4K (512K X 4) to backup location.
That means you are copying half of Oracle block to backup location. Now this process of copy can be preempted by Server CPU depending on load. Lets say when you started copying after copy of those 8 block (4K, half of Oracle block), your process get preempted by CPU and it has allocated CPU time to some other important process.
Mean while DBWR process changes that block that you have copied halfway (since datafile is not freezed and only header is freezed, continuous updates can happen to datafile).
After a while CPU returns back and gives you next 8 blocks to copy (rest of the halk Oracle block). Now here is the problem !!!!!!!!!!!
we copied half of the oracle block taken at time T0 and another half taken at time T1 and in-between the data block got changed.
Does this sounds consistent ? Not to me !!!!!!!!!!!!!!!!!!!!!!!!!
Such type of block is called “Fractured Block”.
Well, since Oracle copies files like this it should do some thing, so that during recovery it wont face any problem.
Usually in case of a normal tablespace (which is not in begin backup mode), when a transaction happens oracle generates redo information and puts in redo log file.
This is the bare minimum information that oracle generates in order to redo the information. It does not copy the complete block.
Where as in case of begin backup mode, if a transaction happens and changes any block FOR THE FIST TIME, oracle copies the complete block to redo log file. This happens only during first time.
If subsequent transaction updates the same block again, oracle will not copy the complete block to redo, instead it will generate minimum information to redo the changes. Now because oracle has to copy the complete block when it changes for the first time in begin backup mode, we say that excess redo gets generated when we put tablespace in begin backup mode.
Question arises, why Oracle has to copy the complete block to redo log files. As you have seen above that during copy of datafile, there can be many fractured blocks, and during restore and recovery its going to put those block back and try to recover.
Now assume that block is fractured and oracle has minimum information that it generates in the redo. Under such condition it wont be possible for Oracle to recover such blocks. So instead Oracle just copies the entire block back from redo log files to datafiles during recovery process. This will make the datafile consistent.
So recovery process is very important which takes care of all fractured blocks and makes it possible to recover a database.
I hope this explains above 2 questions.
Now you can easily explain why hot backup is not possible if database is in NOARCHIVELOG mode.
When you take a backup using RMAN, it does not generate excessive redo logs. The reason is simple. RMAN is intelligent. It does not use OS block for copying, instead it uses oracle blocks for copying datafiles so the files are consistent.
Hope this helps !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Wednesday, October 13, 2010
Virtual IP vip in Oracle RAC in Place of Public IP
Question : Why VIP is used in RAC in place of Public ip(9i rac db).
This question was asked by many RAC DBA expert and i was always failed to convince them for valid reason.
But I am sure now after read the below comments you will be in positions to convince them with valid reason .....
How new connection establish in Oracle RAC?
For failover configuration we should need to configure our physical ip of host name in listener configuration. Listener process is accepting new connection request and handover user process to server process or dispatcher process in Oracle.
Means using listener new connection is being established by Oracle. Once connection get established there is no need of listener process. If new connection is trying to get session in database and listener is down then what will be happening.
User process gets error message and connection fails. Because listener is down in same host or something else problem. But in Oracle RAC database environment database is in sharing mode. Oracle RAC database is shared by all connected nodes. Means more than 1 listeners are running in various nodes.
===>> How it work in Oracle 9I RAC DB ==>>
In Oracle RAC database if user process is trying to get connection with some listener and found listener is down or node is down then Oracle RAC automatically transfer this request to another listener on another node.
Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout.
Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.
Using physical IP address there is biggest gap to get TCP/IP timeout for failover suggestion. Session should need to wait for same timeout. High availability of Oracle RAC depends on this time wasting error message.
===>>> Why VIP (Virtual IP) needs in Oracle RAC? ===>
From Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.
Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node.
This process doesn't wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.
====>>> Characteristic of Virtual IP in Oracle RAC: <====
Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities or commands and kept smaller.
But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable. There is utility also provided to configure virtual IP (vip) with RAC environment called VIPCA. Default path is $ORA_CRS_HOME/bin. During installation of Oracle RAC, it is executed.
==>>>>>>> Advantage of Virtual IP deployment in Oracle RAC: <<===
Using VIP configuration, client can be able to get connection fast even fail over of connection request to node. Because vip automatically assign to another surviving node faster and it can’t wait for TNS timeout old fashion.
===>>>>>>>Disadvantage of Virtual IP deploymenin Oracle RAC: <<==
Some more configurations is needed in system for assign virtual IP address to nodes like in /etc/hosts and others. Some misunderstanding or confusion may occur due to multiple IP assigns in same node.
===>><<<== Important for VIP configuration: <<<====
The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.
This question was asked by many RAC DBA expert and i was always failed to convince them for valid reason.
But I am sure now after read the below comments you will be in positions to convince them with valid reason .....
How new connection establish in Oracle RAC?
For failover configuration we should need to configure our physical ip of host name in listener configuration. Listener process is accepting new connection request and handover user process to server process or dispatcher process in Oracle.
Means using listener new connection is being established by Oracle. Once connection get established there is no need of listener process. If new connection is trying to get session in database and listener is down then what will be happening.
User process gets error message and connection fails. Because listener is down in same host or something else problem. But in Oracle RAC database environment database is in sharing mode. Oracle RAC database is shared by all connected nodes. Means more than 1 listeners are running in various nodes.
===>> How it work in Oracle 9I RAC DB ==>>
In Oracle RAC database if user process is trying to get connection with some listener and found listener is down or node is down then Oracle RAC automatically transfer this request to another listener on another node.
Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout.
Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.
Using physical IP address there is biggest gap to get TCP/IP timeout for failover suggestion. Session should need to wait for same timeout. High availability of Oracle RAC depends on this time wasting error message.
===>>> Why VIP (Virtual IP) needs in Oracle RAC? ===>
From Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.
Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node.
This process doesn't wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.
====>>> Characteristic of Virtual IP in Oracle RAC: <====
Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities or commands and kept smaller.
But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable. There is utility also provided to configure virtual IP (vip) with RAC environment called VIPCA. Default path is $ORA_CRS_HOME/bin. During installation of Oracle RAC, it is executed.
==>>>>>>> Advantage of Virtual IP deployment in Oracle RAC: <<===
Using VIP configuration, client can be able to get connection fast even fail over of connection request to node. Because vip automatically assign to another surviving node faster and it can’t wait for TNS timeout old fashion.
===>>>>>>>Disadvantage of Virtual IP deploymenin Oracle RAC: <<==
Some more configurations is needed in system for assign virtual IP address to nodes like in /etc/hosts and others. Some misunderstanding or confusion may occur due to multiple IP assigns in same node.
===>><<<== Important for VIP configuration: <<<====
The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.
Sunday, September 26, 2010
Redo Concepts
Redo:
All changes to the database are recorded by redo. Redo includes all changes to datafiles, but does not include changes to control files or the parameter file.
Redo is initially written to online redo logs. The contents of a redo log file depend on a combination of Oracle version, operating system and server architecture. In general redo logs written on one architecture cannot be read on another. There are a few exceptions to this rule. For example, in Oracle 10.2 a redo log written in Linux can be read by a Windows database.
Redo Threads
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number. For single instance databases there is only one redo log thread at any time.
Redo Log Groups
A redo thread consists of two or more redo log groups.
Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide protection against media failure (mirroring). All members within a redo log group should be identical at any time.
Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.
Log Switches
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:
ALTER SYSTEM SWITCH LOGFILE;
When a log switch occurs, the sequence number is incremented and redo continues to be written to the next file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.
Redo Log Files
A redo log file consists of a number of fixed size blocks. The overall size of the redo log file is specified when the log group is created. For most platforms including Linux and Solaris the redo log block size is 512 bytes. On other platforms including HP/UX Itanium the redo log block size can be 1024 bytes.
Each redo log file has a fixed header. In recent versions of Oracle (8.0 and above) this header is two blocks. Therefore on Linux/Solaris the header is 1024 bytes. The second block of the header contains a standard Oracle file header which includes the following information:
* Database name
* Thread
* Compatibility Version
* Start Time
* End Time
* Start SCN
* End SCN
Other data is stored in the header. Note that the End SCN is actually the Start SCN of the next redo log file.
Redo Blocks
The body of the redo log file is used to store redo blocks. Each redo block has a 16 byte header (Oracle 9.2 and 10.2). The remainder of each redo block is used to store redo records.
Redo Records
Redo records are a logical structure. The upper size limit is probably 65536 bytes. Redo records can therefore span multiple physical redo blocks. A physical redo block can also contain multiple redo records.
Each redo record has a header. The VLD field in the redo record header specifies the type of the redo record. The size of the redo record header varies depending on the type.
In Oracle 9.2 the redo record header is normally 12 bytes, though they can occasionally increase in size to 28 bytes. In Oracle 10.2 the redo record header is normally 24 bytes, though under some circumstances they can increase to 68 bytes.
The following is an example of a redo record header from Oracle 10.2:
REDO RECORD - Thread:1 RBA: 0x000092.00000193.0088 LEN: 0x0050 VLD: 0x01
SCN: 0x0000.00181068 SUBSCN: 1 05/07/2009 21:53:48
The header includes the following fields
* Thread - redo log thread number
* RBA - redo byte address - address of redo record within redo log. Format is..
* LEN - length of redo record in bytes including header
* VLD - see below
* SCN - system change number of redo record
* SUBSCN: Unknown
* Timestamp e.g. 05/07/2009 21:53:48
The VLD field determines the size of the redo record header. Known values are shown in the following table. These values may vary from one release to another.
Mnemonic Value Description
KCRVOID 0 The contents are not valid
KCRVALID 1 Includes change vectors
KCRDEPND 2 Includes commit SCN
KCRVOID 4 Includes dependent SCN
KCRNMARK 8 New SCN mark record. SCN allocated exactly at this point in the redo log by this instance
KCROMARK 16 Old SCN mark record. SCN allocated at or before this point in the redo. May be allocated by another instance
KCRORDER 32 New SCN was allocated to ensure redo for some block would be ordered by inc/seq# when redo sorted by SCN
Change Vectors
A redo record consists of one or more change records known as change vectors. Each change vector consists of:
* change header
* list of element lengths
* list of elements
The size of the change header is 28 bytes in both Oracle 9.2 and 10.2.
The list of element lengths has a two byte header specifying the overall length of the element length list in bytes. The length of each element is stored in a two byte field. Finally if the structure does not align on a four byte boundary, a further two byte field is appended.
The list of elements consists of one or more elements aligned on a four byte boundary. Element sizes can range from four bytes to at least 32K.
If supplemental logging is enabled then for update operations (11.5), additional elements are appended to the change vector containing the primary key, unique key or column values of the row.
Operation Codes
Each change vector has an operation code. In Oracle 9.2 there were over 150 redo log operations; this number has grown significantly in Oracle 10.2 though the exact figure is not known. The operation code consists of a major number and a minor number.
The major number describes the level in the kernel where the redo is generated. The following table shows common levels:
Level Description
4 Block Cleanout
5 Transaction Layer (Undo)
10 Index Operation
11 Table Operation (DML)
13 Block Allocation
14 Extent Allocation
17 Backup Management
18 Online Backup
19 Direct Load
20 Transaction Metadata (LogMiner)
22 Space Management (ASSM)
23 Block Write (DBWR)
24 DDL Statement
For each level there is one or more subcode. Follow the hyperlinks for more details on individual operations:
* Level 4 - Block Cleanout
* Level 5 - Transaction Layer (Undo)
* Level 10 - Index Operation
* Level 11 - Table Operation (DML)
* Level 13 - Block Allocation
* Level 14 - Extent Allocation
* Level 17 - Backup Management
* Level 18 - Online Backup
* Level 19 - Direct Load
* Level 20 - Transaction Metadata (LogMiner)
* Level 22 - Space Management (ASSM)
* Level 23 - Block Write (DBWR)
* Level 24 - DDL Statement
Log File Dumps
Symbolic dumps can be created for both online redo logs and archived redo logs using the following syntax:
ALTER SYSTEM DUMP LOGFILE '';
For online redo logs the filename of the current redo log can be obtained using the following SQL:
SELECT member FROM v$logfile
WHERE group# =
(
SELECT group# FROM v$log
WHERE status = 'CURRENT'
);
*******************************************
All changes to the database are recorded by redo. Redo includes all changes to datafiles, but does not include changes to control files or the parameter file.
Redo is initially written to online redo logs. The contents of a redo log file depend on a combination of Oracle version, operating system and server architecture. In general redo logs written on one architecture cannot be read on another. There are a few exceptions to this rule. For example, in Oracle 10.2 a redo log written in Linux can be read by a Windows database.
Redo Threads
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number. For single instance databases there is only one redo log thread at any time.
Redo Log Groups
A redo thread consists of two or more redo log groups.
Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide protection against media failure (mirroring). All members within a redo log group should be identical at any time.
Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.
Log Switches
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:
ALTER SYSTEM SWITCH LOGFILE;
When a log switch occurs, the sequence number is incremented and redo continues to be written to the next file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.
Redo Log Files
A redo log file consists of a number of fixed size blocks. The overall size of the redo log file is specified when the log group is created. For most platforms including Linux and Solaris the redo log block size is 512 bytes. On other platforms including HP/UX Itanium the redo log block size can be 1024 bytes.
Each redo log file has a fixed header. In recent versions of Oracle (8.0 and above) this header is two blocks. Therefore on Linux/Solaris the header is 1024 bytes. The second block of the header contains a standard Oracle file header which includes the following information:
* Database name
* Thread
* Compatibility Version
* Start Time
* End Time
* Start SCN
* End SCN
Other data is stored in the header. Note that the End SCN is actually the Start SCN of the next redo log file.
Redo Blocks
The body of the redo log file is used to store redo blocks. Each redo block has a 16 byte header (Oracle 9.2 and 10.2). The remainder of each redo block is used to store redo records.
Redo Records
Redo records are a logical structure. The upper size limit is probably 65536 bytes. Redo records can therefore span multiple physical redo blocks. A physical redo block can also contain multiple redo records.
Each redo record has a header. The VLD field in the redo record header specifies the type of the redo record. The size of the redo record header varies depending on the type.
In Oracle 9.2 the redo record header is normally 12 bytes, though they can occasionally increase in size to 28 bytes. In Oracle 10.2 the redo record header is normally 24 bytes, though under some circumstances they can increase to 68 bytes.
The following is an example of a redo record header from Oracle 10.2:
REDO RECORD - Thread:1 RBA: 0x000092.00000193.0088 LEN: 0x0050 VLD: 0x01
SCN: 0x0000.00181068 SUBSCN: 1 05/07/2009 21:53:48
The header includes the following fields
* Thread - redo log thread number
* RBA - redo byte address - address of redo record within redo log. Format is
* LEN - length of redo record in bytes including header
* VLD - see below
* SCN - system change number of redo record
* SUBSCN: Unknown
* Timestamp e.g. 05/07/2009 21:53:48
The VLD field determines the size of the redo record header. Known values are shown in the following table. These values may vary from one release to another.
Mnemonic Value Description
KCRVOID 0 The contents are not valid
KCRVALID 1 Includes change vectors
KCRDEPND 2 Includes commit SCN
KCRVOID 4 Includes dependent SCN
KCRNMARK 8 New SCN mark record. SCN allocated exactly at this point in the redo log by this instance
KCROMARK 16 Old SCN mark record. SCN allocated at or before this point in the redo. May be allocated by another instance
KCRORDER 32 New SCN was allocated to ensure redo for some block would be ordered by inc/seq# when redo sorted by SCN
Change Vectors
A redo record consists of one or more change records known as change vectors. Each change vector consists of:
* change header
* list of element lengths
* list of elements
The size of the change header is 28 bytes in both Oracle 9.2 and 10.2.
The list of element lengths has a two byte header specifying the overall length of the element length list in bytes. The length of each element is stored in a two byte field. Finally if the structure does not align on a four byte boundary, a further two byte field is appended.
The list of elements consists of one or more elements aligned on a four byte boundary. Element sizes can range from four bytes to at least 32K.
If supplemental logging is enabled then for update operations (11.5), additional elements are appended to the change vector containing the primary key, unique key or column values of the row.
Operation Codes
Each change vector has an operation code. In Oracle 9.2 there were over 150 redo log operations; this number has grown significantly in Oracle 10.2 though the exact figure is not known. The operation code consists of a major number and a minor number.
The major number describes the level in the kernel where the redo is generated. The following table shows common levels:
Level Description
4 Block Cleanout
5 Transaction Layer (Undo)
10 Index Operation
11 Table Operation (DML)
13 Block Allocation
14 Extent Allocation
17 Backup Management
18 Online Backup
19 Direct Load
20 Transaction Metadata (LogMiner)
22 Space Management (ASSM)
23 Block Write (DBWR)
24 DDL Statement
For each level there is one or more subcode. Follow the hyperlinks for more details on individual operations:
* Level 4 - Block Cleanout
* Level 5 - Transaction Layer (Undo)
* Level 10 - Index Operation
* Level 11 - Table Operation (DML)
* Level 13 - Block Allocation
* Level 14 - Extent Allocation
* Level 17 - Backup Management
* Level 18 - Online Backup
* Level 19 - Direct Load
* Level 20 - Transaction Metadata (LogMiner)
* Level 22 - Space Management (ASSM)
* Level 23 - Block Write (DBWR)
* Level 24 - DDL Statement
Log File Dumps
Symbolic dumps can be created for both online redo logs and archived redo logs using the following syntax:
ALTER SYSTEM DUMP LOGFILE '
For online redo logs the filename of the current redo log can be obtained using the following SQL:
SELECT member FROM v$logfile
WHERE group# =
(
SELECT group# FROM v$log
WHERE status = 'CURRENT'
);
*******************************************
Oracle Streams Apply Process changes in 11GR2
I’ve setup a simple replication for table t1 from schema src to schema dst.
Changed Apply Server parallelism to 1 and did a simple test with inserting 100 rows while performing a sql trace:
SQL>desc src.t1;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
N NUMBER
V VARCHAR2(100) Y
SQL> select count(*) from src.t1;
COUNT(*)
----------
0
SQL> select count(*) from dst.t1;
COUNT(*)
----------
0
SQL> select sid from v$streams_apply_server;
SID
----------
22
SQL> exec dbms_monitor.session_trace_enable(22, waits => true);
PL/SQL procedure successfully completed
SQL> insert into src.t1
2 select level, to_char(level)
3 from dual
4 connect by level <= 100;
100 rows inserted SQL> commit;
Commit complete
SQL> select count(*) from dst.t1;
COUNT(*)
----------
100
SQL> exec dbms_monitor.session_trace_disable(22);
PL/SQL procedure successfully completed
******************
To my surprise, when I went to take a look at the trace file, I couldn’t find anything related to Apply process inserting rows into dst.t1, only a handful of internal housekeeping statements.
This made me curious as to where all the stuff really went to so I’ve decided to take a look at v$sql and see if there will be any clues:
******************
SQL> select sql_text
from v$sql
where lower(sql_text) like '%insert%dst%t1%';
SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from v$sql where lower(sql_text) like '%insert%dst%t1%'
************
The only thing I was able to see there was . . . my own statement. Things were starting to look serious.
In order to finally figure out who did what I’ve launched a logminer:
SQL> begin
dbms_logmnr.start_logmnr(
startTime => sysdate-30/1440,
endTime => sysdate,
Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+dbms_logmnr.CONTINUOUS_MINE
);
end;
/
PL/SQL procedure successfully completed
*********************
Let’s see who actually inserted all these rows:
SQL> select * from (
select session#, sql_redo
from v$logmnr_contents
where operation='INSERT'
and seg_owner='DST'
and table_name='T1'
order by timestamp desc
) where rownum <= 5;
SESSION# SQL_REDO
---------- --------------------------------------------------------------------------------
22 insert into "DST"."T1"("N","V") values ('1','1');
22 insert into "DST"."T1"("N","V") values ('5','5');
22 insert into "DST"."T1"("N","V") values ('4','4');
22 insert into "DST"."T1"("N","V") values ('3','3');
22 insert into "DST"."T1"("N","V") values ('2','2');
********
Session with SID 22 is nothing else but our Apply Server . . .
The next step was to try and figure out whether we’re really dealing with some new codepath responsible for such spectacular performance (apparently, due to the complete lack of instrumentation :) or this is just another weird Oracle bug.
I’ve blocked the Apply Server on updating a row and looked at the Apply Server’s stack:
************
01 [oracle@ora11gr2 trace]$ pstack 17036
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x0000000001f57c14 in kddiruUpdate ()
15 #13 0x000000000179eeab in knasdaExecDML ()
16 #14 0x000000000179d928 in knasdaProcDML ()
17 #15 0x000000000178c6fd in knaspd ()
18 #16 0x0000000001787d2f in knasplcr ()
19 #17 0x00000000017866d7 in knaspx ()
20 #18 0x0000000001770fd5 in knalsProc1Txn ()
21 #19 0x000000000177022d in knalsptxn ()
22 #20 0x00000000017424a6 in knasm2 ()
23 #21 0x0000000001776d8d in knalsma ()
24 #22 0x0000000000c25a7d in knlkcbkma ()
25 #23 0x0000000000b93ba7 in ksvrdp ()
26 #24 0x00000000020d2dd7 in opirip ()
27 #25 0x00000000016fe729 in opidrv ()
28 #26 0x0000000001b7183f in sou2o ()
29 #27 0x00000000009d3f8a in opimai_real ()
30 #28 0x0000000001b76ace in ssthrdmain ()
31 #29 0x00000000009d3e71 in main ()
Before we move on, here is a stack dump from a blocked Apply Server in 10.2.0.4:
view source
print?
01 [oracle@ora10gr2 trace]$ pstack 23787
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x00000000084588c9 in updrow ()
15 #13 0x00000000084f2580 in qerupFetch ()
16 #14 0x0000000008453cdd in updaul ()
17 #15 0x0000000008451bca in updThreePhaseExe ()
18 #16 0x00000000084509f5 in updexe ()
19 #17 0x00000000083fe18f in opiexe ()
20 #18 0x00000000083f5c0d in opiall0 ()
21 #19 0x0000000008403d25 in opikpr ()
22 #20 0x00000000083f78b9 in opiodr ()
23 #21 0x00000000084892af in __PGOSF141_rpidrus ()
24 #22 0x00000000085ee820 in skgmstack ()
25 #23 0x000000000848a759 in rpiswu2 ()
26 #24 0x000000000848fdf4 in kprball ()
27 #25 0x0000000001c7c4d7 in knipxup ()
28 #26 0x0000000001c72651 in knipdis ()
29 #27 0x000000000178cacc in knaspd ()
30 #28 0x0000000001787d2f in knasplcr ()
31 #29 0x00000000017866d7 in knaspx ()
32 #30 0x0000000001770fd5 in knalsProc1Txn ()
33 #31 0x000000000177022d in knalsptxn ()
34 #32 0x00000000017424a6 in knasm2 ()
35 #33 0x0000000001776d8d in knalsma ()
36 #34 0x0000000000c25a7d in knlkcbkma ()
37 #35 0x0000000000b93ba7 in ksvrdp ()
38 #36 0x00000000020d2dd7 in opirip ()
39 #37 0x00000000016fe729 in opidrv ()
40 #38 0x0000000001b7183f in sou2o ()
41 #39 0x00000000009d3f8a in opimai_real ()
42 #40 0x0000000001b76ace in ssthrdmain ()
43 #41 0x00000000009d3e71 in main ()
The stack is only 30 functions deep in 11.2.0.1 compared to 42 in 10.2.0.4! Given that whatever goes up the stack from ktcwit1 () function is due to both sessions waiting on the enqueue, the relative codepath change is even bigger.
All the difference comes from the one key thing: a recursive call. If you take a look at line #25 (highlighted), you’ll notice rpiswu2 () function (for these of you unfamiliar with Oracle Kernel Layers, RPI stands for Recursive Program Interface).
Whatever happens further up the stack is essentially the same codepath any user session would use while executing an UPDATE statement. The Apply Servers in 10.2.0.4 generally behave like any other user session would and whatever diagnostic techniques you have learned while troubleshooting user issues could be, to a large extent, applied to the Apply Servers as well.
Every LCR execution leads to at least one recursive call (so if you got, say, a transaction with 1000 LCRs that would be at least 1000 recursive calls by the Apply Server). In 11.2.0.1 the recursive call is missing and the codepath is different up to kauupd () (KA, Access Layer) function.
Indeed, by looking at the Apply Server statistics in 11.2.0.1 you will notice that executing an LCR no longer results in a recursive call so the entire change seems to be around a shortcut which allows the Apply Server to proceed directly into KD (Data) layer, bypass the “regular” codepath and avoid a recursive call.
On a side note it appears the this new codepath was first introduced in 11.1.0.7.
**************************************
What’s up with the instrumentation?
While performance improvement is certainly most welcome, there is a big downside—all these new functions seems to be poorly, or not-at-all, instrumented. This makes it hard to evaluate the gains, as some stuff is simply not there.
How are you supposed to figure out what’s going on then? The good news is that all regular dynamic performance views (like v$session_wait, v$session_event, etc.) seems to be populated correctly, but sql trace took a big hit (plus you can no longer see sql_id in v$session). Whatever falls out of the “old” stuff looks
like a black box . . . pretty much.
Puzzled by this problem, I’ve tried to see whether there is any easy way to enable the old codepath so you can get all the instrumentation facilities back in place. After some trial and error, it turned out that a simple row level trigger . . .
view source
print?
01 SQL> create or replace trigger dst.buid_t1 before delete or insert or update on dst.t1
02 2 for each row
03 3 begin
04 4 null;
05 5 end;
06 6 /
07
08 Trigger created
09
10 SQL> begin
11 2 dbms_ddl.set_trigger_firing_property('DST', 'BUID_T1', false);
12 3 end;
13 4 /
14
15 PL/SQL procedure successfully completed
. . . is enough to get the old codepath back. Here is the stack of Apply Server process in 11.2.0.1 with such a trigger in place:
view source
print?
01 [oracle@ora11gr2 trace]$ pstack 30640
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x00000000084588c9 in updrow ()
15 #13 0x00000000084f2580 in qerupFetch ()
16 #14 0x00000000046a363f in qerstFetch ()
17 #15 0x0000000008453cdd in updaul ()
18 #16 0x0000000008451bca in updThreePhaseExe ()
19 #17 0x00000000084509f5 in updexe ()
20 #18 0x00000000083fe18f in opiexe ()
21 #19 0x00000000083f5c0d in opiall0 ()
22 #20 0x0000000008403d25 in opikpr ()
23 #21 0x00000000083f78b9 in opiodr ()
24 #22 0x00000000084892af in __PGOSF141_rpidrus ()
25 #23 0x00000000085ee820 in skgmstack ()
26 #24 0x000000000848a759 in rpiswu2 ()
27 #25 0x000000000848fdf4 in kprball ()
28 #26 0x0000000001c7c4d7 in knipxup ()
29 #27 0x0000000001c72651 in knipdis ()
30 #28 0x000000000178cacc in knaspd ()
31 #29 0x0000000001787d2f in knasplcr ()
32 #30 0x00000000017866d7 in knaspx ()
33 #31 0x0000000001770fd5 in knalsProc1Txn ()
34 #32 0x000000000177022d in knalsptxn ()
35 #33 0x00000000017424a6 in knasm2 ()
36 #34 0x0000000001776d8d in knalsma ()
37 #35 0x0000000000c25a7d in knlkcbkma ()
38 #36 0x0000000000b93ba7 in ksvrdp ()
39 #37 0x00000000020d2dd7 in opirip ()
40 #38 0x00000000016fe729 in opidrv ()
41 #39 0x0000000001b7183f in sou2o ()
42 #40 0x00000000009d3f8a in opimai_real ()
43 #41 0x0000000001b76ace in ssthrdmain ()
44 #42 0x00000000009d3e71 in main ()
Now that looks much more familiar! All the instrumentation appeared to be back in place as well.
I’ve also discovered that:
1. DELETE seems to be always handled through the old codepath.
2. In case you have a unique constraint or a primary key supported by a non-unique index, INSERT will fall back to the old codepath.
3. UPDATE needs a primary key or a key column(-s) supported by an index in order to use the new codepath.
It remains to be seen whether this new codepath has been implemented as a shortcut for most frequently used scenarios, or whether there are some implementation restrictions as it progresses with the future releases . . . or maybe not, due to Golden Gate taking over as a strategic direction.
Changed Apply Server parallelism to 1 and did a simple test with inserting 100 rows while performing a sql trace:
SQL>desc src.t1;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
N NUMBER
V VARCHAR2(100) Y
SQL> select count(*) from src.t1;
COUNT(*)
----------
0
SQL> select count(*) from dst.t1;
COUNT(*)
----------
0
SQL> select sid from v$streams_apply_server;
SID
----------
22
SQL> exec dbms_monitor.session_trace_enable(22, waits => true);
PL/SQL procedure successfully completed
SQL> insert into src.t1
2 select level, to_char(level)
3 from dual
4 connect by level <= 100;
100 rows inserted SQL> commit;
Commit complete
SQL> select count(*) from dst.t1;
COUNT(*)
----------
100
SQL> exec dbms_monitor.session_trace_disable(22);
PL/SQL procedure successfully completed
******************
To my surprise, when I went to take a look at the trace file, I couldn’t find anything related to Apply process inserting rows into dst.t1, only a handful of internal housekeeping statements.
This made me curious as to where all the stuff really went to so I’ve decided to take a look at v$sql and see if there will be any clues:
******************
SQL> select sql_text
from v$sql
where lower(sql_text) like '%insert%dst%t1%';
SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from v$sql where lower(sql_text) like '%insert%dst%t1%'
************
The only thing I was able to see there was . . . my own statement. Things were starting to look serious.
In order to finally figure out who did what I’ve launched a logminer:
SQL> begin
dbms_logmnr.start_logmnr(
startTime => sysdate-30/1440,
endTime => sysdate,
Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+dbms_logmnr.CONTINUOUS_MINE
);
end;
/
PL/SQL procedure successfully completed
*********************
Let’s see who actually inserted all these rows:
SQL> select * from (
select session#, sql_redo
from v$logmnr_contents
where operation='INSERT'
and seg_owner='DST'
and table_name='T1'
order by timestamp desc
) where rownum <= 5;
SESSION# SQL_REDO
---------- --------------------------------------------------------------------------------
22 insert into "DST"."T1"("N","V") values ('1','1');
22 insert into "DST"."T1"("N","V") values ('5','5');
22 insert into "DST"."T1"("N","V") values ('4','4');
22 insert into "DST"."T1"("N","V") values ('3','3');
22 insert into "DST"."T1"("N","V") values ('2','2');
********
Session with SID 22 is nothing else but our Apply Server . . .
The next step was to try and figure out whether we’re really dealing with some new codepath responsible for such spectacular performance (apparently, due to the complete lack of instrumentation :) or this is just another weird Oracle bug.
I’ve blocked the Apply Server on updating a row and looked at the Apply Server’s stack:
************
01 [oracle@ora11gr2 trace]$ pstack 17036
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x0000000001f57c14 in kddiruUpdate ()
15 #13 0x000000000179eeab in knasdaExecDML ()
16 #14 0x000000000179d928 in knasdaProcDML ()
17 #15 0x000000000178c6fd in knaspd ()
18 #16 0x0000000001787d2f in knasplcr ()
19 #17 0x00000000017866d7 in knaspx ()
20 #18 0x0000000001770fd5 in knalsProc1Txn ()
21 #19 0x000000000177022d in knalsptxn ()
22 #20 0x00000000017424a6 in knasm2 ()
23 #21 0x0000000001776d8d in knalsma ()
24 #22 0x0000000000c25a7d in knlkcbkma ()
25 #23 0x0000000000b93ba7 in ksvrdp ()
26 #24 0x00000000020d2dd7 in opirip ()
27 #25 0x00000000016fe729 in opidrv ()
28 #26 0x0000000001b7183f in sou2o ()
29 #27 0x00000000009d3f8a in opimai_real ()
30 #28 0x0000000001b76ace in ssthrdmain ()
31 #29 0x00000000009d3e71 in main ()
Before we move on, here is a stack dump from a blocked Apply Server in 10.2.0.4:
view source
print?
01 [oracle@ora10gr2 trace]$ pstack 23787
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x00000000084588c9 in updrow ()
15 #13 0x00000000084f2580 in qerupFetch ()
16 #14 0x0000000008453cdd in updaul ()
17 #15 0x0000000008451bca in updThreePhaseExe ()
18 #16 0x00000000084509f5 in updexe ()
19 #17 0x00000000083fe18f in opiexe ()
20 #18 0x00000000083f5c0d in opiall0 ()
21 #19 0x0000000008403d25 in opikpr ()
22 #20 0x00000000083f78b9 in opiodr ()
23 #21 0x00000000084892af in __PGOSF141_rpidrus ()
24 #22 0x00000000085ee820 in skgmstack ()
25 #23 0x000000000848a759 in rpiswu2 ()
26 #24 0x000000000848fdf4 in kprball ()
27 #25 0x0000000001c7c4d7 in knipxup ()
28 #26 0x0000000001c72651 in knipdis ()
29 #27 0x000000000178cacc in knaspd ()
30 #28 0x0000000001787d2f in knasplcr ()
31 #29 0x00000000017866d7 in knaspx ()
32 #30 0x0000000001770fd5 in knalsProc1Txn ()
33 #31 0x000000000177022d in knalsptxn ()
34 #32 0x00000000017424a6 in knasm2 ()
35 #33 0x0000000001776d8d in knalsma ()
36 #34 0x0000000000c25a7d in knlkcbkma ()
37 #35 0x0000000000b93ba7 in ksvrdp ()
38 #36 0x00000000020d2dd7 in opirip ()
39 #37 0x00000000016fe729 in opidrv ()
40 #38 0x0000000001b7183f in sou2o ()
41 #39 0x00000000009d3f8a in opimai_real ()
42 #40 0x0000000001b76ace in ssthrdmain ()
43 #41 0x00000000009d3e71 in main ()
The stack is only 30 functions deep in 11.2.0.1 compared to 42 in 10.2.0.4! Given that whatever goes up the stack from ktcwit1 () function is due to both sessions waiting on the enqueue, the relative codepath change is even bigger.
All the difference comes from the one key thing: a recursive call. If you take a look at line #25 (highlighted), you’ll notice rpiswu2 () function (for these of you unfamiliar with Oracle Kernel Layers, RPI stands for Recursive Program Interface).
Whatever happens further up the stack is essentially the same codepath any user session would use while executing an UPDATE statement. The Apply Servers in 10.2.0.4 generally behave like any other user session would and whatever diagnostic techniques you have learned while troubleshooting user issues could be, to a large extent, applied to the Apply Servers as well.
Every LCR execution leads to at least one recursive call (so if you got, say, a transaction with 1000 LCRs that would be at least 1000 recursive calls by the Apply Server). In 11.2.0.1 the recursive call is missing and the codepath is different up to kauupd () (KA, Access Layer) function.
Indeed, by looking at the Apply Server statistics in 11.2.0.1 you will notice that executing an LCR no longer results in a recursive call so the entire change seems to be around a shortcut which allows the Apply Server to proceed directly into KD (Data) layer, bypass the “regular” codepath and avoid a recursive call.
On a side note it appears the this new codepath was first introduced in 11.1.0.7.
**************************************
What’s up with the instrumentation?
While performance improvement is certainly most welcome, there is a big downside—all these new functions seems to be poorly, or not-at-all, instrumented. This makes it hard to evaluate the gains, as some stuff is simply not there.
How are you supposed to figure out what’s going on then? The good news is that all regular dynamic performance views (like v$session_wait, v$session_event, etc.) seems to be populated correctly, but sql trace took a big hit (plus you can no longer see sql_id in v$session). Whatever falls out of the “old” stuff looks
like a black box . . . pretty much.
Puzzled by this problem, I’ve tried to see whether there is any easy way to enable the old codepath so you can get all the instrumentation facilities back in place. After some trial and error, it turned out that a simple row level trigger . . .
view source
print?
01 SQL> create or replace trigger dst.buid_t1 before delete or insert or update on dst.t1
02 2 for each row
03 3 begin
04 4 null;
05 5 end;
06 6 /
07
08 Trigger created
09
10 SQL> begin
11 2 dbms_ddl.set_trigger_firing_property('DST', 'BUID_T1', false);
12 3 end;
13 4 /
14
15 PL/SQL procedure successfully completed
. . . is enough to get the old codepath back. Here is the stack of Apply Server process in 11.2.0.1 with such a trigger in place:
view source
print?
01 [oracle@ora11gr2 trace]$ pstack 30640
02 #0 0x0000003b83cd450a in semtimedop () from /lib64/libc.so.6
03 #1 0x00000000085ef3f3 in sskgpwwait ()
04 #2 0x00000000085ee5c6 in skgpwwait ()
05 #3 0x000000000829ee31 in ksliwat ()
06 #4 0x000000000829e422 in kslwaitctx ()
07 #5 0x0000000000af92f5 in ksqcmi ()
08 #6 0x00000000082ac019 in ksqgtlctx ()
09 #7 0x00000000082aa77a in ksqgelctx ()
10 #8 0x0000000000c4d566 in ktcwit1 ()
11 #9 0x00000000082d5d99 in kdddgb ()
12 #10 0x00000000082c7530 in kdusru ()
13 #11 0x00000000082c0902 in kauupd ()
14 #12 0x00000000084588c9 in updrow ()
15 #13 0x00000000084f2580 in qerupFetch ()
16 #14 0x00000000046a363f in qerstFetch ()
17 #15 0x0000000008453cdd in updaul ()
18 #16 0x0000000008451bca in updThreePhaseExe ()
19 #17 0x00000000084509f5 in updexe ()
20 #18 0x00000000083fe18f in opiexe ()
21 #19 0x00000000083f5c0d in opiall0 ()
22 #20 0x0000000008403d25 in opikpr ()
23 #21 0x00000000083f78b9 in opiodr ()
24 #22 0x00000000084892af in __PGOSF141_rpidrus ()
25 #23 0x00000000085ee820 in skgmstack ()
26 #24 0x000000000848a759 in rpiswu2 ()
27 #25 0x000000000848fdf4 in kprball ()
28 #26 0x0000000001c7c4d7 in knipxup ()
29 #27 0x0000000001c72651 in knipdis ()
30 #28 0x000000000178cacc in knaspd ()
31 #29 0x0000000001787d2f in knasplcr ()
32 #30 0x00000000017866d7 in knaspx ()
33 #31 0x0000000001770fd5 in knalsProc1Txn ()
34 #32 0x000000000177022d in knalsptxn ()
35 #33 0x00000000017424a6 in knasm2 ()
36 #34 0x0000000001776d8d in knalsma ()
37 #35 0x0000000000c25a7d in knlkcbkma ()
38 #36 0x0000000000b93ba7 in ksvrdp ()
39 #37 0x00000000020d2dd7 in opirip ()
40 #38 0x00000000016fe729 in opidrv ()
41 #39 0x0000000001b7183f in sou2o ()
42 #40 0x00000000009d3f8a in opimai_real ()
43 #41 0x0000000001b76ace in ssthrdmain ()
44 #42 0x00000000009d3e71 in main ()
Now that looks much more familiar! All the instrumentation appeared to be back in place as well.
I’ve also discovered that:
1. DELETE seems to be always handled through the old codepath.
2. In case you have a unique constraint or a primary key supported by a non-unique index, INSERT will fall back to the old codepath.
3. UPDATE needs a primary key or a key column(-s) supported by an index in order to use the new codepath.
It remains to be seen whether this new codepath has been implemented as a shortcut for most frequently used scenarios, or whether there are some implementation restrictions as it progresses with the future releases . . . or maybe not, due to Golden Gate taking over as a strategic direction.
Sunday, September 19, 2010
Removing a Node from a 10gR1 RAC Cluster
Removing a Node from a 10gR1 RAC Cluster [ID 269320.1]
Modified 27-MAY-2009 Type BULLETIN Status PUBLISHED
Note: This article is only relevant for 10gR1 RAC environments.
For 10gR2 RAC environments please follow the documented procedures in the manual:
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration
and Deployment Guide
10g Release 2 (10.2)
Part Number B14197-03
PURPOSE
-------
The purpose of this note is to provide the user with a document that
can be used as a guide to remove a cluster node from an Oracle 10g Real
Applications environment.
SCOPE & APPLICATION
-------------------
This document can be used by DBAs and support analsyts who need to
either remove a cluster node or assist another in removing a cluster
node in a 10g Unix Real Applications environment.
REMOVING A NODE FROM A 10g RAC CLUSTER
--------------------------------------
If you have to remove a node from a RAC 10g database, even if the node
will no longer be available to the environment, there is a certain
amount of cleanup that needs to be done. The remaining nodes need to
be informed of the change of status of the departing node. If there are
any steps that must be run on the node being removed and the node is no
longer available those commands can be skipped.
The most important 3 steps that need to be followed are;
A. Remove the instance using DBCA.
B. Remove the node from the cluster.
C. Reconfigure the OS and remaining hardware.
Here is a breakdown of the above steps.
A. Remove the instance using DBCA.
--------------------------------------
1. Verify that you have a good backup of the OCR (Oracle Configuration
Repository) using ocrconfig -showbackup.
2. Run DBCA from one of the nodes you are going to keep. Leave the
database up and also leave the departing instance up and running.
3. Choose "Instance Management"
4. Choose "Delete an instance"
5. On the next screen, select the cluster database from which you
will delete an instance. Supply the system privilege username
and password.
6. On the next screen, a list of cluster database instances will
appear. Highlight the instance you would like to delete then
click next.
7. If you have services configured, reassign the services. Modify the
services so that each service can run on one of the remaining
instances. Set "not used" for each service regarding the instance
that is to be deleted. Click Finish.
8. If your database is in archive log mode you may encounter the
following errors:
ORA-350
ORA-312
This may occur because the DBCA cannot drop the current log, as
it needs archiving. This issue is fixed in the 10.1.0.3
patchset. But previous to this patchset you should click the
ignore button and when the DBCA completes, manually archive
the logs for the deleted instance and dropt the log group.
SQL> alter system archive log all;
SQL> alter database drop logfile group 2;
9. Verify that the dropped instance's redo thread has been removed by
querying v$log. If for any reason the redo thread is not disabled
then disable the thread.
SQL> alter database disable thread 2;
10. Verify that the instance was removed from the OCR (Oracle
Configuration Repository) with the following commands:
srvctl config database -d
cd/bin
./crs_stat
11. If this node had an ASM instance and the node will no longer be a
part of the cluster you will now need to remove the ASM instance with:
srvctl stop asm -n
srvctl remove asm -n
Verify that asm is removed with:
srvctl config asm -n
B. Remove the Node from the Cluster
----------------------------------------
Once the instance has been deleted. The process of removing the node
from the cluster is a manual process. This is accomplished by running
scripts on the deleted node to remove the CRS install, as well as
scripts on the remaining nodes to update the node list. The following
steps assume that the node to be removed is still functioning.
1. To delete node number 2 first stop and remove the nodeapps on the
node you are removing. Assuming that you have removed the ASM
instance as the root user on a remaining node;
# srvctl stop nodeapps -n
2. Run netca. Choose "Cluster Configuration".
3. Only select the node you are removing and click next.
4. Choose "Listener Configuration" and click next.
5. Choose "Delete" and delete any listeners configured on the node
you are removing.
6. Run/bin/crs_stat. Make sure that all database
resources are running on nodes that are going to be kept. For
example:
NAME=ora..db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on
Ensure that this resource is not running on a node that will be
removed. Use/bin/crs_relocate to perform this.
Example:
crs_relocate ora..db
7. As the root user, remove the nodeapps on the node you are removing.
# srvctl remove nodeapps -n
8. Next as the Oracle user run the installer with the
updateNodeList option on any remaining node in the cluster.
a. DISPLAY=ipaddress:0.0; export DISPLAY
This should be set even though the gui does not run.
b. $ORACLE_HOME/oui/bin/runInstaller -updateNodeList
ORACLE_HOME= CLUSTER_NODES=,
,
With this command we are defining the RDBMS $ORACLE_HOME's that
now are part of the cluster in the Oracle inventory. If there is
no $ORACLE_HOME this step can be skipped.
9. Change to the root user to finish the removal on a node that
is being removed. This command will stop the CRS stack
and delete the ocr.loc file on the node to be removed. The
nosharedvar option assumes the ocr.loc file is not on a shared
file sytem. If it does exist on a shared file system then
specify sharedvar instead. The nosharedhome option specifies
that the CRS_HOME is on a local filesystem. If the CRS_HOME is
on a shared file system, specify sharedhome instead.
Run the rootdelete.sh script from/install. Example:
# cd/install
# ./rootdelete.sh local nosharedvar nosharedhome
10. On a node that will be kept, the root user should run the
rootdeletenode.sh script from the/install directory.
When running this script from the CRS home specify both the node
name and the node number. The node name and the node number are
visiable in olsnodes -n. Also do NOT put a space after the
comma between the two.
# olsnodes -n
1
2
# cd/install
# ./rootdeletenode.sh,2
11. Confirm success by running OLSNODES.
/bin>: ./olsnodes -n
1
12. Now switch back to the oracle user account and run the same
runInstaller command as before. Run it this time from the
instead of the ORACLE_HOME. Specify all of the
remaining nodes.
a. DISPLAY=ipaddress:0.0; export DISPLAY
b./oui/bin/runInstaller -updateNodeList
ORACLE_HOME= CLUSTER_NODES=,
, CRS=TRUE
With this command we are defining the CRS HOME's that now are
part of the cluster in the Oracle inventory.
13. Once the node updates are done you will need to manually delete
the $ORACLE_HOME and $CRS_HOME from the node to be expunged,
unless, of course, either of these is on a shared file system
that is still being used.
a. $ORACLE_HOME>: rm -rf *
b. $CRS_HOME> : rm -rf * (as root)
14. Next, as root, from the deleted node, verify that all init scripts
and soft links are removed:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
Linux:
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
16. You can also remove the /etc/oracle directory, the
/etc/oratab file, and the Oracle inventory (if desired)
17. To remove an ADDITIONAL ORACLE_HOME, ASM_HOME, or EM_HOME from the
inventory on all remaining nodes, run the installer to update the
node list. Example (if removing node 2):
runInstaller -updateNodeList -local \
ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=node1,node3,node4
(If you are using private home installations, please ignore the "-local" flag.)
RELATED DOCUMENTS
-----------------
Oracle® Real Application Clusters Administrator's Guide 10g Release 1 (10.1)
Part Number B10765-02
Chapter 5
Oracle Series/Oracle Database 10g High Availabilty Chapter 5 28-34.
Note 239998.1
Oracle Clusterware and RAC Admin and Deployment Guide - Ch. 10 and 11
Executed steps:
Node 3: lxdb0109c03 deleting.
1 >>
1>> srvctl stop nodeapps -n lxdb0109c03 Run for node 1
1>>srvctl remove nodeapps -n lxdb0109c03 run for node 1
3>> ./rootdelete.sh local nosharedvar nosharedhome run for deleting node (node 3 is going to delete)
1>> ./rootdeletenode.sh lxdb0109c03,3 run for node 1
/u01/app/oracle/product/10.2.0/crs/oui/bin/runInstaller -updateNodeList
ORACLE_HOME=/u01/app/oracle/product/10.2.0/crs CLUSTER_NODES=lxdb0109c01,lxdb0109c02 CRS=TRUE run from
node1
./racgons add_config lxdb0109c03:6201
orapwd file=orapwDBFAP3 password=Alr3_ady entries=10
ln -s /u03/oradata/DBFAP/spfile/spfileDBFAP.ora spfileDBFAP3.ora
Modified 27-MAY-2009 Type BULLETIN Status PUBLISHED
Note: This article is only relevant for 10gR1 RAC environments.
For 10gR2 RAC environments please follow the documented procedures in the manual:
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration
and Deployment Guide
10g Release 2 (10.2)
Part Number B14197-03
PURPOSE
-------
The purpose of this note is to provide the user with a document that
can be used as a guide to remove a cluster node from an Oracle 10g Real
Applications environment.
SCOPE & APPLICATION
-------------------
This document can be used by DBAs and support analsyts who need to
either remove a cluster node or assist another in removing a cluster
node in a 10g Unix Real Applications environment.
REMOVING A NODE FROM A 10g RAC CLUSTER
--------------------------------------
If you have to remove a node from a RAC 10g database, even if the node
will no longer be available to the environment, there is a certain
amount of cleanup that needs to be done. The remaining nodes need to
be informed of the change of status of the departing node. If there are
any steps that must be run on the node being removed and the node is no
longer available those commands can be skipped.
The most important 3 steps that need to be followed are;
A. Remove the instance using DBCA.
B. Remove the node from the cluster.
C. Reconfigure the OS and remaining hardware.
Here is a breakdown of the above steps.
A. Remove the instance using DBCA.
--------------------------------------
1. Verify that you have a good backup of the OCR (Oracle Configuration
Repository) using ocrconfig -showbackup.
2. Run DBCA from one of the nodes you are going to keep. Leave the
database up and also leave the departing instance up and running.
3. Choose "Instance Management"
4. Choose "Delete an instance"
5. On the next screen, select the cluster database from which you
will delete an instance. Supply the system privilege username
and password.
6. On the next screen, a list of cluster database instances will
appear. Highlight the instance you would like to delete then
click next.
7. If you have services configured, reassign the services. Modify the
services so that each service can run on one of the remaining
instances. Set "not used" for each service regarding the instance
that is to be deleted. Click Finish.
8. If your database is in archive log mode you may encounter the
following errors:
ORA-350
ORA-312
This may occur because the DBCA cannot drop the current log, as
it needs archiving. This issue is fixed in the 10.1.0.3
patchset. But previous to this patchset you should click the
ignore button and when the DBCA completes, manually archive
the logs for the deleted instance and dropt the log group.
SQL> alter system archive log all;
SQL> alter database drop logfile group 2;
9. Verify that the dropped instance's redo thread has been removed by
querying v$log. If for any reason the redo thread is not disabled
then disable the thread.
SQL> alter database disable thread 2;
10. Verify that the instance was removed from the OCR (Oracle
Configuration Repository) with the following commands:
srvctl config database -d
cd
./crs_stat
11. If this node had an ASM instance and the node will no longer be a
part of the cluster you will now need to remove the ASM instance with:
srvctl stop asm -n
srvctl remove asm -n
Verify that asm is removed with:
srvctl config asm -n
B. Remove the Node from the Cluster
----------------------------------------
Once the instance has been deleted. The process of removing the node
from the cluster is a manual process. This is accomplished by running
scripts on the deleted node to remove the CRS install, as well as
scripts on the remaining nodes to update the node list. The following
steps assume that the node to be removed is still functioning.
1. To delete node number 2 first stop and remove the nodeapps on the
node you are removing. Assuming that you have removed the ASM
instance as the root user on a remaining node;
# srvctl stop nodeapps -n
2. Run netca. Choose "Cluster Configuration".
3. Only select the node you are removing and click next.
4. Choose "Listener Configuration" and click next.
5. Choose "Delete" and delete any listeners configured on the node
you are removing.
6. Run
resources are running on nodes that are going to be kept. For
example:
NAME=ora.
TYPE=application
TARGET=ONLINE
STATE=ONLINE on
Ensure that this resource is not running on a node that will be
removed. Use
Example:
crs_relocate ora.
7. As the root user, remove the nodeapps on the node you are removing.
# srvctl remove nodeapps -n
8. Next as the Oracle user run the installer with the
updateNodeList option on any remaining node in the cluster.
a. DISPLAY=ipaddress:0.0; export DISPLAY
This should be set even though the gui does not run.
b. $ORACLE_HOME/oui/bin/runInstaller -updateNodeList
ORACLE_HOME=
With this command we are defining the RDBMS $ORACLE_HOME's that
now are part of the cluster in the Oracle inventory. If there is
no $ORACLE_HOME this step can be skipped.
9. Change to the root user to finish the removal on a node that
is being removed. This command will stop the CRS stack
and delete the ocr.loc file on the node to be removed. The
nosharedvar option assumes the ocr.loc file is not on a shared
file sytem. If it does exist on a shared file system then
specify sharedvar instead. The nosharedhome option specifies
that the CRS_HOME is on a local filesystem. If the CRS_HOME is
on a shared file system, specify sharedhome instead.
Run the rootdelete.sh script from
# cd
# ./rootdelete.sh local nosharedvar nosharedhome
10. On a node that will be kept, the root user should run the
rootdeletenode.sh script from the
When running this script from the CRS home specify both the node
name and the node number. The node name and the node number are
visiable in olsnodes -n. Also do NOT put a space after the
comma between the two.
# olsnodes -n
# cd
# ./rootdeletenode.sh
11. Confirm success by running OLSNODES.
12. Now switch back to the oracle user account and run the same
runInstaller command as before. Run it this time from the
remaining nodes.
a. DISPLAY=ipaddress:0.0; export DISPLAY
b.
ORACLE_HOME=
With this command we are defining the CRS HOME's that now are
part of the cluster in the Oracle inventory.
13. Once the node updates are done you will need to manually delete
the $ORACLE_HOME and $CRS_HOME from the node to be expunged,
unless, of course, either of these is on a shared file system
that is still being used.
a. $ORACLE_HOME>: rm -rf *
b. $CRS_HOME> : rm -rf * (as root)
14. Next, as root, from the deleted node, verify that all init scripts
and soft links are removed:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
Linux:
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
16. You can also remove the /etc/oracle directory, the
/etc/oratab file, and the Oracle inventory (if desired)
17. To remove an ADDITIONAL ORACLE_HOME, ASM_HOME, or EM_HOME from the
inventory on all remaining nodes, run the installer to update the
node list. Example (if removing node 2):
runInstaller -updateNodeList -local \
ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=node1,node3,node4
(If you are using private home installations, please ignore the "-local" flag.)
RELATED DOCUMENTS
-----------------
Oracle® Real Application Clusters Administrator's Guide 10g Release 1 (10.1)
Part Number B10765-02
Chapter 5
Oracle Series/Oracle Database 10g High Availabilty Chapter 5 28-34.
Note 239998.1
Oracle Clusterware and RAC Admin and Deployment Guide - Ch. 10 and 11
Executed steps:
Node 3: lxdb0109c03 deleting.
1 >>
1>> srvctl stop nodeapps -n lxdb0109c03 Run for node 1
1>>srvctl remove nodeapps -n lxdb0109c03 run for node 1
3>> ./rootdelete.sh local nosharedvar nosharedhome run for deleting node (node 3 is going to delete)
1>> ./rootdeletenode.sh lxdb0109c03,3 run for node 1
/u01/app/oracle/product/10.2.0/crs/oui/bin/runInstaller -updateNodeList
ORACLE_HOME=/u01/app/oracle/product/10.2.0/crs CLUSTER_NODES=lxdb0109c01,lxdb0109c02 CRS=TRUE run from
node1
./racgons add_config lxdb0109c03:6201
orapwd file=orapwDBFAP3 password=Alr3_ady entries=10
ln -s /u03/oradata/DBFAP/spfile/spfileDBFAP.ora spfileDBFAP3.ora
Subscribe to:
Posts (Atom)