logminer oracle学习

logminer没用过

【转】

如何使用oracle 10g 的logminer查看redo log内容

第一步:首先在spfile中添加一下内容:

UTL_FILE_DIR=C:TEST(该路径任意取)

第二步:重启数据库,让第一步配置的参数生效

第三步:创建字典文件:

EXEC DBMS_LOGMNR_D.BUILD(‘A.ORA’,’C:TEST’);

第四步:将待查询内容的日志文件添加到字典中去

EXEC DBMS_LOGMNR.ADD_LOGFILE(‘C:oracleproduct10.2.0oradatahaigeREDO01.LOG’,DBMS_LOGMNR.NEW);

如果还要添加日志文件,则将DBMS_LOGMNR.NEW改为DBMS_LOGMNR.ADDFILE;

第五步:分析数据

EXEC DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>’C:TESTHAIGE.ORA’);

第六部:查看内容

数据均存在于V$LOGMNR_CONTENTS中;

SELECT TIMESTAMP,USERNAME,SQL_REDO FROM V$LOGMNR_CONTENTS;

另:

http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html

oracle11g的卸载

Oracle Databaseソフトウェアの削除

http://otndnld.oracle.co.jp/document/products/oracle11g/111/windows/E05878-02/deinstall.htm

转载网上关于oracle11g的卸载方法:【方法错误不是11g的

1、开始->设置->控制面板->管理工具->服务停止所有Oracle服务;

2、开始->程序->Oracle – OraDb10g_home1>Oracle Installation Products-> Universal Installer 卸装所有Oracle产品,但Universal Installer本身不能被删除;

3、运行regedit,选择HLMSOFTWAREORACLE,删除这个入口;

4、运行regedit,选择HLMSYSTEMCurrentControlSetServices,滚动这个列表,删除所有Oracle入口;

5、运行regedit,选择HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication,删除所有Oracle入口;

6、开始->设置->控制面板->系统->高级->环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定;

7、从桌面上、STARTUP(启动)组、程序菜单中,删除所有有关Oracle的组和图标;

8、删除X:Program FilesOracle目录;

9、重新启动计算机,重起后才能完全删除Oracle所在目录 ;

10、删除与Oracle有关的文件,选择Oracle所在的缺省目录C:Oracle,删除这个入口目录及所有子目录,并从Windows XP目录(一般为d:WINDOWS)下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等;

11、WIN.INI文件中若有[ORACLE]的标记段,删除该段;

12、如有必要,删除所有Oracle相关的ODBC的DSN;

13、到事件查看器中,删除Oracle相关的日志 说明:如果有个别DLL文件无法删除的情况,则不用理会,重新启动,开始新的安装,安装时,选择一个新的目录,则,安装完毕并重新启动后,老的目录及文件就可以删除掉了。

在卸载oracle时,没必要手工去停止那些服务,当卸载时,oracle会自动先去停止所有有关的服务。

11g卸载时,会提示去执行一个bat

app/oracle/product/11.2.0/dbhome_1/deinstall/deinstall

类似信息如下:

Checking for required files and bootstrapping …
Please wait …
Location of logs /tmp/deinstall2009-09-06_01-53-49-PM/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################
Install check configuration START

Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/dbhome_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory

Install check configuration END

Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/netdc_check5969876881546025484.log

Specify all Single Instance listeners that are to be de-configured [LISTENER]:

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/databasedc_check6393155117405937765.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/emcadc_check.log

Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /tmp/deinstall2009-09-06_01-53-49-PM/logs//ocm_check5624.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y – yes, n – no)? [n]: y
A log of this session will be written to: ‘/tmp/deinstall2009-09-06_01-53-49-PM/logs/deinstall_deconfig2009-09-06_12-54-14-AM.out’
Any error messages from this session will be written to: ‘/tmp/deinstall2009-09-06_01-53-49-PM/logs/deinstall_deconfig2009-09-06_12-54-14-AM.err’

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/emcadc_clean.log

Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/databasedc_clean5177710755362619206.log

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2009-09-06_01-53-49-PM/logs/netdc_clean2059809047871121170.log

De-configuring Single Instance listener(s): LISTENER

De-configuring listener: LISTENER
    Stopping listener: LISTENER
    Listener stopped successfully.
    Deleting listener: LISTENER
    Listener deleted successfully.
Listener de-configured successfully.

De-configuring backup files…
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /tmp/deinstall2009-09-06_01-53-49-PM/logs//ocm_clean5624.log
Oracle Configuration Manager clean END
Oracle Universal Installer clean START

Detach Oracle home ‘/u01/app/oracle/product/11.2.0/dbhome_1’ from the central inventory on the local node : Done

Delete directory ‘/u01/app/oracle/product/11.2.0/dbhome_1’ on the local node : Done

Delete directory ‘/u01/app/oraInventory’ on the local node : Done

Delete directory ‘/u01/app/oracle’ on the local node : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

Oracle install clean START

Clean install operation removing temporary directory ‘/tmp/install’ on node ‘vm10’

Oracle install clean END

Moved default properties file /tmp/deinstall2009-09-06_01-53-49-PM/response/deinstall_OraDb11g_home1.rsp as /tmp/deinstall2009-09-06_01-53-49-PM/response/deinstall_OraDb11g_home1.rsp4

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Following Single Instance listener(s) were de-configured successfully: LISTENER
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home ‘/u01/app/oracle/product/11.2.0/dbhome_1’ from the central inventory on the local node.
Successfully deleted directory ‘/u01/app/oracle/product/11.2.0/dbhome_1’ on the local node.
Successfully deleted directory ‘/u01/app/oraInventory’ on the local node.
Successfully deleted directory ‘/u01/app/oracle’ on the local node.
Oracle Universal Installer cleanup was successful.

Run ‘rm -rf /etc/oraInst.loc’ as root on node(s) ‘vm10’ at the end of the session.

Oracle install successfully cleaned up the temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

删除Archivelog RMAN

Archivelog并不能直接得从OS层直接物理删除,因为archivelog的相关信息是记录在controlfile中的,当物理删除后不会改变controlfile的设置。并且在查询相关的动态视图(例如v$archived_log)时,该部分日志仍然标注为存在。也就是说Oracle并不认为这些日志被删除了,所以在删除archivelog的时候,需要我们在其他地方做一些设置。

一、使用RMAN清楚物理删除后的记录

    可以使用RMAN来删除archivelog,具体可以按以下步骤操作:

    1、物理删除archivelog
    2、进入RMAN (rman target)
    3、crosscheck archivelog all;
    4、delete expried archivelog all;

    这样就在一些Oracle的记录中查不到相应的archivelog记录了。

二、直接使用RMAN删除archivelog

    其实在RMAN中是可以使用命令直接将ARCHIVELOG删除的,命令如下:

    RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;

    表示删除7天以前的所有archivelog,但是这个命令的缺点是无法对archivelog进行细致的划分。

oracle 开启/关闭归档 归档日志满了引起的问题

Oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog)

在关闭数据库后,启动数据库到mount状态
SQL> startup mount;

如果要启用归档模式,此处使用
alter database archivelog 命令。
如果需要停止归档模式,此处使用:
alter database noarchivelog 命令。

SQL> alter database open;

从Oracle10g开始,log_archive_start参数已经废除

一些命令:
archive log list
Select * from V$DATABASE

五 几种设置情况:

(1) Database log mode              Archive Mode

      Automatic archival             Enabled

这是在大部分生产环境中的ORACLE数据库日志及归档模式设置,这种情况下,做好

数据库的定期备份(有热备和冷备)和归档日志备份,可有效的将数据库恢复到有归档

日志的全部时间点。

(2) Database log mode              Archive Mode

      Automatic archival             Disabled

这种情况下,数据库不能自动归档,需要进行手工归档。如果所有在线日志都写满了,

又没有的及时进行手工归档的话,由于LGWR没有可用的在线日志可写,数据库将会挂在

这儿,只有进行手工归档后,有可用的在线日志后才能继续。在生产环境中应该避免

这种情况。

手工归档操作如下:

SVRMGRL> ALTER SYSTEM ARCHIVE LOG ALL;

数据库将会把在线日志进行归档处理

(3) Database log mode              NO Archive Mode

      Automatic archival             Enabled

有些相对欠缺经验的管理员在进行设置时,只在数据库参数文件中设置了

LOG_ARCHIVE_START=TRUE,然后在数据库起来后查看到ARCH归档进程已经起来了,可是

尽管ORACLE已经作了几次日志切换,但还是没有归档日志,这时的设置就是这种情况,

如果数据库不是处在ARVHIVELOG模式,redolog 还是不会被归档。

(4) Database log mode              NO Archive Mode

      Automatic archival             Disabled

这种设置是刚安装的oracle数据库的缺省设置,开发环境也大部分如此。即没有进行归档。