oracle 隨筆

section 0
入口：Administrator's Guide->2 Creating an Oracle Database->Creating the Database
1.設定SID
export ORACLE_SID=devdb
echo $ORACLE_SID
2.建立目錄
echo $ORACLE_BASE
mkdir -p $ORACLE_BASE/oradata/devdb
mkdir -p $ORACLE_BASE/admin/devdb/{a,b,c,u}dump
mkdir -p $ORACLE_BASE/archivelog/devdb
3.建立密碼檔案
cd $ORACLE_HOME/dbs
orapwd file=orapwdevdb password=oracle entries=30
ls
4.建立引數檔案
考試時，先看$ORACLE_HOME/dbs 有沒有引數檔案模板，如果有則按如下方式生成你需要的引數檔案。如果沒有，則可
到文件中查詢引數檔案範例。位置如下：
入口：Administrator's Guide->2 Creating an Oracle Database->Understanding Initialization Parameters
cat init.ora |grep -v ^$ |grep -v ^# >>initdevdb.ora
ls
用vi編輯器編輯initdevdb.ora引數檔案：
刪掉
db_block_buffers = 100 # SMALL
shared_pool_size = 3500000 # SMALL
修改
db_name = devdb
processes = 150 //Section 1的1.2.2題
control_files =
(/u01/oracle/oradata/devdb/control01.ctl,/u01/oracle/oradata/devdb/control02.ctl,/u01/oracle/oradata/devdb/control03.ctl)
新增
undo_management = AUTO
sessions = 300
sga_target = 400M
sga_max_size = 400M
log_archive_dest_1 = "LOCATION=/u01/archivelog/PROD"
log_archive_dest_state_1 = enable
注意：根據機器的配置，設定sga_target、sga_max_size。設定太小，影響做題速度。
5.生成spfile
sqlplus / as sysdba
startup nomount pfile='/u01/oracle/product/10.2.0/db_1/dbs/initdevdb.ora'
create spfile from pfile;
shutdown abort;
6.手動建庫
startup nomount;
將Step 7: Issue the CREATE DATABASE Statement的語句複製，去掉DEFAULT TABLESPACE tbs_1，修改後內容如下。主
要修改的是檔案目錄
CREATE DATABASE devdb
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/oracle/oradata/devdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oracle/oradata/devdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/oracle/oradata/devdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/oracle/oradata/devdb/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/oracle/oradata/devdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oracle/oradata/devdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/oracle/oradata/devdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

7.啟用歸檔
shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
archive log list;

Section1：資料庫和網路設定

問題1 資料庫設定、Undo 管理
1.1 執行基本指令碼，配置資料庫
入口：Administrator's Guide->2 Creating an Oracle Database->Creating the Database->Step 9: Run Scripts to
Build Data Dictionary Views
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
注意：如果時間夠用，可以先做個冷備份
SQL> shutdown immediate;
[oracle@server01 devdb]$ cd /u01/oracle/oradata/devdb
[oracle@server01 devdb]$ cd ..
[oracle@server01 devdb]$ tar -czvf ../devdb.gz *
冷備之後，別忘記開啟資料庫
sqlplus / as sysdba
startup
1.2 設定UNDO 自動管理
1.2.1 調整undo 使快照保留90 分鐘
解答1.2.1：調整undo表空間
show parameters undo
alter system set undo_retention=5400 scope=both;
select tablespace_name,retention from dba_tablespaces;
alter tablespace undotbs RETENTION GUARANTEE;

 

------------------------------------
建立臨時表空間組
select file_name from dba_temp_files;
create temporary tablespace temp1 tempfile '/u01/oracle/oradata/devdb/temp01.dbf' size 10m tablespace group temp_gp;
create temporary tablespace temp2 tempfile '/u01/oracle/oradata/devdb/temp02.dbf' size 10m tablespace group temp_gp;
alter database default temporary tablespace temp_gp;
ALTER TABLESPACE temp1 TABLESPACE GROUP temp_gp;
ALTER TABLESPACE temp1 TABLESPACE GROUP '';

建立表空間example
select file_name from dba_data_files;
create tablespace example datafile '/u01/oracle/oradata/devdb/example01.dbf' size 400m autoextend on next 1m
extent management local uniform size 1m segment space management manual;
建立索引表空間indx
create tablespace INDX datafile '/u01/oracle/oradata/devdb/indx01.dbf' size 40m;
建立TOOLS表空間
create tablespace TOOLS datafile '/u01/oracle/oradata/devdb/tools01.dbf' size 10m;
建立USERS表空間
select * from database_properties;
create tablespace users datafile '/u01/oracle/oradata/devdb/users01.dbf' size 48m
autoextend on next 4m
extent management local uniform size 4m;
alter database default tablespace users;
建立表空間OLTP
create tablespace oltp datafile '/u01/oracle/oradata/devdb/oltp01.dbf' size 48m
autoextend on next 2m
extent management local uniform size 2m
segment space management auto;

建立大資料檔案表空間
create bigfile tablespace dss datafile '/u01/oracle/oradata/devdb/dss01.dbf' size 48m
autoextend on next 2m
maxsize 4T;
日誌檔案管理
冗餘redo日誌
select group#,status member from v$logfile;
select gourp#,bytes/1024/1024 mb,status from v$log;
向現有日誌組新增成員
alter database add logfile member '/u01/oracle/oradata/devdb/redo102.log' to group 1;
alter database add logfile member '/u01/oracle/oradata/devdb/redo202.log' to group 2;
alter database add logfile member '/u01/oracle/oradata/devdb/redo303.log' to group 3;
alter database add logfile group 4 ('/u01/oracle/oradata/devdb/redo4.log','/u01/oracle/oradata/devdb/redo402.log') size 100m;
方案二刪除現有redo，重新新增
select group#,status,member,bytes/1024/1024 mb from v$log;
刪除狀態為inactive 的日誌組
切換日誌
alter system switch logfile;
刪除日誌組
alter database drop logfile group 2;
alter database drop logfile group 3;
刪除之後，磁碟檔案並未刪除，需要在作業系統上刪除對應的redo檔案。
資料庫至少需要保留兩組redo日誌，因此需要在刪除日誌組的同時，新增符合題目要求的日誌組
新增日誌組
alter database logfile group 2('/u01/oracle/oradata/devdb/redo21.log','/u01/oracle/oradata/devdb/redo22.log') size 100m;
alter database logfile group 3('/u01/oracle/oradata/devdb/redo31.log','/u01/oracle/oradata/devdb/redo32.log') size 100m;
冗餘控制檔案
1、建立靜態引數檔案
SQL> create pfile='/u01/oradata/PROD/initPROD.ora' from spfile;
File created.
2、關閉資料庫
3、複製控制檔案到多個磁碟
4、修改靜態引數檔案的control_files部分，指定多個控制檔案的位置。
5、用靜態引數檔案啟動資料庫
SQL> startup nomount pfile='/u01/oradata/PROD/initPROD.ora';
SQL> show parameters control;
6、建立動態資料庫引數，並用動態資料庫引數檔案啟動資料庫
SQL> create spfile from pfile='/u01/oradata/PROD/initPROD.ora';
File created.
SQL> shutdown immediate;
SQL> startup
SQL> show parameters pfile;

收集資料庫統計資訊
exec dbms_stats.gather_database_stats(degree=>5);
設定UTL_FILE_DIR引數
alter system set UTL_FILE_DIR='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;