aix下oracle資料庫管理

$whoami

oracle

$cd $oracle_home/dbs

$ls

可以看到引數檔案initorcl.ora,spfileorcl.ora.

$cat spfileorcl.ora |more --檢視伺服器引數檔案

$cat initorcl.ore | pg

PFILE檔案initorcl.ora不存在

SQL>create pfile from spfile;

$ cat spfileorcl.ora | pg

**********************

SQL>show parameter spfile;

資料庫透過spfile啟動.

SQL>alter system set sga_max_size =620M scope=spfile;

SQL>shutdown immediate;

SQL>show parameter sga_max_size;

引數被修改

注意:如果資料庫是透過PFILE啟動,則不能用alter system 調整引數!

******************引數檔案的操作pfile和spfile互相可以建立

SQL> create spfile = '/db/oracle/spfileorcl.ora' from

pfile = '/db/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

SQL>create pfile = '/db/oracle/initorcl.ora' from

spfile='/db/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora';

*****************

在Unix無需建立例項,只需指定環境變數,然後輸出.所以需要修改oracle使用者的概要檔案/home/oracle/.profile,內容如下：

ORACLE_SID=sdzy

ORACLE_BASE=/db/oracle

ORACLE_HOME=$ORACLE_HOME/product/10.2.0/db_1

PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH

重新以oracle使用者登陸，檢查其環境變數

$whoami

oracle

$

$env|grep ORACLE

--建立口令檔案,名為orapwsdzy

$orapwd file = /db/product/10.2.0/db_1/dbs/orapwsdzy password=oracle

$

$ls /db/product/10.2.0/db_1/dbs/orapwsdzy

/db/product/10.2.0/db_1/dbs/orapwsdzy

建立完畢

--建立目錄結構,用來存放各種資料庫檔案

$mkdir -p /db/admin/sdzy/adump

$mkdir -p /db/admin/sdzy/bdump

$mkdir -p /db/admin/sdzy/cdump

$mkdir -p /db/admin/sdzy/pfile

$mkdir -p /db/admin/sdzy/udump

$mkdir -p /flash_recovery_area

$mkdir -p /db/oradata/sdzy

--建立所需的引數檔案

$cd /db/admin/sdzy/pfile

$

$touch init.ora --建立檔案

$

使用vi工具將下面內容新增到init.ora中;

db_name=sdzy

db_domain=""

sga_target=285212672

pga_aggregate_target=94371840

db_block_size=8192

db_file_multiblock_read_count=16

open_cursors=300

processes=150

background_dump_dest=/db/admin/sdzy/bdump

core_dump_dest=/db/admin/sdzy/cdump

user_dump_dest=/db/admin/sdzy/udump

audit_file_dest=/db/admin/sdzy/adump

control_file = ("/db/oradata/sdzy/control01.ctl","/db/oradata/sdzy/control02.ctl")

db_recovery_file_dest = /flash_recovery_area

db_recovery_file_dest_size=2147483648

log_archive_format =%t_%s_%r.dbf

compatible=10.2.0.1.0

remote_login_passwordfile=EXCLUSIVE

undo_management=AUTO

undo_tablespace=UNDOTBS1

***啟動例項建立資料庫

$sqlplus "/as sysdba"

SQL> startup nomount pfile=/db/admin/sdzy/pfile/init.ora

CREATE DATABASE SDZY

DATAFILE 'db/oradata/sdzy/system01.dbf' SIZE 300M REUSE

AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE 'db/oradata/sdzy/sysaux01.dbf' SIZE 120M REUSE

AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEND

SMALLFILE DEFAULT TEMPORARY TABLESPACE

TEMP TEMPFILE '/db/oradata/sdzy/temp01.dbf' SIZE 20M RRUSE

AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/db/oradata/sdzy/undotbs01.dbf' SIZE 200 REUSE

AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('/db/oradata/sdzy/redo01.log') SIZE 51200K,

GROUP 2('/db/oradata/sdzy/redo02.log') SIZE 51200K,

GROUP 3('/db/oradata/sdzy/redo03.log') SIZE 51200K

USER SYS INENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle;

SQL> host ls /db/oradata/sdzy

--建立表空間USERS,並設定為資料庫預設表空間

SQL>CREATE SMALLFILE TABLESPACE USERS LOGGING DATAFILE 'db/oradata/sdzy/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTEND MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL>ALTER DATABASE DEFAULT TABLESPACE USERS;

--安裝資料字典檢視

SQL> @ /db/product/10.2.0/db_1/rdbms/admin/catalog.sql;

SQL> @ /db/product/10.2.0/db_1/rdbms/admin/catproc.sql;

--建立PL/SQL資源集

SQL> connect system/oracle

SQL>@ /db/product/10.2.0/db_1/sqlplus/admin/pupbld.sql;

SQL>connect system/oracle

SQL>@ /db/product/10.2.0/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;

--建立資料庫控制器服務

$emca -config dbcontrol db -repos create

一套精簡的資料庫建立完成

[@more@]