Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Recreation of a Database

Oracle Tips by Burleson
 

Periodically, a DBA may be required to re-create a database. Perhaps the blocksize was incorrectly specified; perhaps it has to be moved from one location to another; or perhaps a DBA has inherited a system and just wants to see how the database was created. I wish I could say there was an easy way to get this information from the database, but unfortunately, unless you have some of the third-party tools, it just isn’t so.

Hopefully, you are with a shop that has detailed information on the hows, whens, and whys databases were created. Unfortunately, most shops seem to do this in the SOTP (seat of the pants) mode, so no actual documentation is available. The lesson to be learned here is to always use a script to create anything in the database that is permanent. Source 2.2 shows a sample script to document a database create statement for an Oracle instance. (Note: In this script, the MAX set of parameters are not completely documented, because these are stored in the control file.) To completely document the CREATE command, also document the control file with:

ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

The file will be located in the directory specified by the BACKGROUND_DUMP_ DEST location specified in the v$parameter table. A sample output from the procedure and SQL script in Source 2.2 is in Listing 2.1. A sample output from the ALTER DATABASE BACKUP CONTROLFILE TO TRACE is shown in Listing 2.2. Another item that must be documented is the initialization parameters for your database; these are located in the v$parameter virtual table. The script db_rct9i.sql from the Wiley Web site will create a template for you to use to re-create your database. An example of the output from DB_RCT9i.sql is shown in Listing 2.1.

CREATE DATABASE GALINUX1

CONTROLFILE
REUSE

MAXINSTANCES 1

MAXLOGFILES
50

MAXDATAFILES 100

MAXLOGHISTORY
226

DATAFILE '/var/oracle/OraHome2/oradata/galinux1/system01.dbf' SIZE 340787200
REUSE

AUTOEXTEND ON NEXT  MAXSIZE
34359721984

DEFAULT STORAGE (INITIAL 16384 NEXT
16384

MINEXTENTS 1 MAXEXTENTS
505

PCTINCREASE
50)

LOGFILE

GROUP 1
('/var/oracle/OraHome2/oradata/galinux1/redo01.log'

) SIZE 104857600,

GROUP 2 (
'/var/oracle/OraHome2/oradata/galinux1/redo02.log'

) SIZE 104857600,

GROUP 3 ('/var/oracle/OraHome2/oradata/galinux1/redo03.log'

) SIZE
104857600

UNDO TABLESPACE UNDOTBS DATAFILE '/var/oracle/OraHome2/oradata/galinux1/undotbs01.dbf' SIZE 209715200
REUSE

AUTOEXTEND ON NEXT 5242880 MAXSIZE
34359721984


EXTENT MANAGEMENT LOCAL AUTOALLOCATE
                                                  

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE /var/oracle/OraHome2/oradata/galinux1/temp01.dbf' SIZE 41943040 REUSE
EXTENT MANAGEMENT LOCAL LOCAL SIZE 1048576

CHARACTER SET
WE8ISO8859P1

NATIONAL CHARACTER SET AL16UTF16

SET TIME_ZONE ='-
07:00'
NOARCHIVELOG
;

Listing 2.1 Example output from the Database re-creation procedure.

/var/oracle/OraHome2/admin/galinux1/udump/ora_9084.trc

Oracle Enterprise Edition Release 9.0.1.0.0 - Production

With the Partitioning option

JServer Release 9.0.1.0.0 - Production

ORACLE_HOME = /var/oracle/OraHome2

System name:     Linux

Node name:     pubsgalinux

Release:     2.4.4

Version:     #8 Fri Jul 13 13:48:47 EDT 2001

Machine:     i586

Instance name: galinux1

Redo thread mounted by this instance: 1

Oracle process number: 12

Unix process pid: 9084, image: oracle@pubsgalinux (TNS V1-V3)

*** SESSION ID:(7.1237) 2001-08-23 16:08:41.118

*** 2001-08-23 16:08:41.118

# The following commands will create a new control file and use it

# to open the database.

# Data used by the recovery manager will be lost. Additional logs may

# be required for media recovery of offline data files. Use this

# only if the current version of all online logs are available.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GALINUX1" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/var/oracle/OraHome2/oradata/galinux1/redo01.log'  SIZE 100M,
  GROUP 2 '/var/oracle/OraHome2/oradata/galinux1/redo02.log'  SIZE 100M,
  GROUP 3 '/var/oracle/OraHome2/oradata/galinux1/redo03.log'  SIZE 100M
# STANDBY LOGFILE
DATAFILE
  '/var/oracle/OraHome2/oradata/galinux1/system01.dbf',
  '/var/oracle/OraHome2/oradata/galinux1/undotbs01.dbf',
  '/var/oracle/OraHome2/oradata/galinux1/cwmlite01.dbf',
  '/var/oracle/OraHome2/oradata/galinux1/drsys01.dbf',
  '/var/oracle/OraHome2/oradata/galinux1/example01.dbf',
  '/var/oracle/OraHome2/oradata/galinux1/tools01.dbf',  '/var/oracle/OraHome2/oradata/galinux1/users01.dbf',  '/opt/oracle/oradata/galinux1/dbautil_index01.dbf',  '/opt/oracle/oradata/galinux1/dbautil_data01.dbf'
CHARACTER SET WE8ISO8859P1
;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.
RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN;

# Commands to add tempfiles to temporary tablespaces.

# Online tempfiles have complete space information.

# Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/var/oracle/OraHome2/oradata/galinux1/temp01.dbf' REUSE;

# End of tempfile additions.

#

Listing 2.2 Example of output from the ALTER DATABASE BACKUP CONTROL FILE command.

REM
REM NAME        : init_ora_rct.sql
REM FUNCTION    : Recreate the instance init.ora file
REM USE         : GENERAL
REM Limitations : None
REM
SET NEWPAGE 0 VERIFY OFF
SET ECHO OFF feedback off termout off PAGES 300 lines 80 heading off
column name  format a80 word_wrapped
column dbname new_value db noprint
select name dbname from v$database;
DEFINE OUTPUT = 'rep_out\&db\init.ora'
SPOOL &OUTPUT
SELECT '# Init.ora file from v$parameter' name from dual
union
select '# generated on:'||sysdate name from dual
union
select '# script by MRA 08/7/01 PUBS' name from dual
union
select '#' name from dual
UNION
SELECT name||' = '||value name  FROM V$PARAMETER
WHERE value is not null and ISDEFAULT='FALSE';
SPOOL OFF
CLEAR COLUMNS
SET NEWPAGE 0 VERIFY OFF
SET ECHO ON termout on PAGES 22 lines 80 heading on
SET TERMOUT ON
UNDEF OUTPUT
PAUSE Press enter to continue


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter