Product Oracle ServerComponent RDBMSSub Component RDBMSProduct Versions 8.1.x to ease 9.x] Platform GenericDate Created 10-Oct-2002Keywords Generate, create, database, recreateRelated Support Tools Instructions Execution Environment:
Access Privileges:
Requires Connect As Sysdba Privileges
USAGE:
SQLPlus / NOLOG
SQL> Connect Sys /
SQL> @gencrdb
Instructions:
Copy The Scripts Into A File named gencrdb.sql. Execute the script from sqlplus
Connected as sysdba.
Proofread this script before usding 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 include Script Will Generate a create Database Command for An EXISTING
Database. It can be used to recreate the database in Connection with a fulll
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 Reference Dictionary Tables / Views.
References [include References to FAQ, Troubleshooting Guide, And Current Issues
Articles from top tech docs or other relevance 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 releating 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.. genecord.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_PARETERS
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 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
