All in Oracle Database

All in Oracle database.

Monday, August 17, 2009

How to use dbverify utility in Oracle database 11g.

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]$
    

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.


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/.
  
     

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.

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';

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.
  
   

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.