ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle学习笔记

oracle学习笔记

原创 Linux操作系统 作者:magicstar520 时间:2012-06-13 17:30:52 0 删除 编辑
查看重做当前日志情况
SQL> select group#,sequence#,members,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         32          1 CURRENT
         2         30          1 INACTIVE
         3         31          1 INACTIVE
 
强制日志切换
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,members,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         32          1 ACTIVE
         2         33          1 CURRENT
         3         31          1 INACTIVE
SQL> show parameter fast_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
SQL>
自动检查点调整(SelfTune Checkpoint),这个特性可以通过设置FAST_START_MTTR_TARGET为0来启用,如设置此参数为300,说明DBWR每5分钟把buffer cache 里的dirty数据写到数据文件
SQL> alter system set fast_start_mttr_target = 300 scope=both;
System altered.
SQL> show parameter fast_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     300
fast_start_parallel_rollback         string      LOW
强制把database buffer cache 的dirty数据写到数据文件里
SQL> alter system checkpoint;
System altered.
增加日志组
SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3 STALE   ONLINE  /oradata/TEST/redo03.log
         2         ONLINE  /oradata/TEST/redo02.log
         1         ONLINE  /oradata/TEST/redo01.log
SQL> alter database add logfile group 4
  2  ('/oradata/TEST/redo04.log') size 50M;
Database altered.
SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3 STALE   ONLINE  /oradata/TEST/redo03.log
         2         ONLINE  /oradata/TEST/redo02.log
         1         ONLINE  /oradata/TEST/redo01.log
         4         ONLINE  /oradata/TEST/redo04.log
增加日志组成员
SQL> alter database add logfile member
  2  '/oradata/TEST/redo04a.log' to group 4;
Database altered.
SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3 STALE   ONLINE  /oradata/TEST/redo03.log
         2         ONLINE  /oradata/TEST/redo02.log
         1         ONLINE  /oradata/TEST/redo01.log
         4         ONLINE  /oradata/TEST/redo04.log
         4 INVALID ONLINE  /oradata/TEST/redo04a.log
删除组成员
SQL> alter database drop logfile member
  2  '/oradata/TEST/redo04a.log';
Database altered.
SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3         ONLINE  /oradata/TEST/redo03.log
         2         ONLINE  /oradata/TEST/redo02.log
         1         ONLINE  /oradata/TEST/redo01.log
         4         ONLINE  /oradata/TEST/redo04.log
删除组注不能是当前组
SQL> alter database drop logfile group 4;
Database altered.
 
Relocate &rename(重新布署或命名重做日志文件)
第一种方法:
ALTER DATABASE RENEME FILE command

--shut down the database
SQL> select group#,status,member from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------
         3         /oradata/TEST/redo03.log
         2         /oradata/TEST/redo02.log
         1         /oradata/TEST/redo01.log
         4         /oradata/TEST/redo04a.log
         4         /oradata/TEST/redo04b.log
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--copy the online redo log file to the new location
[oracle@oracle TEST]$ cp redo04
redo04a.log  redo04b.log 
[oracle@oracle TEST]$ cp redo04* ..
[oracle@oracle TEST]$ cd ..
[oracle@oracle oradata]$ ll
total 102524
-rw-r----- 1 oracle oinstall 52429312 Jun 13 13:44 redo04a.log
-rw-r----- 1 oracle oinstall 52429312 Jun 13 13:44 redo04b.log
drwxr-x--- 2 oracle oinstall     4096 Jun 13 13:26 TEST

--place the database in mount mode
SQL> startup mount;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size             113248500 bytes
Database Buffers           50331648 bytes
Redo Buffers                2973696 bytes
Database mounted.
 
--execute the command
SQL> alter database rename file
  2  '/oradata/TEST/redo04a.log' to
  3  '/oradata/redo04a.rdo';
Database altered.
SQL> alter database rename file
  2  '/oradata/TEST/redo04b.log' to
  3  '/oradata/redo04b.rdo'
  4  ;
Database altered.
 
--open database for nomal opertion
SQL> alter database open;
Database altered.
SQL> select group#,status,member from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------
         3         /oradata/TEST/redo03.log
         2         /oradata/TEST/redo02.log
         1         /oradata/TEST/redo01.log
         4         /oradata/redo04a.rdo
         4         /oradata/redo04b.rdo
第二种方法:
Add new members and drop old members.
 
查看是否为归档模式有两个视图v$instance,v$database
SQL> select archiver from v$instance;
ARCHIVE
-------
STARTED
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
查看数据库的所有表空间
SQL>  select * from v$tablespace;
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 NEWTBS                         YES NO  YES
7 rows selected.
查看表空间对应的数据文件
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata/TEST/users01.dbf                USERS
/oradata/TEST/sysaux01.dbf               SYSAUX
/oradata/TEST/undotbs01.dbf              UNDOTBS1
/oradata/TEST/system01.dbf               SYSTEM
/oradata/TEST/example01.dbf              EXAMPLE
/oradata/TEST/newtbs01.dbf               NEWTBS
6 rows selected.
创建表空间
SQL> create tablespace chris
  2  datafile '/oradata/TEST/chris01.dbf' size 100M;
Tablespace created.
在表空间增加数据文件
SQL> alter tablespace chris
  2  add datafile '/oradata/TEST/chris02.dbf' size 100M reuse;
Tablespace altered.
指定数据库的默认表空间,创建用户时如果不指定表空间默认的就属于这表空间
SQL> alter database default tablespace test;
Database altered.
指定临时表空间为默认
SQL> alter database default temporary tablespace temp;
Database altered.
创建用户时给用户指定表空间以后在此用户创建的所有表都会在chris表空间里
SQL> create user test identified by test default tablespace chris;
User created.
查看用户是属于那个表空间
SQL> select username,default_tablespace from dba_users where username = 'TEST'
  2  ;
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           CHRIS
查看当前数据库的默认表空间
SQL> select PROPERTY_NAME, PROPERTY_VALUE from DATABASE_PROPERTIES where property_name like 'DEFAU%' ;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ -----------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TBS_TYPE               SMALLFILE
查看各种表空间的类型
SQL> select tablespace_name,contents,extent_management from dba_tablespaces;
TABLESPACE_NAME                CONTENTS  EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         PERMANENT LOCAL
UNDOTBS1                       UNDO      LOCAL
SYSAUX                         PERMANENT LOCAL
TEMP                           TEMPORARY LOCAL
USERS                          PERMANENT LOCAL
EXAMPLE                        PERMANENT LOCAL
NEWTBS                         PERMANENT LOCAL
CHRIS                          PERMANENT LOCAL
8 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16920491/viewspace-732722/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 写给自己
请登录后发表评论 登录
全部评论

注册时间:2011-08-22

  • 博文量
    5
  • 访问量
    10897