数据库

img llmmysun

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

发表于2004/11/4 11:21:00  1122人阅读

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

阅读全文
0 0

相关文章推荐

img
取 消
img