redolog and tablespace and datafile

最新推荐文章于 2024-07-17 07:04:18 发布

原创最新推荐文章于 2024-07-17 07:04:18 发布 · 617 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #sql #integer #system #数据库 #properties

ORACLE管理专栏收录该内容

46 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的关键操作，包括日志文件管理、表空间管理等。通过具体SQL命令示例，展示了如何进行日志文件切换、表空间创建与调整等任务。

1.关于log的视图

SQL> desc v$log;
名称是否为空? 类型
----------------------------------------- -------- --------------------

GROUP#                                             NUMBER
THREAD#                                            NUMBER
SEQUENCE#                                          NUMBER
BYTES                                              NUMBER
MEMBERS                                            NUMBER
ARCHIVED                                           VARCHAR2(3)
STATUS                                             VARCHAR2(16)
FIRST_CHANGE#                                      NUMBER
FIRST_TIME                                         DATE

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         47 CURRENT
         2          1         45 INACTIVE
         3          1         46 INACTIVE

2.切换日志文件

SQL> alter system switch logfile;

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         47 ACTIVE
         2          1         48 CURRENT
         3          1         46 INACTIVE

3.强制发生checkpoint

SQL> alter system set fast_start_mttr_target = 600;

SQL> show parameter start

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
dg_broker_start                      boolean     FALSE
drs_start                            boolean     FALSE
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     600
fast_start_parallel_rollback         string      LOW
log_archive_start                    boolean     TRUE

4.手动checkpoint

SQL> alter system checkpoint;

5.查看redolog文件组数

SQL> desc v$logfile;
名称是否为空? 类型
----------------------------------------- -------- --------------------

GROUP#                                             NUMBER
STATUS                                             VARCHAR2(7)
TYPE                                               VARCHAR2(7)
MEMBER                                             VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> col member format a50;
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
1 ONLINE E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG
NO

3 STALE ONLINE E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG
NO

2 ONLINE E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG

6.增加redolog文件

SQL> alter database add logfile group 4
2 ('E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo04.log') size 10M;

7.在组里增加成员变量

SQL> alter database add logfile member
2 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO04a.log' to group 4;

数据库已更改。

7-1:更改为归档模式

shutdown immediate

startup mount

alter database archivelog;

alter database open;

8.查询表空间

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA
---------- ------------------------------ --- --- ---
         0 SYSTEM                         YES NO YES
         1 UNDOTBS1                       YES NO YES
         2 SYSAUX                         YES NO YES
         4 USERS                          YES NO YES
         3 TEMP                           YES NO YES

9.查看表空间中数据文件

SQL> select * from dba_data_files;

10.创建表空间

SQL> create tablespace paul datafile
2 'f:/oracle/product/10.1.0/oradata/orcl/paul.dbf' size 20m;

11.创建数据库时，指定默认全局临时表空间

create tablesapce dba01

....

...

default tempoary tablespace temp

tempfile '/opt/.../temp01.dbf' size 4000M

charset set...

...