oracle学习笔记
查看重做当前日志情况
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
---------- ---------- ---------- ----------------
1 32 1 CURRENT
2 30 1 INACTIVE
3 31 1 INACTIVE
强制日志切换
SQL> alter system switch logfile;
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
---------- ---------- ---------- ----------------
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;
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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;
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
---------- ------- ------- --------------------------------------------------
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;
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
---------- ------- ------- --------------------------------------------------
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;
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;
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';
---------- ------- ------- --------------------------------------------------
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;
---------- ------- ------- --------------------------------------------------
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
---------- ------- ----------------------------------------
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.
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.
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';
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 ;
2 '/oradata/TEST/redo04b.log' to
3 '/oradata/redo04b.rdo'
4 ;
Database altered.
--open database for nomal opertion
SQL> alter database open;
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
第二种方法：
---------- ------- ----------------------------------------
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
-------
STARTED
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
------------
ARCHIVELOG
查看数据库的所有表空间
SQL> select * from v$tablespace;
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
---------- ------------------------------ --- --- --- ---
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;
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
---------------------------------------- ------------------------------
/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;
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;
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;
SQL> alter database default temporary tablespace temp;
Database altered.
创建用户时给用户指定表空间以后在此用户创建的所有表都会在chris表空间里
SQL> create user test identified by test default tablespace 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 ;
SQL> select username,default_tablespace from dba_users where username = 'TEST'
2 ;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST CHRIS
------------------------------ ------------------------------
TEST CHRIS
查看当前数据库的默认表空间
SQL> select PROPERTY_NAME, PROPERTY_VALUE from DATABASE_PROPERTIES where property_name like 'DEFAU%' ;
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
------------------------------ -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TBS_TYPE SMALLFILE
查看各种表空间的类型
SQL> select tablespace_name,contents,extent_management from dba_tablespaces;
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
------------------------------ --------- ----------
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博客 ” ，链接：https://blog.itpub.net/16920491/viewspace-732722/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
写给自己
请登录后发表评论
登录
全部评论
