但是 PUBLIC 角色有某些權限是具有威脅性的，欲查詢指派給 PUBLIC 的權限有哪些，可用以下查詢
SQL> select count(*) from dba_tab_privs where grantee='PUBLIC';
2017年6月24日 星期六
ORACLE 的 PUBLIC 權限控管
ORACLE 的 PUBLIC ROLE 預設是指派給每個帳號 
SQL> select table_name from dba_tab_privs where grantee='PUBLIC' and privilege='EXECUTE' and table_name like 'UTL%'; 
ORACLE 密碼檔有哪些帳號?
資料庫的密碼檔位置在 
如果將 SYSDBA 和 SYSOPER 授權給其他使用者，授權以後可以重新檢視上述檔案，會看到新增的使用者 
查詢後可看到新增兩個帳號 
$ORACLE_HOME/dbs/orapwSID
例如: orapwdsedb (SID=SEDB)
密碼檔有哪些帳號?
select * from V$PWFILE_USERS;
2017年6月13日 星期二
ORACLE NET 的設定
ORACLE的網路設定有以下重要檔案 
動態: 將參數定義於資料庫的系統參數內，參數包括 local_listner, instance_name, service_names。 
或是 
SERVER SIDE: listener.ora, sqlnet.ora(optional)
CLIENT SIDE: tnsname.ora, sqlnet.ora(optional)
上述檔案預設位置會在 $ORACLE_HOME/network/admin， 或是在(作業)系統變數 TNS_ADMIN 所指定之路徑
LISTENER 必須註冊(REGISTER)所連接的 INSTANCE，註冊方式分為靜態(STATIC)與動態(DYNAMIC)
靜態: 將 SID 或 SERVICE_NAME 寫在LISTENER.ORA
靜態參數的缺點，是無法確定INSTANCE是否正常可連接。在靜態註冊的系統哩，這個參數無任何值
設定動態註冊步驟如下:
2017年6月9日 星期五
ORACLE Enterprise Manager (EM) 的設定與啟動
Database Control 透過EM 來進行管理，EM的設定步驟如下:
(1)設定參數(db01, db02)
/usr/local/bin/setsedb ORACLE_SID , ORACLE_UNQNAME , ORACLE_HOSTNAME /etc/hosts
(2)設定ORACLE Net(db01, db02)
/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 設定完成以後，使用 lsnrctl start啟動db01及db02的監聽器
(3)刪除舊有的設定(db01)
emca -deconfig dbcontrol db -repos drop
(4)建立新的設定
emca -config dbcontrol db -repos create
有下列參數必須設定妥當:(透過執行SCRIPT: setsedb)
ORACLE_SID
ORACLE_UNQNAME (透過執行 select name, db_unique_name from v$database; 取得 db_unique_name )
ORACLE_HOSTNAME (即是 HOSTNAME)
【PRIMARY (DB01)設定】
主機部分(HOSTNAME)，設定於/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.80.136 db01
ORACLE設定檔，位置在 /usr/local/bin/setsedb
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=sedb; export ORACLE_SID
ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace; export ORACLE_ALERT
NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG
NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT
ORACLE_UNQNAME=prisedb;
ORACLE_HOSTNAME=$HOSTNAME;
ORA_NLS33=$ORACLE_HOME/nls/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ucb:/etc:.:$PATH
export PATH
ORACLE監聽器，檔案在 /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prisedb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = sedb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
【STANDBY (DB02)設定】
主機部分(HOSTNAME)，設定於/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.80.133 db02
ORACLE設定檔，位置在 /usr/local/bin/setsedb
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=sedb; export ORACLE_SID
ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace; export ORACLE_ALERT
NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG
NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT
ORACLE_UNQNAME=stdsedb;
ORACLE_HOSTNAME=$HOSTNAME;
ORA_NLS33=$ORACLE_HOME/nls/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ucb:/etc:.:$PATH
export PATH
ORACLE監聽器，檔案在 /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdsedb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = sedb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
【PRIMARY EM 設定】
【刪除舊有的設定】
[oracle@db01 j2ee]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Jun 8, 2017 5:56:06 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: sedb
Listener port number:
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jun 8, 2017 5:57:48 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/prisedb/emca_2017_06_08_17_56_05.log.
Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Jun 8, 2017 5:57:51 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 8, 2017 5:57:51 PM
【建立新的設定】
[oracle@db01 j2ee]$ emca -config dbcontrol db -repos create
STARTED EMCA at Jun 8, 2017 6:03:46 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: sedb
Listener port number: 1521
Listener ORACLE_HOME [ /oracle/app/oracle/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ db01
Listener ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ sedb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jun 8, 2017 6:04:30 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/prisedb/emca_2017_06_08_18_03_46.log.
Jun 8, 2017 6:04:31 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jun 8, 2017 6:13:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jun 8, 2017 6:13:57 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Jun 8, 2017 6:15:08 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jun 8, 2017 6:15:13 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Jun 8, 2017 6:15:13 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Jun 8, 2017 6:15:34 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Jun 8, 2017 6:15:34 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jun 8, 2017 6:16:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jun 8, 2017 6:16:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jun 8, 2017 6:17:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 8, 2017 6:17:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://db01:1158/em <<<<<<<<<<<
Jun 8, 2017 6:17:25 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /oracle/app/oracle/product/11.2.0/dbhome_1/db01_prisedb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 8, 2017 6:17:25 PM
【設定DB CONTROL CONSOLE】
上述設定完成以後，執行 emctl start dbconsole
但是必定要在前述設定皆完成以後再執行
先執行的結果
[oracle@db01 j2ee]$ emctl start dbconsole
OC4J Configuration issue. /oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_db01_prisedb not found.
【執行成功，會產生的資料夾】
2017年6月7日 星期三
ORACLE的參數檔: 執行個體中的參數
參數檔可以透過以下VIEW查詢
執行中INSTANCE的參數(記憶體中)： V$PARAMETER
其結構如下:
DISK儲存的靜態參數(檔案為SPFILE{$SID}.ora，例如，SID為SEDB，則檔案為SPFILESEDB.ORA)： V$SPPARAMETER
其結構如下:
查詢【基本參數Basic Parameter】的方法：
select name, value from v$parameter where isbasic='TRUE' order by name;
查詢SPFILE和記憶體中的基本參數(但是SPPARAMETER的欄位較少)：
select s.name, s.value
from v$spparameter s join v$parameter p on s.name=p.name
where p.isbasic='TRUE' order by name;
欲修改靜態參數，需使用ALTER SYSTEM，並且在命令後面加上改變範圍，將改變範圍設為SPFILE，或是BOTH(預設值)，例如：
alter system set log_buffer=6m scope=both; 或是
alter system set log_buffer=6m scope=SPFILE;
與SESSION有關的參數和設定
與SESSION 有關的VIEW為 NLS_SESSION_PARAMETERS
查詢所有與SESSION有關的參數
select * from nls_session_parameters
例如，欲更改幣值
ALTER SESSION set NLS_CURRENCY='GBP';
TO_DATE ORA-01843:not a valid month 錯誤處理
例如，在筆電上，執行 
SQL> alter session set nls_date_language='american'; 
然後，再執行 
select to_date('25-DEC-2010') from dual;
ORA-01843: 不是有效的月份
這個問題的本質是系統不能識別英文的月簡寫，而能識別中文。
Oracle系統的語言配置主要保存在V$NLS_PARAMETERS數據字典視圖中。查詢該視圖關於語言的設置值。如下：
SQL> select * from v$nls_parameters where parameter like '%DATE%';
Session altered
修改後，參數的日期的語言設定更改如下:
select to_date('25-DEC-2010') from dual;
結果就正常了
訂閱：
文章 (Atom)