Monday, June 28, 2010

config cygwin cron

$ which cron-config
/usr/bin/cron-config
$ cron-config
Do you want to install the cron daemon as a service? (yes/no) yes
Enter the value of CYGWIN for the daemon: [ ] Cygwin Cron
ERROR: Only "[no]ntsec" "[no]smbntsec" "[no]traverse" allowed.
Enter the value of CYGWIN for the daemon: [ ] ntsec

You must decide under what account the cron daemon will run.
If you are the only user on this machine, the daemon can run as yourself.
   This gives access to all network drives but only allows you as user.
Otherwise cron should run under the local system account.
  It will be capable of changing to other users without requiring a
  password, using one of the three methods detailed in
  http://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1
Do you want the cron daemon to run as yourself? (yes/no) no


Running cron_diagnose ...
... no problem found.

INFO: A cron daemon is already running.

In case of problem, examine the log file for cron,
/var/log/cron.log, and the Windows event log (using /usr/bin/cronevents)
for information about the problem cron is having.

Examine also any cron.log file in the HOME directory
(or the file specified in MAILTO) and cron related files in /tmp.

If you cannot fix the problem, then report it to cygwin@cygwin.com.
Please run the script /usr/bin/cronbug and ATTACH its output
(the file cronbug.txt) to your e-mail.

WARNING: PATH may be set differently under cron than in interactive shells.
         Names such as "find" and "date" may refer to Windows programs.

$ cygrunsrv -R "Cron daemon"
cygrunsrv: Error removing a service: OpenService:  Win32 error 1060:
The specified service does not exist as an installed service.

$ cygrunsrv -R cron
$ cron-config
Do you want to install the cron daemon as a service? (yes/no) yes
Enter the value of CYGWIN for the daemon: [ ] ntsec

You must decide under what account the cron daemon will run.
If you are the only user on this machine, the daemon can run as yourself.
   This gives access to all network drives but only allows you as user.
Otherwise cron should run under the local system account.
  It will be capable of changing to other users without requiring a
  password, using one of the three methods detailed in
  http://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1
Do you want the cron daemon to run as yourself? (yes/no) yes

Please enter the password for user 'liqy':
Reenter:
Running cron_diagnose ...
... no problem found.

INFO: A cron daemon is already running.

In case of problem, examine the log file for cron,
/var/log/cron.log, and the Windows event log (using /usr/bin/cronevents)
for information about the problem cron is having.

Examine also any cron.log file in the HOME directory
(or the file specified in MAILTO) and cron related files in /tmp.

If you cannot fix the problem, then report it to cygwin@cygwin.com.
Please run the script /usr/bin/cronbug and ATTACH its output
(the file cronbug.txt) to your e-mail.

WARNING: PATH may be set differently under cron than in interactive shells.
         Names such as "find" and "date" may refer to Windows programs.

$ cat /var/log/cron.log
/usr/sbin/cron: can't lock /var/run/cron.pid, otherpid may be 4428: Resource temporarily unavailable
/usr/sbin/cron: can't lock /var/run/cron.pid, otherpid may be 4428: Resource temporarily unavailable
$ ps -ef |grep 4428
  SYSTEM    4428       1   ?  13:46:40 /usr/sbin/cron
$ > /var/log/cron.log
$ cat /var/log/cron.log
$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (t.cron installed on Mon Jun 21 13:41:56 2010)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)
* * * * * date>>t.log
$ pwd
/home/liqy
$ cat t.log
Mon Jun 21 13:47:02 MPST 2010
Mon Jun 21 13:48:02 MPST 2010
Mon Jun 21 13:49:02 MPST 2010
Mon Jun 21 13:50:03 MPST 2010
Mon Jun 21 13:51:03 MPST 2010
Mon Jun 21 13:52:02 MPST 2010
Mon Jun 21 13:53:02 MPST 2010
Mon Jun 21 13:54:02 MPST 2010
Mon Jun 21 13:55:02 MPST 2010


Update on 25-Jan-2016

Similar error encountered on Windows 10.

I found this is easier way to install cron as service.


and I changed the account to start the service using my own ID.



sample code for load top AWR sql into sql plan baseline

--in this example we load top 30 sql inside AWR snapshot 710 to 714 , into sql tuning set , followed by loaded and create as sql plan baseline.

EXEC DBMS_SQLTUNE.DROP_SQLSET('tset1');
EXEC DBMS_SQLTUNE.CREATE_SQLSET('tset1');

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 my_plans PLS_INTEGER;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  710,714,
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'tset1',
             populate_cursor => baseline_cursor);
    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/

Quick NFS HOWTO for Centos

On the server

vi /etc/exports
add lines like:
/data1/sessions 192.168.0.0/255.255.0.0(rw) 10.0.0.0/255.0.0.0(rw)
vi /etc/hosts.allow
add lines like:
portmap: 192.168.0.0/255.255.0.0, 10.0.0.0/255.0.0.0
/etc/init.d/nfsd start

On the Client

vi /etc/fstab, adding the following line:
nfshostname:/data1/sessions /mnt nfs rw,hard,intr 0 0
make sure to mkdir /mnt/sessions, or it won’t work. To do it manually, just:
mount nfshostname:/data1/sessions /mnt/sessions

What is sytem privilege "export/import full database"

SYS@ODST> select * from dba_sys_privs where grantee='LIQY'; 

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO

SYS@ODST> grant export full database to liqy;

Grant succeeded.


odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log  tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:45:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export DBAM's table M_ICC_CALL
Export terminated successfully with warnings.
odsdev01:ODST:/software/oraods/temp> oerr exp 9
00009, 00000, "no privilege to export %s's table %s"
// *Cause:  An attempt was made to export another user's table. Only a
//          database administrator can export another user's tables.
// *Action: Ask your database administrator to do the export.


SYS@ODST> select * from dba_sys_privs where grantee='LIQY';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO
LIQY                           EXPORT FULL DATABASE                     NO

SYS@ODST> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           T_ROLE                         NO  YES
LIQY                           EXP_FULL_DATABASE              NO  YES

odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log  tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:51:41 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DBAM. . exporting table                    M_ICC_CALL



SYS@ODST> select * from dba_sys_privs where grantee='EXP_FULL_DATABASE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_FULL_DATABASE              RESUMABLE                                NO
EXP_FULL_DATABASE              BACKUP ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY TYPE                         NO
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
EXP_FULL_DATABASE              READ ANY FILE GROUP                      NO
EXP_FULL_DATABASE              SELECT ANY SEQUENCE                      NO
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
EXP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER              NO

8 rows selected.

Finally, get the answer from metalink:

The system privileges EXPORT/IMPORT FULL DATABASE, introduced with 10gR1, are currently not used. These will be implemented in future releases with new functionality but in 10/11g these are not operational.

So confusing ...

Simple log miner

本身这个步骤很多高手都已经贴过了,只是我在使用中发现大体上大家写的都有些复杂,于是,我总结了个超级简化版的,方便大家使用:

1.安装LOGMNR包,需要本步骤没什么可多说的,只是需要注意在连接数据库的时候默认最好使用本地验证方式
C:\>sqlplus /nolog
SQL> conn / as sysdba
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslm.sql
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslmd.sql
SQL> @D:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\dbmslms.sql"
SQL> show parameter utl;

2.创建数据字典
SQL> alter system set utl_file_dir='d:\oracle\logmnr' scope=both;
SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora','d:\oracle\logmnr');

3.添加日志文件
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.new);
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',Options=>dbms_logmnr.addfile);

SQL> begin
sys.dbms_logmnr.add_logfile(LogFileName=>'D:\1_15969.dbf',options =>dbms_logmnr.addfile);
end;

4.使用字典分析日志文件
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\logmnr\dictionary.ora');

5.查询结果
SQL> select scn,sql_redo from v$logmnr_contents;

6.退出logmnr
SQL> execute dbms_logmnr.end_logmnr;


PS:这里最重要的是第5步,如果结果集很大的话建议使用PL/SQL等工具进行操作,这样便于后期修改,相对于SQLPLUS的格式化输出命令来说使用 PL/SQL DEVELOPER确实能方便很多。

Safety shutdown abort

以下方式是本人认为比较安全的的shutdown abort步骤,非官方,仅作参考
1. kill all dedicated server process
$kill -9 `ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $9}'`
$ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $9}'|xargs kill -9
可以等等一下事物回滚,呵 呵
2.switch log file
sqlplus '/as sysdba'
SQL> alter system switch logfile;
3.checkpoint and suspend IO
SQL> alter system checkpoint;
SQL> alter system suspend;
执行完这个 别把这个session终止,也不要执行其他语句,否则可能sqlplus "/as sysdba"上不去了。
4.shutdown db
SQL> shutdown abort
5.restart db
sqlplus '/as sysdba'
SQL> start up
6.review transaction rollback
SQL> select * from V$FAST_START_TRANSACTIONS;
通常shutdown abort不会损坏数据文件,即使损坏,也可能只是一些Block corruption,recove就OK了,更坏的可能是数据文件损坏,可能就需要做恢复了,比较麻烦。
不过我觉得这种方式还比较安全一 些吧。
到是对于shutdown immediate不成功的情况,没有按照这个方法试过,呵呵。