SQL>>
System>
SQL>>
System>
SQL>>
System>
SQL>>
System>
SQL>>
System>
SQL>>
System> (2)重启数据库:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2254802944 bytes
Fixed>
Variable> Database Buffers 1174405120 bytes
Redo Buffers 4440064 bytes
Database mounted.
Database opened.
(3)查看各个内存参数设置:
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 7G
sga_target big integer 0
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 10G
memory_target big integer 10G
shared_memory_address integer 0
四、修改redo log组以及大小:--为防止日志频繁切换,引起数据库性能低下问题。
1、创建redo日志存放目录:
$ mkdir -p /home/oracle/app/oradata/xtwllog/
$ chmod 750 /home/oracle/app/oradata/xtwllog/
2、查询日志信息:
SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 ACTIVE
2 50 CURRENT
3 50 ACTIVE
3、查询日志目录:
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------ ----------------
1 ONLINE /home/oracle/app/oradata/xtwl/redo01.log NO
2 ONLINE /home/oracle/app/oradata/xtwl/redo02.log NO
3 ONLINE /home/oracle/app/oradata/xtwl/redo03.log NO
4、新增两组日志组,每组500M:
SQL>> alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance xtwl (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/home/oracle/app/oradata/xtwl/redo02.log'
8、需要手动切换日志多次,使新建的日志组能够应用:
SQL>>
System> SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
2 50 ACTIVE
3 50 INACTIVE
4 500 CURRENT
5 500 UNUSED
SQL>>
System> SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
2 50 ACTIVE
3 50 INACTIVE
4 500 ACTIVE
5 500 CURRENT
9、使用alter system checkpoint将Active的日志状态置为INACTIVE:
SQL>>
System> SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
2 50 INACTIVE
3 50 INACTIVE
4 500 INACTIVE
5 500 CURRENT
10、删除原2,3日志组:
SQL>>
Database>
SQL>>
Database>
SQL>>
Database> SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS
---------- ---------- ---------- ----------------
1 1 500 UNUSED
2 1 500 UNUSED
3 1 500 UNUSED
4 1 500 INACTIVE
5 1 500 CURRENT
12、多次执行切换日志操作,使新建的日志组都能正常应用:
SQL>>
System>
SQL>>
System>
SQL>>
System> SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS
---------- ---------- ---------- ----------------
1 1 500 ACTIVE
2 1 500 ACTIVE
3 1 500 CURRENT
4 1 500 INACTIVE
5 1 500 ACTIVE
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------ ----------------
1 ONLINE /home/oracle/app/oradata/xtwllog/redo01.log NO
2 ONLINE /home/oracle/app/oradata/xtwllog/redo02.log NO
3 ONLINE /home/oracle/app/oradata/xtwllog/redo03.log NO
4 ONLINE /home/oracle/app/oradata/xtwllog/redo04.log NO
5 ONLINE /home/oracle/app/oradata/xtwllog/redo05.log NO
13、删除原redo日志文件,释放磁盘空间:
$ rm /home/oracle/app/oradata/xtwl/redo0*.log
五、修改数据库为归档日志模式:
1、新建归档日志存放目录:
$ mkdir -p /home/oracle/app/archlog
$ chmod 750 /home/oracle/app/archlog
2、停止数据库:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3、启动数据库到mount状态:
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes