10手动脚本创建数据库

1）设置ORACLE_SID

export ORACLE_SID=mydb

2）用SYS建立DBA角色用户mydba

sqlplus / as sysdba     ----以dba角色进入
show user                         ----查看当前角色
create user mydba identified by oracle;
grant dba to mydba
SQL> select username,account_status from dba_users where username like 'MYDBA%';

USERNAME                                             ACCOUNT_STATUS
------------------------------ --------------------------------
MYDBA                                                    OPEN

Elapsed: 00:00:00.08
SQL>

3）pfile文件

$ORACLE_HOME/dbs/init.ora    ----模板文件

4）连接数据库

$ SQLPLUS /nolog
CONNECT SYS/password AS SYSDBA

5）创建SPFILE

CREATE SPFILE='/data/oracle/jack/spfilemydb.ora' FROM
        PFILE='/data/oracle/jack/init.ora';
shutdown   ----下次会使用本次创建的spfile

6）启动到nomount状态，创建数据库

CREATE DATABASE mydb
     USER SYS IDENTIFIED BY pz6r58
     USER SYSTEM IDENTIFIED BY y1tz5p
     LOGFILE GROUP 1 ('/data/oracle/oracle/oradata/mydb/redo01.log') SIZE 10M,
                     GROUP 2 ('/data/oracle/oracle/oradata/mydb/redo02.log') SIZE 10M,
                     GROUP 3 ('/data/oracle/oracle/oradata/mydb/redo03.log') SIZE 10M
     MAXLOGFILES 5
     MAXLOGMEMBERS 5
     MAXLOGHISTORY 1
     MAXDATAFILES 100
     MAXINSTANCES 1
     CHARACTER SET US7ASCII
     NATIONAL CHARACTER SET AL16UTF16
     DATAFILE '/data/oracle/oracle/oradata/mydb/system01.dbf' SIZE 325M REUSE
     EXTENT MANAGEMENT LOCAL
     SYSAUX DATAFILE '/data/oracle/oracle/oradata/mydb/sysaux01.dbf' SIZE 325M REUSE
     DEFAULT TABLESPACE tbs_1
     DEFAULT TEMPORARY TABLESPACE tempts1
            TEMPFILE '/data/oracle/oracle/oradata/mydb/temp01.dbf'
            SIZE 20M REUSE
     UNDO TABLESPACE undotbs
            DATAFILE '/data/oracle/oracle/oradata/mydb/undotbs01.dbf'
            SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

8）增加表空间

-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
         DATAFILE '/data/oracle/oracle/oradata/mydb/users01.dbf'
         SIZE 25M REUSE AUTOEXTEND ON NEXT    1280K MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
         DATAFILE '/data/oracle/oracle/oradata/mydb/indx01.dbf'
         SIZE 25M REUSE AUTOEXTEND ON NEXT    1280K MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;

9）运行数据字典视图

CONNECT SYS/password AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql

10）其他脚本运行

11）备份数据库

EXP/IMP  
EXPDP/IMPDP