oracle 基本技巧總結

jack_gogo 12年前發布 | 77K 次閱讀 pfile spfile Oracle 數據庫服務器 oracle ， awr tabelspace.sga

/**************表空間管理******************、

create  tablespace "traineeManage" datafile 'E:\app\jack\oradata\orcl\trainmanage' size 100M autoextend on next 1 M
 maxsize unlimited logging extent management local segment space management auto;

create smallfile temporary tablespace "traineeManageSys_Temp" tempfile 'F:\trainManageSys\data\traineeManageSys_temp.ldf'
 size 100m autoextend on next 1 m maxsize unlimited extent management local uniform size 1m;

alter database default tablespace traineeManage;//創建默認表空間

 drop tablespace "traineeManage" including contents and datafiles;
 drop tablespace "traineeManage_Temp" including contents and datafiles;

 

/****************用戶管理************************/

create user 用戶名 identified by 密碼 default tablespace "traineeManageSys" temporary tablespace
 "traineeManageSys_Temp" profile default account unlock;

drop user 用戶名;

 

/*****************授權管理**************/

grant connect,dba,resource to 用戶名;
  grant unlimited tablespace to 用戶名;

 

/*****************創建備份目錄并且賦予相應權限*****************、

create directory our_dir （目錄名）as 'F:\trainManageSys\data\backup';（指定路徑）
  grant read,write on directory our_dir to our;

 

/***********創建自動增長************************/

create sequence T_resgist_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
 
 create trigger tri_res before insert on T_resgistration_way
 for each row when (new.resgistion_way_id is null)
   begin
      select T_resgist_seq.Nextval into:new.resgistion_way_id from dual;
    end;

/**************oralce的啟動和關閉***********/

startup;

SQL>shutdown normal
SQL>shutdown transactional
SQL>shutdown immediate
SQL>shutdown abort

 

/****************pfile ,spfile管理**************/

  ##用生成對應SID的spfile生成pfile，生成的pfile位置：

  $ORACLE_HOME/dbs/init$ORACLE_SID.ora
  SQL> create pfile from spfile;

-------------------------------------------------------------------
##自己指定生成文件的位置
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;

 

/***********半小時采集一次，采集信息保存9天*****************/

exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>9*24*60);
/******取消自動收集**************/

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0,retention=>9*24*60);
注：10g，11g默認是自動開啟awr信息收集的，會對系統有一定的影響（很小）；如果要關閉awr信息收集，只需設置interval參數為0即可。但interval設0后，AWR報告無法生成;注：報告生成在$ORACLE_HOME/rdbms/admin/目錄下
 
/****************修改sga參數*****************/
 
修改sga的原則是：
sga_target < sga_max_size;
sga_target 加上pga等進程占用的內存必須小于機器物理內存（像我的機器內存是4G）
 
修改代碼如下：
alter system set sga_max_size = 2048m scope=spfile;
aleter system set sga_target=2048m scope=spfile;
##修改SGA的相關參數，只能是spfile，然后重啟數據庫。不能直接scope=both!
 
/*******************查詢oracle配置參數************************/
show parameter；
具體查詢某個參數，只需輸入具體參數即可，如查詢sga-target
show parameter sga_target;
SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1G
sga_target                           big integer 1G
SQL>
/*************redo,logfile管理**************************/
日志切換時間間隔查詢
SELECT to_char(b.first_time, 'YYYY-MM-DD HH24:MI:SS') as swtich_time, (b.first_time - a.first_time) * 24 as "switch_interval(hr)" FROM v$log_history a, v$log_history b WHERE a.SEQUENCE# + 1 = b.SEQUENCE# ORDER BY SWTICH_TIME;

 

SQL> SELECT to_char(b.first_time, 'YYYY-MM-D
as "switch_interval(hr)" FROM v$log_history
TICH_TIME;

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-01-09 10:13:10          .035277778
2012-01-09 10:13:24          .003888889
2012-01-09 10:14:09               .0125
2012-01-09 10:14:49          .011111111
2012-01-09 10:21:50          .116944444
2012-01-09 17:24:32               7.045
2012-01-11 14:22:52          44.9722222
2012-01-11 15:00:21          .624722222
2012-01-13 16:53:52          49.8919444
2012-01-31 09:49:26          424.926111
2012-02-03 09:07:15          71.2969444

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-03 13:48:05          4.68055556
2012-02-10 09:14:11             163.435
2012-02-13 10:02:22          72.8030556
2012-02-14 08:34:13          22.5308333
2012-02-15 09:49:32          25.2552778
2012-02-16 08:39:12          22.8277778
2012-02-16 17:35:09              8.9325
2012-02-17 08:35:26          15.0047222
2012-02-20 08:35:05          71.9941667
2012-02-21 08:24:16          23.8197222
2012-02-22 08:34:42          24.1738889

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-22 11:38:55          3.07027778
2012-02-22 13:56:41          2.29611111
2012-02-23 08:32:39          18.5994444
2012-02-23 14:07:11          5.57555556
2012-02-24 08:22:57          18.2627778
2012-02-29 17:10:28          128.791944
2012-02-29 17:40:30          .500555556
2012-03-05 09:25:31          111.750278
2012-03-05 09:50:44          .420277778
2012-03-05 10:51:01          1.00472222
2012-03-05 13:19:41          2.47777778

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-06 08:22:26          19.0458333
2012-03-06 09:48:05              1.4275
2012-03-06 15:05:24          5.28861111
2012-03-07 08:40:33          17.5858333
2012-03-08 08:42:51          24.0383333
2012-03-09 08:25:47          23.7155556
2012-03-09 08:57:53                .535
2012-03-13 14:51:01          101.885556
2012-03-14 08:26:40          17.5941667
2012-03-15 09:41:35          25.2486111
2012-03-16 08:21:46          22.6697222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-19 08:38:25             72.2775
2012-03-19 16:22:39          7.73722222
2012-03-20 08:27:44          16.0847222
2012-03-22 08:30:07          48.0397222
2012-03-23 08:28:48          23.9780556
2012-03-23 16:46:08          8.28888889
2012-03-26 08:29:54          63.7294444
2012-03-26 13:04:32          4.57722222
2012-03-26 14:11:44                1.12
2012-03-27 08:26:03          18.2386111
2012-03-28 09:08:28          24.7069444

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-29 09:26:45          24.3047222
2012-04-05 15:43:23          174.277222
2012-04-05 16:13:23                  .5
2012-04-06 08:55:31          16.7022222
2012-04-10 09:30:57          96.5905556
2012-05-16 17:15:09          871.736667
2012-05-17 08:42:32          15.4563889

已選擇62行。--------
##前一百條記錄（反序）
SELECT to_char(b.first_time, 'YYYY-MM-DD HH24:MI:SS') as swtich_time, (b.first_time - a.first_time) * 24 as "switch_interval(hr)" FROM v$log_history a, v$log_history b WHERE a.SEQUENCE# + 1 = b.SEQUENCE# AND ROWNUM <= 100 ORDER BY SWTICH_TIME desc;

 

---------------

SQL> SELECT to_char(b.first_time, 'YYYY-MM-DD
as "switch_interval(hr)" FROM v$log_history a,
<= 100 ORDER BY SWTICH_TIME desc;

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-05-17 08:42:32          15.4563889
2012-05-16 17:15:09          871.736667
2012-04-10 09:30:57          96.5905556
2012-04-06 08:55:31          16.7022222
2012-04-05 16:13:23                  .5
2012-04-05 15:43:23          174.277222
2012-03-29 09:26:45          24.3047222
2012-03-28 09:08:28          24.7069444
2012-03-27 08:26:03          18.2386111
2012-03-26 14:11:44                1.12
2012-03-26 13:04:32          4.57722222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-26 08:29:54          63.7294444
2012-03-23 16:46:08          8.28888889
2012-03-23 08:28:48          23.9780556
2012-03-22 08:30:07          48.0397222
2012-03-20 08:27:44          16.0847222
2012-03-19 16:22:39          7.73722222
2012-03-19 08:38:25             72.2775
2012-03-16 08:21:46          22.6697222
2012-03-15 09:41:35          25.2486111
2012-03-14 08:26:40          17.5941667
2012-03-13 14:51:01          101.885556

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-09 08:57:53                .535
2012-03-09 08:25:47          23.7155556
2012-03-08 08:42:51          24.0383333
2012-03-07 08:40:33          17.5858333
2012-03-06 15:05:24          5.28861111
2012-03-06 09:48:05              1.4275
2012-03-06 08:22:26          19.0458333
2012-03-05 13:19:41          2.47777778
2012-03-05 10:51:01          1.00472222
2012-03-05 09:50:44          .420277778
2012-03-05 09:25:31          111.750278

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-29 17:40:30          .500555556
2012-02-29 17:10:28          128.791944
2012-02-24 08:22:57          18.2627778
2012-02-23 14:07:11          5.57555556
2012-02-23 08:32:39          18.5994444
2012-02-22 13:56:41          2.29611111
2012-02-22 11:38:55          3.07027778
2012-02-22 08:34:42          24.1738889
2012-02-21 08:24:16          23.8197222
2012-02-20 08:35:05          71.9941667
2012-02-17 08:35:26          15.0047222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-16 17:35:09              8.9325
2012-02-16 08:39:12          22.8277778
2012-02-15 09:49:32          25.2552778
2012-02-14 08:34:13          22.5308333
2012-02-13 10:02:22          72.8030556
2012-02-10 09:14:11             163.435
2012-02-03 13:48:05          4.68055556
2012-02-03 09:07:15          71.2969444
2012-01-31 09:49:26          424.926111
2012-01-13 16:53:52          49.8919444
2012-01-11 15:00:21          .624722222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-01-11 14:22:52          44.9722222
2012-01-09 17:24:32               7.045
2012-01-09 10:21:50          .116944444
2012-01-09 10:14:49          .011111111
2012-01-09 10:14:09               .0125
2012-01-09 10:13:24          .003888889
2012-01-09 10:13:10          .035277778

已選擇62行。

 

/****************查詢當前的日志設置情況：***************/
SQL> col member for a40;
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  E:\APP\JACK\ORADATA\ORCL\REDO03.LOG      NO
         2         ONLINE  E:\APP\JACK\ORADATA\ORCL\REDO02.LOG      NO
         1         ONLINE  E:\APP\JACK\ORADATA\ORCL\REDO01.LOG      NO
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
         1          1         64   52428800        512          1 NO
CURRENT                2907057 17-5月 -12       2.8147E+14
         2          1         62   52428800        512          1 NO
INACTIVE               2856611 16-5月 -12          2879698 17-5月 -12
         3          1         63   52428800        512          1 NO
INACTIVE               2879698 17-5月 -12          2907057 17-5月 -12
/***************添加臨時日志組*****************/
SQL> alter database add logfile group 4 ('E:\APP\JACK\ORADATA\ORCL\REDO04.LOG') size 150m;
 
將當前的online redo log 切換到新增的redo log group上
SQL> alter system switch logfile;
 
/************刪除online redo log ****************/
SQL> alter database drop logfile group 1;
 
//然后需要手動在對應目錄下，手動刪除redo logfile文件。
SQL> !
[oracle@orcal-50 ~]$ cd 'E:\APP\JACK\ORADATA\ORCL\REDO01.LOG'
[oracle@orcal-50 ORA10G]$rm -rf redo01.log
/* ***********歸檔控制*******************、
開啟歸檔
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open
如：SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             268435920 bytes
Database Buffers          796917760 bytes
Redo Buffers                4603904 bytes
數據庫裝載完畢。
SQL> alter database archivelog;
數據庫已更改。
SQL> alter database open;
數據庫已更改。
 
查詢歸檔：
SQL> archive log list
如：SQL> archive log list;
數據庫日志模式            存檔模式
自動存檔             啟用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日志序列     62
下一個存檔日志序列   65
當前日志序列           65
 
關閉歸檔：
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open
如下：
SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             268435920 bytes
Database Buffers          796917760 bytes
Redo Buffers                4603904 bytes
數據庫裝載完畢。
SQL> alter datadase noarchivelog;
alter datadase noarchivelog
      *
第 1 行出現錯誤:
ORA-00940: 無效的 ALTER 命令

SQL> alter database noarchivelog;
數據庫已更改。
SQL> alter database open；
SQL> alter database noarchivelog;
數據庫已更改。
SQL> alter database open;
數據庫已更改。
 
 本文由用戶 jack_gogo 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！