#!/bin/ksh #============================================================================ # File: clonedb.sh # Type: UNIX korn-shell script # Author: Tim Gorman (Evergreen Database Technologies, Inc) # Date: 21 June, 1999 # # Description: # # UNIX shell-script to create a "clone" subset database for one # specific tablespace within the current database. # # The script makes the following assumptions (which may not be # valid on all systems): # # 1. both the "source" database and the "clone" database are # constructed using the OFA standard for datafiles, # archived redo logfiles, and administrative files # 2. the DB_NAME parameter (not the $ORACLE_SID value) is # used for all directory naming in the OFA standard # 3. a standard UNIX "rcp" command (or something which uses # the same syntax, such as "scp") can be used without the # need for UNIX-level passwords for all file transfers. # Specify the file-transfer command to be used: _CopyCmd="rcp" # ...by modifying the line above... # # If any of the above assumptions are *NOT* true, then be sure to set # the following shell variable "_Remark" to contain the value "REM ". # Be sure to include the trailing space-character (i.e. _Remark="REM "): _Remark="REM " # If *ALL* of the above assumptions are true, then it would be safe to # change the value of the shell variable above to the null-string: ###_Remark="" # # Setting the shell variable "_Remark" to the value of "REM " will # prevent the generated scripts from being executed. This is suitable # for testing and for environments where all mount-points and directory # naming are not *exactly* equivalent (with only the "db-name" being # different). # # In the event that both environments (i.e. both the "source" database # and the "clone" database) are exactly the same (except where the # "db-name" is referenced), then setting the "_Remark" shell variable # to a null-value string (i.e. "") would allow the generated SQL # scripts to execute immediately. # # PLEASE BE AWARE! It is far safer to leave the "_Remark" variable # containing value "REM ", so that you can review # the generated scripts beforehand... # # Modifications: # TGorman 21jun99 written for Matchlogic # #============================================================================ # #---------------------------------------------------------------------------- # ...validate command-line parameters... #---------------------------------------------------------------------------- if (( $# != 3 )) then echo "Usage: \"clonedb.sh \"; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...save the command-line parameters into shell variables... #---------------------------------------------------------------------------- _TS=${1} _ToHost=${2} _ToDb=${3} _TmpFile=/tmp/clonedb_${_ToDb}_$$ # #---------------------------------------------------------------------------- # ...verify that ORACLE_SID and ORACLE_HOME are set... #---------------------------------------------------------------------------- if [[ "${ORACLE_SID}" = "" ]] then echo "\"ORACLE_SID\" is not set; aborting..." exit 1 fi if [[ "${ORACLE_HOME}" = "" ]] then echo "\"ORACLE_HOME\" is not set; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...verify that the SQL*Plus executable is executable... #---------------------------------------------------------------------------- if [ ! -x ${ORACLE_HOME}/bin/sqlplus ] then echo "SQL*Plus executable file cannot be found; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...verify the tablespace name (i.e. 1st command-line parameter) and # retrieve the DB_NAME value from the source database... #---------------------------------------------------------------------------- ${ORACLE_HOME}/bin/sqlplus -s internal << __EOF__ > ${_TmpFile}.out 2>&1 whenever oserror exit failure whenever sqlerror exit failure set echo off feedb off timi off pages 0 trimsp on lines 500 column dummy new_value V_DUMMY select decode(count(*), 1, 'dummy', 'ERROR!') dummy from dba_tablespaces where tablespace_name = upper('${_TS}'); select &&V_DUMMY from dual; __EOF__ # #---------------------------------------------------------------------------- # ...if the SQL*Plus session (above) failed, it is likely because the # tablespace name specified was not valid for the source database... #---------------------------------------------------------------------------- if (( $? != 0 )) then echo "\"${_TS}\" not valid tablespace in \"${ORACLE_SID}\"; aborting..." rm -f ${_TmpFile}.out exit 1 fi # #---------------------------------------------------------------------------- # ...connect once more to SQL*Plus as the INTERNAL (i.e. SYS) account and # use the "SQL-generating-SQL" technique to generate the following SQL # scripts: # 1. a script to copy datafiles from the source database to the # clone database # 2. a script to copy archived redo logfiles from the source database # to the clone database # 3. a script to create a new controlfile for the clone database, # perform recovery, and open the clone database # 4. an "init.ora" file for the clone database # If the "_Remark" shell variable is set to the "null" string, then the # first two scripts will also be executed on the source database immediately # after the are generated. If the "_Remark" shell variable is set to the # value of "REM " (i.e. the default value), then none of these scripts will # be executed at all... #---------------------------------------------------------------------------- ${ORACLE_HOME}/bin/sqlplus -s internal << __EOF__ > ${_TmpFile}.out 2>&1 whenever oserror exit 1 whenever sqlerror exit 1 col value new_value V_DBNAME noprint select value from v\$parameter where name = 'db_name'; /* * ...generate an "init.ora" initialization parameter file for use by * the "clone" database... */ spool init${_ToDb}_0.ora prompt # generated by "clonedb.sh" based on database &&V_DBNAME prompt # select name || ' = ' || replace(value, '&&V_DBNAME', '${_ToDb}') text from v\$parameter where name in ('audit_file_dest','background_dump_dest','core_dump_dest', 'user_dump_dest','compatible','control_files','db_block_size', 'db_files','db_name','log_archive_dest','log_archive_format', 'nls_date_format','rollback_segments','event') order by name, value / spool off whenever oserror exit 2 whenever sqlerror exit 2 set echo off feedb off timin off pages 0 trimsp on lines 500 paus off verif off col sort1 noprint col sort2 noprint col sort3 noprint /* * ...generate a SQL script to copy all necessary datafiles to the "clone" * database. In the event that the mount-points of the "clone" database * environment do not exactly match those of the "source" database, then * this generated SQL script *MUST* be edited before being used... */ spool clonedb_${_ToDb}_1.sql prompt variable v_log_seq number prompt begin prompt select sequence# into :v_log_seq from v\$log where status = 'CURRENT';; prompt end;; prompt / select distinct 1 sort1, tablespace_name sort2, 1 sort3, 'ALTER TABLESPACE ' || tablespace_name || ' BEGIN BACKUP;' text from dba_rollback_segs where status = 'ONLINE' union select 1 sort1, f.tablespace_name sort2, 2 sort3, '!${_CopyCmd} ' || f.file_name || ' ${_ToHost}:' || replace(f.file_name, '&&V_DBNAME', '${_ToDb}') text from (select distinct tablespace_name from dba_rollback_segs where status = 'ONLINE') s, dba_data_files f where f.tablespace_name = s.tablespace_name union select distinct 1 sort1, tablespace_name sort2, 3 sort3, 'ALTER TABLESPACE ' || tablespace_name || ' END BACKUP;' text from dba_rollback_segs where status = 'ONLINE' union select distinct 1 sort1, tablespace_name sort2, 4 sort3, 'ALTER SYSTEM SWITCH LOGFILE;' text from dba_rollback_segs where status = 'ONLINE' union select distinct 1 sort1, tablespace_name sort2, 5 sort3, 'whenever sqlerror continue' text from dba_rollback_segs where status = 'ONLINE' union select distinct 1 sort1, tablespace_name sort2, 6 sort3, 'ALTER SYSTEM ARCHIVE LOG ALL;' text from dba_rollback_segs where status = 'ONLINE' union select distinct 1 sort1, tablespace_name sort2, 7 sort3, 'whenever sqlerror exit failure' text from dba_rollback_segs where status = 'ONLINE' union select distinct 1 sort1, tablespace_name sort2, 8 sort3, '@clonedb_arch ${_CopyCmd} ${_ToHost} ${_ToDb} ' || tablespace_name text from dba_rollback_segs where status = 'ONLINE' union select 2 sort1, '${_TS}' sort2, 1 sort3, 'ALTER TABLESPACE ${_TS} BEGIN BACKUP;' text from dual union select 2 sort1, '${_TS}' sort2, 2 sort3, '!${_CopyCmd} ' || file_name || ' ${_ToHost}:' || replace(file_name, '&&V_DBNAME', '${_ToDb}') text from dba_data_files where tablespace_name = upper('${_TS}') union select 2 sort1, '${_TS}' sort2, 3 sort3, 'ALTER TABLESPACE ${_TS} END BACKUP;' text from dual union select 2 sort1, '${_TS}' sort2, 4 sort3, 'ALTER SYSTEM SWITCH LOGFILE;' text from dual union select 2 sort1, '${_TS}' sort2, 5 sort3, 'whenever sqlerror continue' text from dual union select 2 sort1, '${_TS}' sort2, 6 sort3, 'ALTER SYSTEM ARCHIVE LOG ALL;' text from dual union select 2 sort1, '${_TS}' sort2, 7 sort3, 'whenever sqlerror exit failure' text from dual union select 2 sort1, '${_TS}' sort2, 8 sort3, '@clonedb_arch ${_CopyCmd} ${_ToHost} ${_ToDb} ${_TS}' text from dual order by 1, 2, 3, 4 / spool off whenever oserror exit 3 whenever sqlerror exit 3 set echo on feedback on timing on /* * ...un-comment these lines *ONLY* if the "source" database and the "clone" * database share the exact same file-directory and mount-point naming * conventions... */ ${_Remark}spool clonedb_${_ToDb}_1 ${_Remark}@clonedb_${_ToDb}_1 ${_Remark}spool off whenever oserror exit 4 whenever sqlerror exit 4 set echo off feedback off timing off /* * ...generate a SQL script to generate a new controlfile for the "clone" * database using the CREATE CONTROLFILE command... */ spool clonedb_${_ToDb}_2.sql prompt SPOOL clonedb_${_ToDb}_2 prompt STARTUP NOMOUNT PFILE=${ORACLE_HOME}/dbs/init${_ToDb}_0.ora prompt CREATE CONTROLFILE REUSE SET DATABASE ${_ToDb} RESETLOGS prompt MAXLOGFILES 16 prompt MAXLOGMEMBERS 1 prompt MAXDATAFILES 1022 prompt MAXINSTANCES 1 prompt MAXLOGHISTORY 1000 prompt LOGFILE GROUP 1 ( prompt '/vol01/oradata/${_ToDb}/redo01a.log' prompt ) SIZE 100M, prompt GROUP 2 ( prompt '/vol01/oradata/${_ToDb}/redo02a.log' prompt ) SIZE 100M select decode(rownum, 1, 'DATAFILE ', ', ') || '''' || replace(file_name, '&&V_DBNAME', '${_ToDb}') || '''' from dba_data_files where tablespace_name in (select distinct tablespace_name from dba_rollback_segs where status = 'ONLINE' union select upper('${_TS}') from dual); prompt ;; prompt RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;; prompt ALTER SYSTEM ARCHIVE LOG ALL;; prompt ALTER DATABASE OPEN RESETLOGS;; prompt SPOOL OFF exit success __EOF__ # #---------------------------------------------------------------------------- #---------------------------------------------------------------------------- integer _Rtn=$? if (( ${_Rtn} != 0 )) then echo "SQL*Plus failed with exit status \"${_Rtn}\"; aborting..." echo "Check file \"${_TmpFile}.out\"..." exit 1 fi # #---------------------------------------------------------------------------- # ...notify the calling user of the locations of the various generated # scripts... #---------------------------------------------------------------------------- echo "" echo "Parameter file for \"clone\" database in \"init${_ToDb}_0.ora\"..." if [[ "${_Remark}" = "" ]] then echo "Datafiles/archives copied using \"clonedb_${_ToDb}_1.sql\"..." else echo "SQL script to copy datafiles in \"clonedb_${_ToDb}_1.sql\"..." fi echo "SQL script to CREATE CONTROLFILE in \"clonedb_${_ToDb}_2.sql\"..." echo "" # #---------------------------------------------------------------------------- # ...cleanup temporary "scratch" files and exit... #---------------------------------------------------------------------------- rm -f ${_TmpFile}.* exit 0