Script to Generate a CREATE DATABASE command from an existing database.

Product	Oracle Server
Component	RDBMS
Sub Component	RDBMS
Product Versions	8.1.x to ease 9.x]
Platform	Generic
Date Created	10-Oct-2002
Keywords	Generate, create, database, recreate
Related Support Tools
Instructions
Execution Environment: <SQL, SQL*Plus, iSQL*Plus> Access Privileges: Requires connect as sysdba privileges Usage: sqlplus /nolog SQL> connect sys/<password> as sysdba SQL> @gencrdb Instructions: Copy the scripts into a file named gencrdb.sql. Execute the script from sqlplus connected as sysdba. PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.The script will produce an output file named crdb.sql. This file can be used to create a database with the same initial configuration of the one in which the script was executed..
Description
The included script will generate a CREATE DATABASE command for an existing database. It can be used to recreate the database in connection with a full export and import, used for reference etc. o This will also work in an OPS or RAC environment, you will need to create the logfiles for additional threads manually. o You need to be connected as SYSDBA or have select privileges on all referenced dictionary tables / views.
References
[Include references to FAQ, Troubleshooting guide, and Current issues Articles from Top Tech Docs or other relevant references.]
Script
-- gencrdb.sql -- -- Generate a CREATE DATABASE command from an existing database. -- (C) 2002 Oracle Corporation, written by Harm ten Napel -- This script will work from 8i onwards. -- -- DISCLAIMER -- -- This script is provided for educational purposes only. It is NOT supported -- by Oracle World Wide Technical Support. The script has been tested and -- appears to work as intended. However, you should always test any script -- before relying on it. -- spool crdb.sql set pages 1000 set head off set termout off set feedback off set newpage none set serveroutput on select 'CREATE DATABASE '||name text from v$database; -- select 'CONTROLFILE REUSE' from dual; -- optional select 'LOGFILE' from dual; declare print_var varchar2(200); cursor c1 is select member from gv$logfile where inst_id = 1 order by group#; logfile gv$logfile.member%TYPE; cursor c2 is select bytes from gv$log where inst_id = 1 order by group#; bytes number; lsize varchar2(30); begin open c1; open c2; for record in ( select group#, count(*) members from gv$logfile where inst_id = 1 group by group#) loop dbms_output.put_line(print_var); fetch c2 into bytes; if mod(bytes,1024) = 0 then if mod(bytes,1024*1024) = 0 then lsize := to_char(bytes/(1024*1024))||'M'; else lsize := to_char(bytes/1024)||'K'; end if; else lsize := to_char(bytes); end if; lsize := lsize||','; if record.members > 1 then fetch c1 into logfile; print_var := 'GROUP '||record.group#||' ('; dbms_output.put_line(print_var); print_var := ''''||logfile||''''||','; for i in 2..record.members loop fetch c1 into logfile; dbms_output.put_line(print_var); print_var := ''''||logfile||''''||','; end loop; print_var := rtrim(print_var,','); dbms_output.put_line(print_var); print_var := ') SIZE '||lsize; else fetch c1 into logfile; print_var := 'GROUP '||record.group#||' '''|| logfile||''''||' SIZE '||lsize; end if; end loop; close c1; close c2; print_var := rtrim(print_var,','); dbms_output.put_line(print_var); end; / select 'MAXLOGFILES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'REDO LOG'; select 'MAXLOGMEMBERS '||dimlm from sys.x$kccdi; select 'MAXDATAFILES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'DATAFILE'; select 'MAXINSTANCES '||RECORDS_TOTAL from v$controlfile_record_section where type = 'DATABASE'; select 'MAXLOGHISTORY '||RECORDS_TOTAL from v$controlfile_record_section where type = 'LOG HISTORY'; select log_mode from v$database; select 'CHARACTER SET '||value from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; select 'NATIONAL CHARACTER SET '||value from v$nls_parameters where parameter = 'NLS_NCHAR_CHARACTERSET'; select 'DATAFILE' from dual; declare cursor c1 is select * from dba_data_files where tablespace_name = 'SYSTEM' order by file_id; datafile dba_data_files%ROWTYPE; print_datafile dba_data_files.file_name%TYPE; begin open c1; fetch c1 into datafile; -- there is always 1 datafile print_datafile := ''''||datafile.file_name|| ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,'; loop fetch c1 into datafile; if c1%NOTFOUND then -- strip the comma and print the last datafile print_datafile := rtrim(print_datafile,','); dbms_output.put_line(print_datafile); exit; else -- print the previous datafile and prepare the next dbms_output.put_line(print_datafile); print_datafile := ''''||datafile.file_name|| ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,'; end if; end loop; end; / select ';' from dual; spool off -- end script