oracle 筆記(續1)

next_junction發表於2014-01-19
Oracle筆記
oracle工具如下：
Oracle Universal Installer(OUI):Used to install,upgrade,or remove software components，Based on a Java engine.
Oracle Database Configuration Assistant:A grphical user interface tool that interacts with the OUI,or can be used independently,to create,delete,or modify a database.
SQL*PLUS:A utility to access data in an Oracle database.
Oracle Enterprise Manager:A graphical interface used to administer,monitor,and tune one or more databases.
1.Oracle Universal Installer
2.Database Configuration Assistant.
3.Database Upgrade Assistant
4.Oracle Net Manager
5.Oracle Enterprise Manager
6.SQL*PLUS
7.Recovery Manager
8.Oracle Secure Backup
9.Data Pump
10.SQL*Loader

Starting the OUI:
To start Oracle Universal Installer on UNIX:$ ./runInstaller(必須在圖形介面執行，命令列介面執行不了，還有最好不要用root使用者安裝，新建一個oracle使用者去執行此程式。./ 是因為linux系統是在path找命令)
To start Oracle Universal Installer on NT:Start > Programs > Oracle Installation > Products > Universal Installer
Non-Interactive Installation(非互動式安裝，需要有一個互動檔案)。
Allows for no user interaction,Response files:Templates must be edited,Text files contain variables and values,Parameters are customized.
To start Universal Installer in non-interactive mode: ./runInstaller -responsefile myrespfile -silent.(在runInstaller同級目錄中有個response目錄，裡面有response檔案模版，可以參考)

Oracle DBCA：
You use the Oracle Database Configuration Assistant to :Create a database,Configure database options,Delete a database,Manage templates.
DB Administrator Users:
Users SYS and SYSTEM are create automatically,During database creation,Granted the DBA role.
User SYS,Owner of the database data dictionary,Default password:change_on_install(從9.2.0版本後就不設定預設口令了)
User SYSTEM,Owner of additional internal tables and views used by Oracle tools,default password:manager.

SQL*PLUS - IMPORTANT!!!!!!!!
An Oracle tool providing:Capability to interact with and manipulate the database,Ability to start up and shut down the database,create and run queries,add rows,modify data,and write customized reports.
A subset of the standard SQL language with specific add ons.
Connecting to SQL*PLUS:
$sqlplus /nolog
sql>connect / as sysdba
 Connected to an idle instance
sql>startup --啟動資料庫
sql>select * from v$sga_dynamic_components;
$lsncrl start --啟動監聽(一般情況都是先啟動監聽，在去啟動DB)

Initializetion Parameter Files:
Entries are specific to the instance being started.Two types of parameter:Explicit(顯示的):Having an entry in the file,Implicit(隱式的):No entry within the file,but assuming the Oracle default values.Multiple initialization parameter files can exist.Changes to entries int file take effect based on the type of initialization parameter file used:Static parameter file,PFILE,Persistent server parameter file,SPFILE.

sql>show parameter sga;--檢視parameter以sga開頭的

PFILE --initSID.ora:
Text file,Modified with an operating system editor,Modifications made manually,Changes take effect on the next start up,Only opened during instance start up,Default location is $ORACLE_HOME/dbs.
$env | grep ORACLE_HOME --檢視ORACLE_HOME環境變數
ORACLE_SID和SID(Site Identifier,站點標示符)
    tom大師語錄>>If you're unfamiliar with the term SID or ORACLE_SID,a full definition is called for.The SID is a site identifier.It and ORACLE_HOME(where the Oracle software is installed) are hashed together in UNIX  to create a unique key name for attaching an SGA.If your ORACLE_SID or ORACLE_HOME is not set correctly,you'll get the ORACLE NOT AVAILABLE error,since you can't attach to a shared memory segment that is indentified by this unique key.On Windows,shared memory isn't used in the same fashion as UNIX,but the SID is still important.You can have more than one database on the same ORACLE_HOME,so you need a way to uniquely identify each one,along with their configuration files.

init{ORACLE_SID}.ora引數檔案中的引數：db_name,db_block_size,control_file引數。
SPFILE-spfileSID.ora:Binary file,Maintained by the Oracle server,Always resides on the server side(其儲存在伺服器上，避免了pfile儲存在客戶端的弊端),Ability to make changes persistent across shut down and start up,Can self-tune parameter values,Can have Recovery Manager support backing up to the initialization parameter file.
Creating an SPFILE：
(1)sql > create spfile='$ORACLE_HOME/dbs/spfile{ORACLE_SID}.ora' from pfile='$ORACLE_HOME/dbs/init{ORACLE_SID}.ora';
(2)sql > create spfile from pfile;
可以在instance啟動前或者instance啟動後建立。
$string spife | more oracle --檢視二進位制檔案。

Modify parameters in spfile
Alter xtsystem set parameter=value --deferred引數是指下次啟動時候改變起作用。
sql>alter system set fast_start_mttr_target=250;--修改引數，記憶體和spfile引數檔案中此引數值都改變，預設是both。如果是pfile的話，只能改memory中的引數值。
sql>alter system set fast_start_mttr_target=250 comment='hello world';--comment選項是加註釋
sql>desc v$parameter;--儲存引數的v$檢視
sql>alter system set undo_tablespace=undo2;
sql>alter system reset undo_suppress_errors scope=both sid='*';--reset為預設值。注意引數選項，學著檢視聯機文件。
spfile和pfile比較：
1：An spfile can be backed-up with RMAN(RMAN cannot backup pfile)
2:Reduce human errors.The spfile is maintained by the server.Parameters are checked before changes are accepted.
3:Eliminate configuration problems(no need to have a local pfile if you want to start Oracle from a remote machine)
4:Easy to find --stored in a central location.

Order of precedence:spfileSID.ora>Default spfile(spfile.ora)>initSID.ora>default pfile
只能指定pfile啟動DB：startup pfile=$ORACLE_HOME/dbs/initDBA1.ora
如果想用spfile，可以在pfile檔案中寫：spfile=/database/startup/spfileDBA1.ora這樣就是通過pfile找到了spfile。如下：
$mv spfileding.ora test --移動當前資料夾中的spfile到test中
$rm -f initding.ora --刪除當前資料夾的pfile
$cp test/spfileding.ora ./spfileabc.ora --從test中copy spfile
$vi ding.ora--建立一個pfile
spfile=$ORACLE_HOME/dbs/spfileabc.ora--編輯pfile引用spfile
$sqlplus /nolog
sql>conn / as sysdba
sql>startup pfile=$ORACLE_HOME/dbs/ding.ora--這樣就以pfile啟動oracle instance了，所用的pfile中引用了spfile。

Starting Up a Database
shutdown nomount mount open 四個狀態(狀態不可逆，只進不退)
$sqlplus / as sysdba
sql> conn / as sysdba
sql>!ps -ef | grep oracle --這個時候可以看到啟動了一個連線程式
sql>!ipcs --可以看到沒有分配記憶體
sql>startup nomount--(讀取引數檔案，啟動instance(分配記憶體和啟動後臺程式)到nomount狀態)
sql>!ipcs--現在可以看到共享記憶體了，但是資料庫只是nomount狀態
sql>show parameter db_name;
sql>show parameter db_block_size;--到現在只能看到一部分引數
sql>alter database mount;--mount就是把一個資料庫和instance掛接起來，Oracle Database then reads the control files to get the names of the database's datafiles and redo log files.現在資料庫還是關閉狀態，只有管理員可以訪問控制，如做一些備份恢復等維護工作，普通使用者不能訪問。
sql>alter database open;--All files opened as described by the control file for this instance.具體過程去讀文件。

sql>alter database db01 mount;--from nomount to mount
sql>alter database db01 open read only;--啟動資料庫到只讀狀態

Use the startup command to restrict access to a database:
sql> startup restrict;--受限模式，特權使用者可以用
Use the alter system command to place an instance in restricted mode:
sql> alter system enable restricted session;--受限模式

sql>create user ding identified by ding;--建立使用者
sql>grant connect,resource to ding;--授權使用者
sql>alter system enable restricted session;--開啟受限模式
，這時普通使用者就連不上了
sql>select saddr,sid,serial#，username from V$session;--檢視session
sql>alter system kill session 'sid,serial#';--踢出使用者，其中sid和serial#應該替換為上面sql語句中查詢到的具體值。

開啟為只讀模式
startup mount.   alter database open read only.--這個模式中使用者只能查詢，不能修改
sql>startup mount;
sql>alter database open read only;--開啟為只讀模式

關閉資料庫：Close Database：寫sga資料到資料檔案和日誌檔案，關閉線上資料檔案和日誌檔案，at this point,the database is closed and inaccessible for normal operations.The control files remain open after a database is closed bu still mounted。Unmount a Database：這時instance還在。Shut Down an Instance：關閉例項，收回sga和終止後臺程式。

關閉資料庫：
shutdown abort：相當斷電
shutdown immdeiate：最常用，強制終止會話，事務，回滾和提交
shutdown transactional：等待事務提交。
shutdown normal：只是不允許新連線，會等待會話，等待事務，不強制checkpoint。
一致性關閉(乾淨關閉)：shutdown normal/transactional/immediate.
非一致性關閉(不乾淨關閉):shutdown abort / instance failure/startup force.
sql>select addr,status from v$transaction;--檢視當前系統有沒有活動的事務。

Diagnostic Files:
          Contain information about significant events encountered,Used to resolve problems,Used to better manage the database on a day-to-day basis.Several types exist:alertSID.log file,Background trace files,User trace files.
    Background>SID_processname_PID.trc(db01_qmn0_3783.trc)(每個程式都會有個trace檔案)
User Trace Files>SID_ora_PID.trc(db01_ora_3627.trc)
     Alert Log File>alertSID.log file:Records the commands,Records results of major events,Used for day-to-day operational information,Used for diagnosing database errors.Each entry has a time stamp associated with it,Must be managed by DBA,Location defined by BACKGROUND_DUMP_DEST.
Background Trace Files>
        Log errors detected by any background process,Are used to diagnose and troubleshoot errors.Create when a background process encounters an error.Location defined by BACKGROUND_DUMP_DEST.(有錯誤時候才寫相關程式trace檔案)
User trace files>
        produced by the user process,Can be generated by a server process,Contain statistics for traced SQL statements,Contain user error messages.Created when a user encounters user session errors.Location is defined by USER_DUMP_DEST.Size defined by MAX_DUMP_FILE_SIZE.
Enable/Disable User Tracing>
        (1)Session level:Using the alter session command:alter session set sql_trace=true(如果在當前會話執行了此語句，sql>show parameter sql_trace--&gt查詢到的值還是false，但是set語句已經執行成功了，只是查的時候不顯示true)
Executing DBMS procedure:dbms_system.set_sql_trace_in_session.
        (2)Instance level:Setting the initialization parameter:sql_trace=true.一般是在session上設定，如果在instance上設定的話，每個使用者都產生trace檔案這樣在使用者量大的時候會擠爆硬碟。一般不再instance上開啟sql_trace。

  如果spfile或者pfile丟了：可以通過alert日誌檔案建立引數檔案。把裡面的引數copy出來，該加單引號的加單引號，該加括號的加括號--這樣一個引數檔案就建立好了。
sql>select username,account_status from dba_users;--檢視所有使用者狀態。
sql>alter user hr account unlock;--解鎖hr使用者。
sql>alter user hr identified by hr;--設定密碼。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------(待續2)







來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29117696/viewspace-1072658/，如需轉載，請註明出處，否則將追究法律責任。

相關文章