Oracle手工建庫指南

 [oracle@orcl ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
EDITOR=vim
NLS_LANG=american_america.AL32UTF8
NLS_DATE_FORMAT=YYYY-MM-DD:HH24:MI:SS
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$PATH
export PATH ORACLE_SID ORACLE_BASE ORACLE_HOME EDITOR NLS_LANG NLS_DATE_FORMAT
alias sqlplus='/usr/local/rlwrap/bin/rlwrap sqlplus'
alias rman='/usr/local/rlwrap/bin/rlwrap rman'

[root@orcl ~]# su - oracle
[oracle@orcl ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ orapwd file=orapworcl password=123456 entries=5[oracle@orcl ~]$ cat $ORACLE_HOME/dbs/initorcl.ora
*.db_name=orcl
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_unique_name='orcl'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=1500
*.control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control012ctl','/u01/app/oracle/oradata/orcl/control03.ctl')
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

[oracle@orcl ~]$ cat create_db.sql 
CREATE DATABASE orcl 
      USER SYS IDENTIFIED BY "123456" 
      USER SYSTEM IDENTIFIED BY "123456"
      LOGFILE 
      GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01_1.dbf','/u01/app/oracle/oradata/orcl/redo01_2.dbf') SIZE 150M,
      GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02_1.dbf','/u01/app/oracle/oradata/orcl/redo02_2.dbf') SIZE 150M,
      GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03_1.dbf','/u01/app/oracle/oradata/orcl/redo03_2.dbf') SIZE 150M
      MAXLOGFILES 10
      MAXLOGMEMBERS 5
      MAXLOGHISTORY 1
      MAXDATAFILES 100
      MAXINSTANCES 1
      CHARACTER SET AL32UTF8
      NATIONAL CHARACTER SET AL16UTF16
      DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 2000M REUSE EXTENT MANAGEMENT LOCAL
      SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 800M REUSE
      DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M REUSE
      UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs1.dbf' SIZE 2000M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

[oracle@orcl ~]$ env |grep SID
ORACLE_SID=orcl
[oracle@orcl ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 29 11:55:01 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.

SQL> startup nomount
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size             121637108 bytes
Database Buffers           41943040 bytes
Redo Buffers                2973696 bytes

SQL> @/home/oracle/create_db.sql;
Database created

[oracle@orcl ~]$ ls -lh /u01/app/oracle/oradata/orcl/
total 5.6G
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control012ctl
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control01.ctl
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control03.ctl
-rw-r----- 1 oracle oinstall 151M Sep 29 14:25 redo01_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:25 redo01_2.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo02_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo02_2.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo03_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo03_2.dbf
-rw-r----- 1 oracle oinstall 801M Sep 29 14:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 2.0G Sep 29 14:25 system01.dbf
-rw-r----- 1 oracle oinstall  21M Sep 29 14:25 temp01.dbf
-rw-r----- 1 oracle oinstall 2.0G Sep 29 14:25 undotbs1.dbf

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;
PL/SQL procedure successfully completed.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size             138414324 bytes
Database Buffers           25165824 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

[oracle@orcl ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 29 14:52:27 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1290917246)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1290917246       CURRENT 1          2011-09-29:14:21:50

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    2000     SYSTEM               ***     /u01/app/oracle/oradata/orcl/system01.dbf
2    2000     UNDOTBS1             ***     /u01/app/oracle/oradata/orcl/undotbs1.dbf
3    800      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 20          /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> create tablespace users datafile '/u01/app/oracle/oradata/orcl/users01.dbf' size 500M 
  2  autoextend on maxsize 5G;
Tablespace created

SQL> alter database default tablespace users;
Database altered