DBA script (2)

From: Paultje Bakker <bakker_at_cs.uq.oz.au>
Date: 21 Feb 1994 01:02:59 GMT
Message-ID: <2k9183$3pe_at_uqcspe.cs.uq.oz.au>

Hi people,

Here's the script to recreate a database. Please forgive any (easily changed) VAX specific bits.
REM File:
REM RECREATE_DATABASE.SQL
REM
REM Purpose:
REM SQL*Plus Script to create a SQLDBA command file called REM CREATE_<DB>.SQL,which when run shall recreate the database REM <DB> - the one it was run under.
REM Result serves also as documentation of how a database is REM constructed, and may be edited by the DBA to eg. reduce REM the number of datafiles per tablespace. REM
REM For now, store the results in a separate directory until REM their correctness is proven.
REM
REM History:
REM 26-JAN-94 R. Bakker Created REM
REM Settings
REM
SET ECHO OFF
SET TERMOUT OFF
SET HEAD OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET EMBEDDED ON
SET PAGESIZE 0
SET LINESIZE 100
SET RECSEP OFF
REM
REM Initialisations
REM
REM May have to change this to CHAR(10)||CHAR(13) for some configurations. REM
DEFINE CR = "CHR(10)"

DEFINE SEPARATOR = "# ========================================================================="

REM
REM Fetch database name and store it in "db" REM
column db new_value db noprint format a9 select value db
from v$parameter
where name = 'db_name'
/
REM
REM Fetch today's date + time
REM
column now new_value now noprint format a20 select to_char(sysdate,'DD-MON-YY HH24:MI:SS') now from sys.dual;
/
spool dsk$pg0001:[pg0001.create_db]create_&db..sql prompt &separator

prompt # File:
prompt #    CREATE_&DB..SQL
prompt #
prompt # Purpose:
prompt #    SQL*DBA command procedure to create the &db database.
prompt #
prompt # History:
prompt #    &now  Created automatically by RECREATE_DATABASE.SQL
prompt #
prompt # Usage:
prompt #    This file reflects the state of the &db database at
prompt #    the date and time shown above. It can be run as is or
prompt #    edited in order to implement changes in the database,
prompt #    eg. resize a tablespace, create a tablespace etc.
prompt #
prompt #    This file should be regenerated regularly, preferably daily.
prompt #
prompt #    This file is to be run in SQLDBA, after the database
prompt #    has been shut down. Make sure that any logicals related
prompt #    with the database have been set, eg. on VMS, you would
prompt #    have to do _at_dsk$localdba:[&db]oradbs.com first to
prompt #    give ORA_PARAMS a value (amongst others).
prompt #
prompt #    Running this file recreates the database, ie:
prompt #    **** ALL DATA IS LOST ****
prompt #    so make sure a full export is made first.
prompt #
prompt #    Note that REUSE has been placed in connection with each
prompt #    datafile; if you want to change the size of one, remove
prompt #    the REUSE clause and delete the old version of the file
prompt #    sometime between shutting down the database and running
prompt #    this file. Any new datafiles to be added should NOT have
prompt #    a REUSE clause.
prompt #
prompt #    This file should be run under a userid which has the
prompt #    privileges necessary to create the specified files.
prompt #
prompt #    NOTE: Until a way can be found to determine the size
prompt #    of a redo log file from within ORACLE, we have no choice
prompt #    but to delete the log files first and then recreate as
prompt #    the default size of 500K. That is why no REUSE clause is
prompt #    used for them.
prompt #

prompt &separator
prompt
prompt set echo on
prompt
prompt spool sys$login:create_&db..lis
prompt
prompt &separator
prompt # Delete redo log files and then recreate, as size can't be determined. prompt &separator
prompt
select 'host delete/log '||T1.NAME||';*' from V$LOGFILE T1
/
prompt
prompt startup nomount
prompt
prompt connect internal
prompt
select 'create database &db controlfile reuse'||&CR||

       '       datafile '||''''||T1.FILE_NAME||''''||' size '||
       decode(mod(T1.BYTES,(1024*1024))


              ,0,to_char(T1.BYTES/(1024*1024))||'M'


              ,  decode(mod(T1.BYTES,1024)


                        ,0,to_char(T1.BYTES/1024)||'K'
                        ,  to_char(T1.BYTES)
                       )
             )||' reuse'

from SYS.DBA_DATA_FILES T1
where T1.TABLESPACE_NAME = 'SYSTEM'
and T1.FILE_ID = (select min(T2.FILE_ID)

                     from   SYS.DBA_DATA_FILES T2
                     where  T2.TABLESPACE_NAME = 'SYSTEM')

/
REM
REM Recreate the redo log files at the standard (and default) size REM of 500K.
REM
select decode(T1.FILE#

,1,' logfile '
, ' '

             )||''''||T1.NAME||''''||' size 500K'||
       decode(T1.FILE#


              ,T2.FILE#,';'


              ,         ','

             )

from V$LOGFILE T2,V$LOGFILE T1
where T2.FILE# = (select max(T3.FILE#)

                   from   V$LOGFILE T3)

/
prompt
prompt &separator
prompt # Add a second rollback segment to allow creation of additional tablespaces prompt &separator
prompt
prompt create rollback segment dummy;;
prompt
prompt &separator

prompt # Use an operating system-dependent procedure to temporarily
prompt # add the line "rollback_segments = (dummy)"
prompt # to init.ora, so the database shall start with rollback segments that
prompt # already exist.

prompt &separator
prompt
REM
REM I would have preferred to do this via a SQL script to keep it REM OS independent, eg:
REM _at_ou:change_init_ora &db
REM but alas, in SQLDBA variables and parameters do not exist. REM VMS specific!
REM
prompt host _at_ou:change_init_ora &db
prompt
prompt &separator
prompt # Shut down and then restart the database to enable the prompt # dummy rollback segment
prompt &separator
prompt
prompt disconnect
prompt shutdown immediate
prompt startup open &db
prompt
prompt &separator
prompt # Create the additional tablespaces (if any). prompt &separator
prompt
prompt connect system/manager
prompt
REM
REM The union is necessary due to the limit imposed on the length REM of what a query can return.
REM
column TABLESPACE_NAME noprint
column part noprint
select T1.TABLESPACE_NAME

,      1 part
,      'create tablespace       '||T1.TABLESPACE_NAME||&CR||
       '       datafile         '||''''||T2.FILE_NAME||''''||&CR||
       '       size             '||
       decode(mod(T2.BYTES,(1024*1024))


              ,0,to_char(T2.BYTES/(1024*1024))||'M'


              ,  decode(mod(T2.BYTES,1024)


                        ,0,to_char(T2.BYTES/1024)||'K'
                        ,  to_char(T2.BYTES)
                       )
             )||' reuse'||&CR||
       '       default storage  (initial        '||
       decode(mod(T1.INITIAL_EXTENT,(1024*1024))


              ,0,to_char(T1.INITIAL_EXTENT/(1024*1024))||'M'


              ,  decode(mod(T1.INITIAL_EXTENT,1024)


                        ,0,to_char(T1.INITIAL_EXTENT/1024)||'K'
                        ,  to_char(T1.INITIAL_EXTENT)
                       )
             )

from SYS.DBA_DATA_FILES T2,SYS.DBA_TABLESPACES T1

where  T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
and    T1.TABLESPACE_NAME != 'SYSTEM'
and    T2.FILE_ID = (select min(T3.FILE_ID)
                     from   SYS.DBA_DATA_FILES T3
                     where  T3.TABLESPACE_NAME = T1.TABLESPACE_NAME)

union
select T1.TABLESPACE_NAME

,      2 part
,      '                         next           '||
       decode(mod(T1.NEXT_EXTENT,(1024*1024))


              ,0,to_char(T1.NEXT_EXTENT/(1024*1024))||'M'


              ,  decode(mod(T1.NEXT_EXTENT,1024)


                        ,0,to_char(T1.NEXT_EXTENT/1024)||'K'
                        ,  to_char(T1.NEXT_EXTENT)
                       )
             )||&CR||
       '                         minextents     '||
       decode(mod(T1.MIN_EXTENTS,(1024*1024))


              ,0,to_char(T1.MIN_EXTENTS/(1024*1024))||'M'


              ,  decode(mod(T1.MIN_EXTENTS,1024)


                        ,0,to_char(T1.MIN_EXTENTS/1024)||'K'
                        ,  to_char(T1.MIN_EXTENTS)
                       )
             )||&CR||
       '                         maxextents     '||
       decode(mod(T1.MAX_EXTENTS,(1024*1024))


              ,0,to_char(T1.MAX_EXTENTS/(1024*1024))||'M'


              ,  decode(mod(T1.MAX_EXTENTS,1024)


                        ,0,to_char(T1.MAX_EXTENTS/1024)||'K'
                        ,  to_char(T1.MAX_EXTENTS)
                       )
             )||&CR||
       '                         pctincrease    '||
       to_char(T1.PCT_INCREASE)||');'||&CR||&CR

from SYS.DBA_DATA_FILES T2,SYS.DBA_TABLESPACES T1

where  T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
and    T1.TABLESPACE_NAME != 'SYSTEM'
and    T2.FILE_ID = (select min(T3.FILE_ID)
                     from   SYS.DBA_DATA_FILES T3
                     where  T3.TABLESPACE_NAME = T1.TABLESPACE_NAME)

order by 1,2
/
prompt
prompt &separator
prompt # Additional tablespace datafiles (if any) prompt &separator
prompt

select 'alter  tablespace       '||T1.TABLESPACE_NAME||&CR||
       '       add'||&CR||
       '       datafile         '||''''||T1.FILE_NAME||''''||&CR||
       '       size             '||
       decode(mod(T1.BYTES,(1024*1024))


              ,0,to_char(T1.BYTES/(1024*1024))||'M'


              ,  decode(mod(T1.BYTES,1024)


                        ,0,to_char(T1.BYTES/1024)||'K'
                        ,  to_char(T1.BYTES)
                       )
             )||' reuse;'||&CR||&CR

from SYS.DBA_DATA_FILES T1
where T1.FILE_ID != (select min(T2.FILE_ID)

                      from   SYS.DBA_DATA_FILES T2
                      where  T2.TABLESPACE_NAME = T1.TABLESPACE_NAME)

/
prompt
prompt &separator
prompt # Additional rollback segments (if any) prompt &separator
prompt
column SEGMENT_NAME noprint
column part noprint
select T1.SEGMENT_NAME

,      1 part
,      'create rollback segment '||T1.SEGMENT_NAME||&CR||
       '       tablespace       '||T1.TABLESPACE_NAME||&CR||
       '       storage          (initial        '||
       decode(mod(T1.INITIAL_EXTENT,(1024*1024))


              ,0,to_char(T1.INITIAL_EXTENT/(1024*1024))||'M'


              ,  decode(mod(T1.INITIAL_EXTENT,1024)


                        ,0,to_char(T1.INITIAL_EXTENT/1024)||'K'
                        ,  to_char(T1.INITIAL_EXTENT)
                       )
             )

from SYS.DBA_ROLLBACK_SEGS T1
where T1.SEGMENT_NAME not in ('SYSTEM','DUMMY') union
select T1.SEGMENT_NAME

,      2 part
,      '                         next           '||
       decode(mod(T1.NEXT_EXTENT,(1024*1024))


              ,0,to_char(T1.NEXT_EXTENT/(1024*1024))||'M'


              ,  decode(mod(T1.NEXT_EXTENT,1024)


                        ,0,to_char(T1.NEXT_EXTENT/1024)||'K'
                        ,  to_char(T1.NEXT_EXTENT)
                       )
             )||&CR||
       '                         minextents     '||
       decode(mod(T1.MIN_EXTENTS,(1024*1024))


              ,0,to_char(T1.MIN_EXTENTS/(1024*1024))||'M'


              ,  decode(mod(T1.MIN_EXTENTS,1024)


                        ,0,to_char(T1.MIN_EXTENTS/1024)||'K'
                        ,  to_char(T1.MIN_EXTENTS)
                       )
             )||&CR||
       '                         maxextents     '||
       decode(mod(T1.MAX_EXTENTS,(1024*1024))


              ,0,to_char(T1.MAX_EXTENTS/(1024*1024))||'M'


              ,  decode(mod(T1.MAX_EXTENTS,1024)


                        ,0,to_char(T1.MAX_EXTENTS/1024)||'K'
                        ,  to_char(T1.MAX_EXTENTS)
                       )
             )||&CR||
       '                         pctincrease    '||
       to_char(T1.PCT_INCREASE)||');'||&CR||&CR

from SYS.DBA_ROLLBACK_SEGS T1
where T1.SEGMENT_NAME not in ('SYSTEM','DUMMY') order by 1,2
/
prompt
prompt &separator
prompt # Use an operating system-dependent procedure to reinstate prompt # the init.ora file (ie. without "rollback_segments = (dummy)"). prompt &separator
prompt
REM
REM I would have preferred to do this via a SQL script to keep it REM OS independent, eg:
REM _at_ou:replace_init_ora &db
REM but alas, in SQLDBA variables and parameters do not exist. REM VMS specific!
REM
prompt host _at_ou:replace_init_ora &db
prompt
prompt &separator
prompt # Shut down and then restart the database to activate rollback prompt # segments and get rid of the dummy rollback segment. prompt &separator
prompt
prompt disconnect
prompt shutdown immediate
prompt startup open &db
prompt
prompt connect system/manager
prompt
prompt drop rollback segment dummy;;
prompt
prompt &separator
prompt # Remind the user of what he still has to do prompt &separator
prompt
prompt
prompt REM Database creation completed.
prompt REM If you previously did a full export, now is the time prompt REM to do the full import.
prompt REM
prompt REM Don't forget to change the SYS and SYSTEM passwords! prompt
prompt spool off
prompt exit
spool off
SET TERMOUT ON
SET HEAD ON
SET VERIFY ON
SET FEEDBACK ON
SET EMBEDDED OFF
SET PAGESIZE 66
SET LINESIZE 80
SET RECSEP WRAP Rick Bakker, roving DBA

--
Paul Bakker  bakker_at_cs.uq.oz.au | "PhD theses usually marshall an
Computer Science Dept.          | army of facts to starve a
The University of Qld           | slender and tedious truth
QLD 4072 Australia              | into submission" - The Guardian              

Received on Mon Feb 21 1994 - 02:02:59 CET