oracle 11g r2 for windows 建库脚本

时间：2010-08-28 来源：brjl

安装完软件后，通过dbca建库，实例名是abc，相关内容如下：

文件:	abc.rar
大小:	6KB
下载:	下载

建库脚本默认保留在 C:\app\Administrator\admin\abc\scripts\目录下。 abc.bat文件： OLD_UMASK=`umask`
umask 0027
mkdir C:\app\Administrator\admin\abc\adump
mkdir C:\app\Administrator\admin\abc\dpdump
mkdir C:\app\Administrator\admin\abc\pfile
mkdir C:\app\Administrator\cfgtoollogs\dbca\abc
mkdir C:\app\Administrator\flash_recovery_area
mkdir C:\app\Administrator\flash_recovery_area\abc
mkdir C:\app\Administrator\oradata\abc
mkdir C:\app\Administrator\product\11.2.0\dbhome_1\database
umask ${OLD_UMASK}
set ORACLE_SID=abc
set PATH=%ORACLE_HOME%\bin;%PATH%
C:\app\Administrator\product\11.2.0\dbhome_1\bin\oradim.exe -new -sid ABC -startmode manual -spfile
C:\app\Administrator\product\11.2.0\dbhome_1\bin\oradim.exe -edit -sid ABC -startmode auto -srvcstart system
C:\app\Administrator\product\11.2.0\dbhome_1\bin\sqlplus /nolog @C:\app\Administrator\admin\abc\scripts\abc.sql
abc.sql文件： set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host C:\app\Administrator\product\11.2.0\dbhome_1\bin\orapwd.exe file=C:\app\Administrator\product\11.2.0\dbhome_1\database\PWDabc.ora force=y
@C:\app\Administrator\admin\abc\scripts\CloneRmanRestore.sql
@C:\app\Administrator\admin\abc\scripts\cloneDBCreation.sql
@C:\app\Administrator\admin\abc\scripts\postScripts.sql
@C:\app\Administrator\admin\abc\scripts\lockAccount.sql
@C:\app\Administrator\admin\abc\scripts\postDBCreation.sql
CloneRmanRestore.sql SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\app\Administrator\admin\abc\scripts\CloneRmanRestore.log append
startup nomount pfile="C:\app\Administrator\admin\abc\scripts\init.ora";
@C:\app\Administrator\admin\abc\scripts\rmanRestoreDatafiles.sql;
spool off

CloneRmanRestore.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\app\Administrator\admin\abc\scripts\cloneDBCreation.log append
Create controlfile reuse set database "abc"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'C:\app\Administrator\oradata\abc\SYSTEM01.DBF',
'C:\app\Administrator\oradata\abc\SYSAUX01.DBF',
'C:\app\Administrator\oradata\abc\UNDOTBS01.DBF',
'C:\app\Administrator\oradata\abc\USERS01.DBF'
LOGFILE GROUP 1 ('C:\app\Administrator\oradata\abc\redo01.log') SIZE 51200K,
GROUP 2 ('C:\app\Administrator\oradata\abc\redo02.log') SIZE 51200K,
GROUP 3 ('C:\app\Administrator\oradata\abc\redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="C:\app\Administrator\admin\abc\scripts\initabcTemp.ora";
Create controlfile reuse set database "abc"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'C:\app\Administrator\oradata\abc\SYSTEM01.DBF',
'C:\app\Administrator\oradata\abc\SYSAUX01.DBF',
'C:\app\Administrator\oradata\abc\UNDOTBS01.DBF',
'C:\app\Administrator\oradata\abc\USERS01.DBF'
LOGFILE GROUP 1 ('C:\app\Administrator\oradata\abc\redo01.log') SIZE 51200K,
GROUP 2 ('C:\app\Administrator\oradata\abc\redo02.log') SIZE 51200K,
GROUP 3 ('C:\app\Administrator\oradata\abc\redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "abc" open resetlogs;
exec dbms_service.delete_service('seeddata');
exec dbms_service.delete_service('seeddataXDB');
alter database rename global_name to "abc";
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\app\Administrator\oradata\abc\TEMP01.DBF' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
alter system disable restricted session;
connect "SYS"/"&&sysPassword" as SYSDBA
@C:\app\Administrator\product\11.2.0\dbhome_1\demo\schema\mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install C:\app\Administrator\product\11.2.0\dbhome_1\assistants\dbca\templates\example.dmp C:\app\Administrator\product\11.2.0\dbhome_1\assistants\dbca\templates\example01.dfb C:\app\Administrator\oradata\abc\example01.dbf C:\app\Administrator\admin\abc\scripts\ "'SYS/&&sysPassword as SYSDBA'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup restrict pfile="C:\app\Administrator\admin\abc\scripts\initabcTemp.ora";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT AL16UTF16;
alter user sys account unlock identified by "&&sysPassword";
alter user system account unlock identified by "&&systemPassword";
alter system disable restricted session;

postScripts.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\app\Administrator\admin\abc\scripts\postScripts.log append
@C:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set current_schema=ORDSYS;
@C:\app\Administrator\product\11.2.0\dbhome_1\ord\im\admin\ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"&&sysPassword" as SYSDBA
alter user CTXSYS account unlock identified by &&sysPassword;
connect "CTXSYS"/"&&sysPassword"
@C:\app\Administrator\product\11.2.0\dbhome_1\ctx\admin\defaults\dr0defdp.sql;
@C:\app\Administrator\product\11.2.0\dbhome_1\ctx\admin\defaults\dr0defin.sql "SIMPLIFIED CHINESE";
connect "SYS"/"&&sysPassword" as SYSDBA
alter user CTXSYS password expire account lock;
connect "SYS"/"&&sysPassword" as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;

lockAccount.sql

SET VERIFY OFF
set echo on
spool C:\app\Administrator\admin\abc\scripts\lockAccount.log append
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
   sys.dbms_assert.enquote_name(
   sys.dbms_assert.schema_name(
   item.USERNAME),false) || ' password expire account lock' ;
END LOOP;
END;
/
spool off

postDBCreation.sql SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\app\Administrator\admin\abc\scripts\postDBCreation.log append
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\spfileabc.ora' FROM pfile='C:\app\Administrator\admin\abc\scripts\init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
spool off
exit;
init.ora文件 ##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=abcXDB)"

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=C:\app\Administrator
memory_target=854589440

###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\app\Administrator\admin\abc\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Database Identification
###########################################
db_domain=""
db_name=abc

###########################################
# File Configuration
###########################################
control_files=("C:\app\Administrator\oradata\abc\control01.ctl", "C:\app\Administrator\flash_recovery_area\abc\control02.ctl")
db_recovery_file_dest=C:\app\Administrator\flash_recovery_area
db_recovery_file_dest_size=4039114752

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

###########################################
# Network Registration
###########################################
#local_listener=LISTENER_ABC

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Cache and I/O
###########################################
db_block_size=8192

initabcTemp.ora文件 ##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=abcXDB)"

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=C:\app\Administrator
memory_target=854589440

###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\app\Administrator\admin\abc\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Database Identification
###########################################
db_domain=""
db_name=abc

###########################################
# File Configuration
###########################################
control_files=("C:\app\Administrator\oradata\abc\control01.ctl", "C:\app\Administrator\flash_recovery_area\abc\control02.ctl")
db_recovery_file_dest=C:\app\Administrator\flash_recovery_area
db_recovery_file_dest_size=4039114752

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

###########################################
# Network Registration
###########################################
#local_listener=LISTENER_ABC

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Cache and I/O
###########################################
db_block_size=8192

_no_recovery_through_resetlogs=true