oracle如何开启日志,oracle启动日志在哪里

Oracle归档日志

显示归档日志信息

创新互联2013年至今，是专业互联网技术服务公司，拥有项目成都网站设计、做网站网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元江宁做网站,已为上家服务,为江宁各地企业和个人服务,联系电话:18980820575

1,使用ARCHIVE LOG LIST命令可以显示日志操作模式,归档位置,自动归档机器要归档的日志序列号等信息.

2显示日志操作模式

SELECT name,log_mode FROM v$database;

3,显示Oracle归归档日志信息.

Col name format a46

Select name, swquence#, first_change# FROM v$archived_log;

Name用于表示Oracle归归档日志文件名,sequence#用于表示归档日志对应的日志序列号,firs_change#用于标识归档日志的起始SCN值.

4、执行介质 恢复 时,需要使用归档日志文件,此四必须准确定位归档日志的存放位置.通过查询动态性能视图v$archive_dest可以取得归档日志所在目录.

SELECT destination FROM v$archive dest;

5,显示日志历史信息

SELECT * FROM v$loghist;

THREAD#用于标识重做线程号,SEQUNCE#用于标识日志序列号,FIRST_CHANGE#用于标识日志序列号对应的起始SCN值,FIRST_TIME用于标识起始SCN的发生时间.SWICTH_CHANGE#用于标识日志切换的SCN值.

6.显示归档进程信息.

进行日志切换时,ARCH进程会自动将重做日志内容复制到Oracle归归档日志中,为了加快归档速度,应该启用多个ARCH进程.通过查询动态性能视图V$ARCHIVE_PROCESSES可以显示所有归档进程的信息!

SELECT * FROM v$archive_processes;

Porcess用于标识ARCH进程的编号,status用于标识ARCH进程的状态(ACTIVE:活动,STOPPED:未启动),log_sequence用于标识正在进行归档的日志序列号,state用于标识ARCH进程的工作状态

oracle 如何查看日志？

1、Oracle日志的路径的查看：

登录：sqlplus "/as sysdba"

查看路径：SQL select * from v$logfile;

SQL select * from v$logfile;(#日志文件路径)

2、Oracle日志文件包含哪些内容：（日志的数量可能略有不同）

control01.ctl example01.dbf redo02.log   sysaux01.dbf undotbs01.dbf

control02.ctl redo03.log   system01.dbf users01.dbf

control03.ctl redo01.log     SHTTEST.dbf temp01.dbf

3、Oracle日志的查看方法语句代码：

SQLselect * from v$sql   (#查看最近所作的操作)

SQLselect * fromv $sqlarea（#查看最近所作的操作）

4、LogMiner的使用：

5、查看LogMiner工具分析结果

SQL select * from dict t where t.table_name like '%LOGMNR%'；看所有与logmnr相关的视图

6、分析结果在GV$LOGMNR_CONTENTS 视图中，可按以下语句查询： 

selec tscn , timestamp , log_id , seg_owner , seg_type , table_space , data_blk# , data_obj# , data_objd#, session#,serial#,username,session_info,sql_redo,sql_undo from logmnr3 t where t.sql_redo like 'create%';

如何启动Oracle的监听日志

启动监听日志的步骤如下：

1.首先查看当前监听的状态

[oracle@oel ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:29:58

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

STATUS of the LISTENER

————————

Alias listener

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-JUL-2012 13:12:51

Uptime 0 days 0 hr. 17 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “orcl” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

Service “orclXDB” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

The command completed successfully

从上面显示信息来看，我们监听正常。并且没有看到Listener Log File的信息。

2.进入监听配置，查看监听详细信息

[oracle@oel ~]$ lsnrctl –进入监听配置

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:35:00

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL show –查看监听参数信息

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

rawmode displaymode

rules trc_file

trc_directory trc_level

log_file log_directory

log_status current_listener

inbound_connect_timeout startup_waittime

snmp_visible save_config_on_stop

dynamic_registration enable_global_dynamic_endpoint

oracle_home pid

connection_rate_limit

LSNRCTL show log_status –查看监听日志状态

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

LISTENER parameter “log_status” set to OFF

The command completed successfully

上面信息显示Listener parameter “log_status” set to OFF说明监听日志是关闭的。这是时候我们需要启动监听日志。

3.设置当前监听，如果有多个监听，在修改监听配置的时候需要指定某个具体的监听，我这里只有一个默认的Listener，所以不需要设置。我这里还是演示一下。

LSNRCTL show current_listener

Current Listener is LISTENER

LSNRCTL set current_listener Listener

Current Listener is Listener

LSNRCTL show current_listener

Current Listener is Listener

4.启动监听日志,并且保存配置信息

LSNRCTL set log_status on –设置监听日志状态为on

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

LISTENER parameter “log_status” set to ON

The command completed successfully

LSNRCTL save_config –保存监听配置

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

Saved listener configuration parameters.

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Old Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak

The command completed successfully

5.在查看监听状态

[oracle@oel ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2012 13:44:32

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel)(PORT=1521)))

STATUS of the LISTENER

————————

Alias listener

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-JUL-2012 13:12:51

Uptime 0 days 0 hr. 31 min. 41 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “orcl” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

Service “orclXDB” has 1 instance(s).

Instance “orcl”, status READY, has 1 handler(s) for this service…

The command completed successfully

通过上述配置之后，看到了Listener Log File /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

最后我们可以通过监听日志来查询里面的详细信息

[oracle@oel ~]$ grep ‘2012-07-01T13:45:40′ /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml|more

msg time=’2012-07-01T13:45:40.302+08:00′ org_id=’oracle’ comp_id=’tnslsnr’

如何打开 oracle 日志文件

监听日志在$ORACLE_BASE/diag/tnslsnr/hostname/listener/trace目录下，文件名为listener.log上面的hostname根据你的实际主机名而定

oracle 如何关闭打开归档日志的方法

查看oracle数据库是否为归档模式

SQL select name,log_mode from V$database;

NAME LOG_MODE

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

TEST NOARCHIVELOG

SQL archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 80157

Current log sequence 80163

编辑本段配置数据库的.归档模式

1.改变非归档模式到归档模式:

1)SQL conn / as sysdba (以DBA身份连接数据库)

2)SQL shutdown immediate;(立即关闭数据库)

3)SQL startup mount (启动实例并加载数据库，但不打开)

4)SQL alter database archivelog; (更改数据库为归档模式)

5)SQL alter database open; (打开数据库)

6)SQL alter system archive log start; (启用自动归档)

7)SQL exit (退出)

做一次完全备份,因为非归档日志模式下产生的备份日志对于归档模式已经不可用了.这一步非非常重要!

2.改变归档模式到非归档模式:

1)SQLSHUTDOWN NORMAL/IMMEDIATE;

2)SQLSTARTUP MOUNT;

3)SQLALTER DATABASE NOARCHIVELOG;

4)SQLALTER DATABASE OPEN;

3.归档相关命令

archive log stop;

archive log start;

archive log list;

show parameters;

show parameters log_archive_start;

show parameters log_archive_max_process; #归档进程数

alter system set log_archive_max_process=5; #将归档进程数改为5

select * from v$bgprocess; #检察后台进程