August 17, 2009
1. Use dbv to verify a datafile.
[oracle@sinh1 sindb1]$ dbv file=/u01/oracle/oradata/sindb1/system01.dbf feedback=1000
DBVERIFY: Release 11.1.0.6.0 - Production on Mon Aug 17 09:32:58 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/sindb1/system01.dbf
....................................................
DBVERIFY - Verification complete
Total Pages Examined : 51200
Total Pages Processed (Data) : 13494
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 3992
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2380
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 31334
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 251706 (0.251706)
Note: If you don't give a full path name of the file to be verified, dbv will encounter
the following fatal error:
************************************************************************
[oracle@sinh1 sindb1]$ dbv FILE=system01.dbf
DBVERIFY: Release 11.1.0.6.0 - Production on Mon Aug 17 09:28:31 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBV-00600: Fatal Error - [21] [2] [0] [0]
*************************************************************************
2. Use dbv to verify a segment.
2.1. Find the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock)
of the segment. Using the following SQL:
SILVER@sindb1> SELECT T.TS#,
S.RELATIVE_FNO,
S.HEADER_BLOCK
FROM DBA_SEGMENTS S, V$TABLESPACE T
WHERE S.OWNER='SILVER' AND S.SEGMENT_TYPE='TABLE'
AND S.SEGMENT_NAME='TEST' AND T.NAME=S.TABLESPACE_NAME; 2 3 4 5 6
TS# RELATIVE_FNO HEADER_BLOCK
---------- ------------ ------------
4 4 275
SILVER@sindb1>
2.2. Execute dbv to verify the segment.
[oracle@sinh1 sindb1]$ dbv userid=silver/oracle segment_id=4.4.275 FEEDBACK=1000
DBVERIFY: Release 11.1.0.6.0 - Production on Mon Aug 17 09:40:19 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 4.4.275
.
DBVERIFY - Verification complete
Total Pages Examined : 256
Total Pages Processed (Data) : 155
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 89
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 251928 (0.251928)
[oracle@sinh1 sindb1]$
All in Oracle Database
All in Oracle database.
Monday, August 17, 2009
Thursday, August 13, 2009
How to Create an Encrypted Tablespace in Oracle11g R1
August 13, 2009
1. Create a Wallet
Before you can create an Encrypted tablespace, there must be a wallet.
1.1. specify the wallet location in $ORACLE_HOME/network/admin/sqlnet.ora file.
So the first step is to know where Oracle finds a wallet, wallet searching path.
##################sqlnet.ora#######################
ENCRYPTION_WALLET_LOCATION =(
SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/oracle/admin/sindb1/wallet/))
)
##################sqlnet.ora#######################
1.2. create a wallet.
SQL>CONNECT /AS SYSDBA;
SQL>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
You will find a file named ewallet.p12 in /u01/oracle/admin/sindb1/wallet directory.
You should back it up to an safe place.
1.3. open&close a wallet.
SQL>ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
SQL>ALTER SYSTEM SET WALLET CLOSE;
2. Create an encrypted tablespace.
Issue the following SQL:
SQL> create tablespace tsdata9 datafile '/u01/oracle/oradata/sindb1/tsdata09.dbf' size 16M reuse
encryption using 'AES256' default storage(encrypt);
Now, you can create objects in this tablespace just like in a normal tablespace.
But the content in the datafiles of this tablespace has been encrypted.
1. Create a Wallet
Before you can create an Encrypted tablespace, there must be a wallet.
1.1. specify the wallet location in $ORACLE_HOME/network/admin/sqlnet.ora file.
So the first step is to know where Oracle finds a wallet, wallet searching path.
##################sqlnet.ora#######################
ENCRYPTION_WALLET_LOCATION =(
SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/oracle/admin/sindb1/wallet/))
)
##################sqlnet.ora#######################
1.2. create a wallet.
SQL>CONNECT /AS SYSDBA;
SQL>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
You will find a file named ewallet.p12 in /u01/oracle/admin/sindb1/wallet directory.
You should back it up to an safe place.
1.3. open&close a wallet.
SQL>ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
SQL>ALTER SYSTEM SET WALLET CLOSE;
2. Create an encrypted tablespace.
Issue the following SQL:
SQL> create tablespace tsdata9 datafile '/u01/oracle/oradata/sindb1/tsdata09.dbf' size 16M reuse
encryption using 'AES256' default storage(encrypt);
Now, you can create objects in this tablespace just like in a normal tablespace.
But the content in the datafiles of this tablespace has been encrypted.
Tuesday, August 11, 2009
How to Create an Oracle 11g Database manually on Linux32 Platform
August 12, 2009
In some cases, we may want to create an Oracle database manually. This article will tell you how to.
1. Pre-creation
We suppose the Oracle 11g software has been installed in the machine.
The following environment variables have been set:
ORACLE_BASE (for example, /u01/oracle)
ORACLE_HOME (for example, /u01/oracle/product/11.1.0/db_1)
ORACLE_SID (for example, sindb1)
NLS_LANG (for example, American_america.UTF8)
LD_LIBRARY_PATH (must including $ORACLE_HOME/lib)
PATH (must including $ORACLE_HOME/bin)
You also know the SID name. Then you can begin the manual creation of your database.
2. Prepare the directories.
2.1. Admin directories for your instances
mkdir -p $ORACLE_BASE/admin/sindb1/adump #audit log directory
mkdir -p $ORACLE_BASE/admin/sindb1/script #your manual creation scripts are stored here.
2.2. Diagnostic directories.
Actually, the only thing you should do is to set the DIAGNOSTIC_DEST initial parameter in your pfile or spfile.
Here, we set it to $ORACLE_BASE, that is /u01/oracle. And you need not to manually create any directories for
udump,cdump,bdump, and so on. Oracle will automatically create these directories while you create the database.
2.3. Database datafiles directory
Here, we set it to $ORACLE_BASE/oradata/sindb1.
mkdir -p $ORACLE_BASE/oradata/sindb1
2.4. Checkpoint
Make sure all above directories have been created correctly.
3. Prepare the scripts.
To manually create an Oracle database, you should take the following steps:
Change directory to $ORACLE_BASE/admin/sindb1/script.
3.1. Create the controlling script
The content of the $ORACLE_BASE/admin/sindb1/script/control.sql could like this:
----------------------------------------
--connect sys as sysdba--
--nomount--
spool 1.log;
@createdb.sql;
spool off;
--open--
spool 2.log;
@?/rdbms/admin/catalog.sql;
spool off;
spool 3.log;
@?/rdbms/admin/catproc.sql;
spool off;
--connect system--
--open--
spool 4.log;
@?/sqlplus/admin/pupbld.sql;
spool off;
----------------------------------------
3.2. Create the createdb.sql
The content of the $ORACLE_BASE/admin/sindb1/script/createdb.sql could like this:
----------------------------------------
CREATE DATABASE sindb1
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/oracle/oradata/sindb1/redo1a.log') SIZE 64M REUSE,
GROUP 2 ('/u01/oracle/oradata/sindb1/redo2a.log') SIZE 64M REUSE
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXLOGHISTORY 1
MAXDATAFILES 200
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oracle/oradata/sindb1/system01.dbf' SIZE 325M REUSE
SYSAUX
DATAFILE '/u01/oracle/oradata/sindb1/sysaux01.dbf' SIZE 128M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/oracle/oradata/sindb1/users01.dbf' SIZE 32M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/sindb1/temp01.dbf' SIZE 32M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/oracle/oradata/sindb1/undotbs01.dbf' SIZE 128M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
----------------------------------------
Here, for better efficiency, we don't multiply the online redo logfiles.
4. Create an Oracle database manually.
Change directory to $ORACLE_BASE/admin/sindb1/script.
4.1. Create a password file
OS>orapwd file=$ORACLE_HOME/dbs/orapwsindb1 entries=8
It will prompt you to input the password of SYS user. We assume it is "oracle".
4.2. Create the initial parameter file
The content of the $ORACLE_BASE/admin/sindb1/script/initsindb1.ora could be:
######################################################
db_name='sindb1'
#memory_target=1G
sga_target=300M
log_buffer=4194304
processes = 200
audit_file_dest='/u01/oracle/admin/sindb1/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
#db_recovery_file_dest_size=2G
diagnostic_dest='/u01/oracle'
#dispatchers='(PROTOCOL=TCP) (SERVICE=sindb1XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = ('/u01/oracle/oradata/sindb1/control1.ctl')
control_files = ('/u01/oracle/oradata/sindb1/control2.ctl')
compatible ='11.1.0'
fast_start_mttr_target=1200
######################################################
You can use the MEMORY_TARGET parameter, omit SGA_TARGET and LOG_BUFFER, to simplify the memory management.
This is a new feature of Oracle11g.
The DIAGNOSTIC_DEST parameter is also a wonderful new feature of Oracle11g.
The control_files parameter has many lines, Oracle will merge these lines as many values for this parameter.
Copy $ORACLE_BASE/admin/sindb1/script/initsindb1.ora to $ORACLE_HOME/dbs/.
To verify your pfile, you should do the following:
OS>sqlplus "/as sysdba"
SQL>create spfile='/u01/oracle/product/11.1.0/db_1/dbs/spfilesindb1.ora' from pfile='/u01/oracle/product/11.1.0/db_1/dbs/initsindb1.ora';
SQL>startup nomount;
Then you should check the alert_sindb1.log, located in /u01/oracle/diag/rdbms/sindb1/sindb1/trace.
If any error occurs, revise your pfile and re-startup nomount your instance.
4.3. Run CREATE DATABASE
SQL>connect /as sysdba
SQL>startup nomount;
SQL>spool 1.log;
SQL>@createdb.sql;
SQL>spool off;
Make sure no error message appears in the alert_sindb1.log.
4.4. Run catalog.sql and catproc.sql
Change directory to $ORACLE_BASE/admin/sindb1/script.
SQL>spool 2.log;
SQL>@?/rdbms/admin/catalog.sql;
SQL>spool off;
SQL>spool 3.log;
SQL>@?/rdbms/admin/catproc.sql;
SQL>spool off;
These two sql will take about twenty minutes, depending upon your hardware.
Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
4.5. Run pupbld.sql
SQL>connect system/oracle
SQL>spool 4.log;
SQL>@?/sqlplus/admin/pupbld.sql;
SQL>spool off;
Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
OK. Now your database has been created!
5. Configure listener.ora and tnsnames.ora
Here's the listener.ora and tnsnames.ora, located in $ORACLE_HOME/network/admin.
####################################
###########listener.ora#############
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
(ADDRESS=(PROTOCOL=IPC)(KEY=NPNKEY))
)
SID_LIST_LISTNER=
(SID_LIST=
(SID_DESC=
(GLOBAL_NAME=sindb1)
(SID_NAME=sindb1)
(ORACLE_HOME=/u01/oracle/product/11.1.0/db_1)
)
)
###################################
####################################
###########tnsnames.ora#############
sindb1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sindb1))
)
###################################
You can use lsnrctl tool to start the listener and check if it works using tnsping and sqlplus tools.
OS>lsnrctl start
Make sure no error message appears
OS> tnsping sindb1
Make sure no error message appears
SQL>connect system/oracle@sindb1
Make sure no error message appears
6. Perform a cold backup of your new database.
Shutdown the database and listener.
SQL>connect /as sysdba
SQL>shutdown immediate
SQL>startup
SQL>shutdown immediate
Make sure no error message adds to alert_sindb1.log.
Copy all file in $ORACLE_BASE/oradata/sindb1/ to your backup directory, e.g. /u02/bak/.
Copy the $ORACLE_HOME/dbs/spfilesindb1 to your backup directory, e.g. /u01/bak/.
In some cases, we may want to create an Oracle database manually. This article will tell you how to.
1. Pre-creation
We suppose the Oracle 11g software has been installed in the machine.
The following environment variables have been set:
ORACLE_BASE (for example, /u01/oracle)
ORACLE_HOME (for example, /u01/oracle/product/11.1.0/db_1)
ORACLE_SID (for example, sindb1)
NLS_LANG (for example, American_america.UTF8)
LD_LIBRARY_PATH (must including $ORACLE_HOME/lib)
PATH (must including $ORACLE_HOME/bin)
You also know the SID name. Then you can begin the manual creation of your database.
2. Prepare the directories.
2.1. Admin directories for your instances
mkdir -p $ORACLE_BASE/admin/sindb1/adump #audit log directory
mkdir -p $ORACLE_BASE/admin/sindb1/script #your manual creation scripts are stored here.
2.2. Diagnostic directories.
Actually, the only thing you should do is to set the DIAGNOSTIC_DEST initial parameter in your pfile or spfile.
Here, we set it to $ORACLE_BASE, that is /u01/oracle. And you need not to manually create any directories for
udump,cdump,bdump, and so on. Oracle will automatically create these directories while you create the database.
2.3. Database datafiles directory
Here, we set it to $ORACLE_BASE/oradata/sindb1.
mkdir -p $ORACLE_BASE/oradata/sindb1
2.4. Checkpoint
Make sure all above directories have been created correctly.
3. Prepare the scripts.
To manually create an Oracle database, you should take the following steps:
Change directory to $ORACLE_BASE/admin/sindb1/script.
3.1. Create the controlling script
The content of the $ORACLE_BASE/admin/sindb1/script/control.sql could like this:
----------------------------------------
--connect sys as sysdba--
--nomount--
spool 1.log;
@createdb.sql;
spool off;
--open--
spool 2.log;
@?/rdbms/admin/catalog.sql;
spool off;
spool 3.log;
@?/rdbms/admin/catproc.sql;
spool off;
--connect system--
--open--
spool 4.log;
@?/sqlplus/admin/pupbld.sql;
spool off;
----------------------------------------
3.2. Create the createdb.sql
The content of the $ORACLE_BASE/admin/sindb1/script/createdb.sql could like this:
----------------------------------------
CREATE DATABASE sindb1
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/oracle/oradata/sindb1/redo1a.log') SIZE 64M REUSE,
GROUP 2 ('/u01/oracle/oradata/sindb1/redo2a.log') SIZE 64M REUSE
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXLOGHISTORY 1
MAXDATAFILES 200
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oracle/oradata/sindb1/system01.dbf' SIZE 325M REUSE
SYSAUX
DATAFILE '/u01/oracle/oradata/sindb1/sysaux01.dbf' SIZE 128M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/oracle/oradata/sindb1/users01.dbf' SIZE 32M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/sindb1/temp01.dbf' SIZE 32M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/oracle/oradata/sindb1/undotbs01.dbf' SIZE 128M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
----------------------------------------
Here, for better efficiency, we don't multiply the online redo logfiles.
4. Create an Oracle database manually.
Change directory to $ORACLE_BASE/admin/sindb1/script.
4.1. Create a password file
OS>orapwd file=$ORACLE_HOME/dbs/orapwsindb1 entries=8
It will prompt you to input the password of SYS user. We assume it is "oracle".
4.2. Create the initial parameter file
The content of the $ORACLE_BASE/admin/sindb1/script/initsindb1.ora could be:
######################################################
db_name='sindb1'
#memory_target=1G
sga_target=300M
log_buffer=4194304
processes = 200
audit_file_dest='/u01/oracle/admin/sindb1/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
#db_recovery_file_dest_size=2G
diagnostic_dest='/u01/oracle'
#dispatchers='(PROTOCOL=TCP) (SERVICE=sindb1XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = ('/u01/oracle/oradata/sindb1/control1.ctl')
control_files = ('/u01/oracle/oradata/sindb1/control2.ctl')
compatible ='11.1.0'
fast_start_mttr_target=1200
######################################################
You can use the MEMORY_TARGET parameter, omit SGA_TARGET and LOG_BUFFER, to simplify the memory management.
This is a new feature of Oracle11g.
The DIAGNOSTIC_DEST parameter is also a wonderful new feature of Oracle11g.
The control_files parameter has many lines, Oracle will merge these lines as many values for this parameter.
Copy $ORACLE_BASE/admin/sindb1/script/initsindb1.ora to $ORACLE_HOME/dbs/.
To verify your pfile, you should do the following:
OS>sqlplus "/as sysdba"
SQL>create spfile='/u01/oracle/product/11.1.0/db_1/dbs/spfilesindb1.ora' from pfile='/u01/oracle/product/11.1.0/db_1/dbs/initsindb1.ora';
SQL>startup nomount;
Then you should check the alert_sindb1.log, located in /u01/oracle/diag/rdbms/sindb1/sindb1/trace.
If any error occurs, revise your pfile and re-startup nomount your instance.
4.3. Run CREATE DATABASE
SQL>connect /as sysdba
SQL>startup nomount;
SQL>spool 1.log;
SQL>@createdb.sql;
SQL>spool off;
Make sure no error message appears in the alert_sindb1.log.
4.4. Run catalog.sql and catproc.sql
Change directory to $ORACLE_BASE/admin/sindb1/script.
SQL>spool 2.log;
SQL>@?/rdbms/admin/catalog.sql;
SQL>spool off;
SQL>spool 3.log;
SQL>@?/rdbms/admin/catproc.sql;
SQL>spool off;
These two sql will take about twenty minutes, depending upon your hardware.
Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
4.5. Run pupbld.sql
SQL>connect system/oracle
SQL>spool 4.log;
SQL>@?/sqlplus/admin/pupbld.sql;
SQL>spool off;
Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
OK. Now your database has been created!
5. Configure listener.ora and tnsnames.ora
Here's the listener.ora and tnsnames.ora, located in $ORACLE_HOME/network/admin.
####################################
###########listener.ora#############
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
(ADDRESS=(PROTOCOL=IPC)(KEY=NPNKEY))
)
SID_LIST_LISTNER=
(SID_LIST=
(SID_DESC=
(GLOBAL_NAME=sindb1)
(SID_NAME=sindb1)
(ORACLE_HOME=/u01/oracle/product/11.1.0/db_1)
)
)
###################################
####################################
###########tnsnames.ora#############
sindb1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sindb1))
)
###################################
You can use lsnrctl tool to start the listener and check if it works using tnsping and sqlplus tools.
OS>lsnrctl start
Make sure no error message appears
OS> tnsping sindb1
Make sure no error message appears
SQL>connect system/oracle@sindb1
Make sure no error message appears
6. Perform a cold backup of your new database.
Shutdown the database and listener.
SQL>connect /as sysdba
SQL>shutdown immediate
SQL>startup
SQL>shutdown immediate
Make sure no error message adds to alert_sindb1.log.
Copy all file in $ORACLE_BASE/oradata/sindb1/ to your backup directory, e.g. /u02/bak/.
Copy the $ORACLE_HOME/dbs/spfilesindb1 to your backup directory, e.g. /u01/bak/.
Thursday, August 6, 2009
How to enable block change tracking on Oracle 10g database?
August 6, 2009
The space of a block change tracking file is about 1/30,000 the size of the database to be tracked.
For a database with about 1T, the file size less than 50MB.
This feature of your database can be enabled or disable as you database is open.
1. Enable block change tracking
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
If the STATUS is DISABLE, then you can issue the following SQL to enable this feature.
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F' REUSE;
Then, query the V$BLOCK_CHANGE_TRACKING view again.
2. Disable block change tracking
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Issue the following SQL:
SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Query the V$BLOCK_CHANGE_TRACKING view again.
3. Change location of block change tracking file
3.1. Change location but losing the content inside block change tracking file
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Issue the following SQL:
SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'D:\BCT.F' REUSE;
3.2. Change location without losing the original block change tracking file
SQL>shutdown immediate;
SQL>startup mount;
Then use OS command, e.g. mv or cp, relocate the block change tracking file, for example,
OS>move D:\BCT.F D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F
SQL>alter database rename file 'D:\BCT.F' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F';
SQL>alter database open;
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Note: If you do not rename the file after you have moved the block change tracking file to another
location, as you open the database, Oracle will automatically recreate a new file at the origional
location, thus, you lose the content of the file.
So, keep in mind to rename the file prior to opening the database.
The space of a block change tracking file is about 1/30,000 the size of the database to be tracked.
For a database with about 1T, the file size less than 50MB.
This feature of your database can be enabled or disable as you database is open.
1. Enable block change tracking
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
If the STATUS is DISABLE, then you can issue the following SQL to enable this feature.
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F' REUSE;
Then, query the V$BLOCK_CHANGE_TRACKING view again.
2. Disable block change tracking
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Issue the following SQL:
SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Query the V$BLOCK_CHANGE_TRACKING view again.
3. Change location of block change tracking file
3.1. Change location but losing the content inside block change tracking file
Query the current status of block change tracking on the database.
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Issue the following SQL:
SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'D:\BCT.F' REUSE;
3.2. Change location without losing the original block change tracking file
SQL>shutdown immediate;
SQL>startup mount;
Then use OS command, e.g. mv or cp, relocate the block change tracking file, for example,
OS>move D:\BCT.F D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F
SQL>alter database rename file 'D:\BCT.F' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SVDB1\BCT.F';
SQL>alter database open;
SQL>SELECT STATUS, FILENAME, BYTES/1024/1024 SIZE_M FROM V$BLOCK_CHANGE_TRACKING;
Note: If you do not rename the file after you have moved the block change tracking file to another
location, as you open the database, Oracle will automatically recreate a new file at the origional
location, thus, you lose the content of the file.
So, keep in mind to rename the file prior to opening the database.
Tuesday, August 4, 2009
RMAN Configuration for Oracle
August 4, 2009
When you use rman utility, you can configure some parameters for rman environment.
1. login rman and connect the target database.
OS>rman target /
2. list all rman configurations
RMAN> SHOW ALL;
Note: These configurations are stored in the v$rman_configuration view within your target database's control file.
You can find then like this:
SQL>select CONF#,NAME,VALUE from v$rman_configuration;
3. configure parameters for RMAN.
RMAN>CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;
RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /tmp/%U;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
4. clear configurations for RMAN
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN>CONFIGURE RETENTION POLICY CLEAR;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
5. Configure Tablespaces for Exclusion from Whole Database Backups
RMAN>CONFIGURE EXCLUDE FOR TABLESPACE ts_data1;
You can find these excluded tablespaces from v$tablespace view. You will find that the TEMPORARY tablespace and the TS_DATA1
tablespace are not included in database backkup.
SQL>SELECT * FROM V$TABLESPACE WHERE INCLUDED_IN_DATABASE_BACKUP<>'YES';
You can also find that the tablespaces which were set flashback off from v$tablespace.
SQL>SELECT * FROM V$TABLESPACE WHERE FLASHBACK_ON<>'YES';
When you use rman utility, you can configure some parameters for rman environment.
1. login rman and connect the target database.
OS>rman target /
2. list all rman configurations
RMAN> SHOW ALL;
Note: These configurations are stored in the v$rman_configuration view within your target database's control file.
You can find then like this:
SQL>select CONF#,NAME,VALUE from v$rman_configuration;
3. configure parameters for RMAN.
RMAN>CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;
RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /tmp/%U;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
4. clear configurations for RMAN
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN>CONFIGURE RETENTION POLICY CLEAR;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
5. Configure Tablespaces for Exclusion from Whole Database Backups
RMAN>CONFIGURE EXCLUDE FOR TABLESPACE ts_data1;
You can find these excluded tablespaces from v$tablespace view. You will find that the TEMPORARY tablespace and the TS_DATA1
tablespace are not included in database backkup.
SQL>SELECT * FROM V$TABLESPACE WHERE INCLUDED_IN_DATABASE_BACKUP<>'YES';
You can also find that the tablespaces which were set flashback off from v$tablespace.
SQL>SELECT * FROM V$TABLESPACE WHERE FLASHBACK_ON<>'YES';
Sunday, August 2, 2009
How to transport a tablespace from Linux(32 bit) to Windows(32 bit) in Oracle 10g database?
August 3, 2009
1. Prerequest
Both source and destination Oracle database should have the same database characterset and National character set.
Because the data files of the tablespaces which to be tranformed are copied to the distination directory through os command, e.g., ftp, rcp, cp.
When importing these data files and creating the tablespaces on destination database,
Oracle won't import the logical content of these tablespaces one by one.
Oracle just import the metadata of the objects inside these tablespaces, and reuse these data files, don't clean the content of these data files, and creates these tablespaces.
2. Operations on source database
2.1. Alter the tablespaces to read only model.
SQL>ALTER TABLESPACE EXAMPLE READ ONLY;
SQL>SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='EXAMPLE';
Find out data files of these tablespaces.
SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';
2.2. Check if these tablespaces can be transported?
SQL>CONN SYSTEM/ORACLE@SRC_DB1;
SQL>EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('EXAMPLE',TRUE,TRUE);
SQL>SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
2.3. Check the users who owning objects on these tablespaces.
SQL>SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='EXAMPLE';
2.4. Export metadata of these tablespaces to be transported.
SQL>SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DATA_PUMP_DIR';
expdp system/oracle@SRC_DB1 dumpfile=expdp_example.dmp transport_tablespaces=example
You will find the dump file in the DATA_PUMP_DIR.
3. Using OS command to copy the expdp_example.dmp file to destination machine.
Using OS command to copy these data files of these tablespaces to destination machine.
For example, the example01.dbf is the only data file of the example tablespace. It is copied to the destination machine and is put in the d:\ directory.
The expdp_example.dmp file is copied the DATA_PUMP_DIR of the destination database.
4. Operations on destination database.
4.1. Create users from 2.3. on the destination database.
SQL>create user hr identified by hr;
SQL>grant dba to hr;
4.2. Import the metadata of the transformed tablespaces and create the tablespaces on destination database.
SQL>select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
impdp system/oracle@DEST_DB1 DUMPFILE=expdp_example.dmp TRANSPORT_DATAFILES='D:\example01.dbf' LOGFILE=impdp_example.log
4.3. Check if the tablespaces have been imported.
SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';
SQL>SELECT * FROM HR.COUNTRIES;
5. Endian Transformation
Check the platform of both destination and source database from their v$database views.
Execute the following SQL on both source and destination database to determine if data files need to be transformed.
SQL> SELECT PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
If needed, use RMAN to tranform the data files' endian.
RMAN> CONVERT DATAFILE
2> '/hq/finance/work/tru/tbs_31.f',
3> '/hq/finance/work/tru/tbs_32.f',
4> '/hq/finance/work/tru/tbs_41.f'
5> TO PLATFORM="Solaris[tm] OE (32-bit)"
6> FROM PLATFORM="HP TRu64 UNIX"
7> DB_FILE_NAME_CONVERT=
8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
9> PARALLELISM=5;
6. Solve data pump ORA-29341 error caused by seperately located indexes
When there are many index violations in SYS.TRANSPORT_SET_VIOLATIONS, you can add EXCLUDE=CONSTRAINT parameter in your expdp command line.
This will cause expdp skip ORA-29341 error and expdp will work well.
But in doing so, you should add necessary constrains on the destination database after you have imported these tablespaces.
1. Prerequest
Both source and destination Oracle database should have the same database characterset and National character set.
Because the data files of the tablespaces which to be tranformed are copied to the distination directory through os command, e.g., ftp, rcp, cp.
When importing these data files and creating the tablespaces on destination database,
Oracle won't import the logical content of these tablespaces one by one.
Oracle just import the metadata of the objects inside these tablespaces, and reuse these data files, don't clean the content of these data files, and creates these tablespaces.
2. Operations on source database
2.1. Alter the tablespaces to read only model.
SQL>ALTER TABLESPACE EXAMPLE READ ONLY;
SQL>SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='EXAMPLE';
Find out data files of these tablespaces.
SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';
2.2. Check if these tablespaces can be transported?
SQL>CONN SYSTEM/ORACLE@SRC_DB1;
SQL>EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('EXAMPLE',TRUE,TRUE);
SQL>SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
2.3. Check the users who owning objects on these tablespaces.
SQL>SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='EXAMPLE';
2.4. Export metadata of these tablespaces to be transported.
SQL>SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DATA_PUMP_DIR';
expdp system/oracle@SRC_DB1 dumpfile=expdp_example.dmp transport_tablespaces=example
You will find the dump file in the DATA_PUMP_DIR.
3. Using OS command to copy the expdp_example.dmp file to destination machine.
Using OS command to copy these data files of these tablespaces to destination machine.
For example, the example01.dbf is the only data file of the example tablespace. It is copied to the destination machine and is put in the d:\ directory.
The expdp_example.dmp file is copied the DATA_PUMP_DIR of the destination database.
4. Operations on destination database.
4.1. Create users from 2.3. on the destination database.
SQL>create user hr identified by hr;
SQL>grant dba to hr;
4.2. Import the metadata of the transformed tablespaces and create the tablespaces on destination database.
SQL>select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
impdp system/oracle@DEST_DB1 DUMPFILE=expdp_example.dmp TRANSPORT_DATAFILES='D:\example01.dbf' LOGFILE=impdp_example.log
4.3. Check if the tablespaces have been imported.
SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';
SQL>SELECT * FROM HR.COUNTRIES;
5. Endian Transformation
Check the platform of both destination and source database from their v$database views.
Execute the following SQL on both source and destination database to determine if data files need to be transformed.
SQL> SELECT PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
If needed, use RMAN to tranform the data files' endian.
RMAN> CONVERT DATAFILE
2> '/hq/finance/work/tru/tbs_31.f',
3> '/hq/finance/work/tru/tbs_32.f',
4> '/hq/finance/work/tru/tbs_41.f'
5> TO PLATFORM="Solaris[tm] OE (32-bit)"
6> FROM PLATFORM="HP TRu64 UNIX"
7> DB_FILE_NAME_CONVERT=
8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
9> PARALLELISM=5;
6. Solve data pump ORA-29341 error caused by seperately located indexes
When there are many index violations in SYS.TRANSPORT_SET_VIOLATIONS, you can add EXCLUDE=CONSTRAINT parameter in your expdp command line.
This will cause expdp skip ORA-29341 error and expdp will work well.
But in doing so, you should add necessary constrains on the destination database after you have imported these tablespaces.
Tuesday, July 28, 2009
Fractured block in Oracle
A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.
For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.
Subscribe to:
Posts (Atom)