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
Saturday, December 11, 2010
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'
);
*******************************************
Subscribe to:
Posts (Atom)