[oracle@prod ~]$ ps -ef | grep pmon
oracle 4945 4909 0 09:47 pts/1 00:00:00 grep pmon
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ export ORACLE_SID=testprd
[oracle@prod ~]$
[oracle@prod ~]$ echo $ORACLE_SID
testprd
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ cd ../..
[oracle@prod /]$
[oracle@prod /]$ cd /disk1/oradata/testprd/
[oracle@prod testprd]$ mkdir -p log_mine
[oracle@prod testprd]$ ll
total 48
drwxr-xr-x 3 oracle oinstall 4096 May 3 15:20 arch_dest
drwxr-xr-x 2 oracle oinstall 4096 May 11 17:13 backup_data
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:29 data_files
drwxr-xr-x 2 oracle oinstall 4096 May 15 09:47 log_mine
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:30 rman_catalog
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$ cd
[oracle@prod ~]$
[oracle@prod ~]$ cd /oraeng/app/oracle/product/11.2.0/dbhome_2/dbs/
[oracle@prod dbs]$ ll
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 3014 May 3 15:11 inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ vi inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ cat inittestprd.ora
db_name='testprd'
db_unique_name=testprd
sga_max_size=4G
sga_target=3G
processes = 150
audit_file_dest='/oraeng/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/disk1/oradata/testprd/arch_dest/'
db_recovery_file_dest_size=2G
diagnostic_dest='/oraeng/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/disk1/oradata/testprd/data_files/trecont.ctl)
compatible ='11.2.0'
utl_file_dir = '/disk1/oradata/testprd/log_mine/' (to store logminer dictionary) -----*****imp*****
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 09:53:17 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@testprd >> create spfile from pfile;
File created.
SYS@testprd >> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 1728057176 bytes
Database Buffers 2533359616 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.
SYS@testprd >>
SYS@testprd >>
SYS@testprd >> set time on
09:54:10 SYS@testprd >>
09:54:14 SYS@testprd >> select status from v$instance;
STATUS
------------
OPEN
09:54:21 SYS@testprd >> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/
09:54:25 SYS@testprd >>
09:54:37 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
09:55:00 SYS@testprd >>
09:55:01 SYS@testprd >>
09:55:14 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
09:57:39 SYS@testprd >>
10:02:04 SYS@testprd >> !
[oracle@prod 2016_05_15]$ pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
[oracle@prod 2016_05_15]$ ll
total 20544
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$ echo $ORACLE_SID
testprd
[oracle@prod 2016_05_15]$
10:07:27 @ >> conn scott/scott
Connected.
10:07:33 scott@testprd >> select count(*) from tab;
COUNT(*)
----------
5
Elapsed: 00:00:00.09
10:07:40 scott@testprd >>
10:07:41 scott@testprd >> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Elapsed: 00:00:00.01
10:07:57 scott@testprd >>
------------------ Now connect with SYS to enable SUPPLEMENTAL LOG DATA
10:10:24 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
Elapsed: 00:00:00.00
10:10:41 SYS@testprd >>
10:10:42 SYS@testprd >> alter database add supplemental log data;
Database altered.
Elapsed: 00:00:00.02
10:10:58 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
Elapsed: 00:00:00.00
10:11:12 SYS@testprd >> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:11:21 SYS@testprd >>
10:11:22 SYS@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:11:27 @ >> conn scott/scott
Connected.
10:11:34 scott@testprd >>
10:11:35 scott@testprd >>
10:11:35 scott@testprd >> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Elapsed: 00:00:00.01
10:11:51 scott@testprd >> select count(*) from salgrade;
COUNT(*)
----------
5
Elapsed: 00:00:00.05
10:12:02 scott@testprd >> delete from salgrade;
5 rows deleted.
Elapsed: 00:00:00.00
10:12:09 scott@testprd >> commit;
Commit complete.
Elapsed: 00:00:00.01
10:12:11 scott@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:12:19 @ >> conn sys / as sysdba
Enter password:
Connected.
10:12:32 SYS@testprd >>
10:12:32 SYS@testprd >>
10:12:33 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:12:39 SYS@testprd >> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
10:12:46 SYS@testprd >> /
System altered.
Elapsed: 00:00:02.73
10:12:53 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
10:12:57 SYS@testprd >> !
[oracle@prod 2016_05_15]$ ll
total 22664
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
[oracle@prod 2016_05_15]$ exit
exit
10:14:34 SYS@testprd >>
10:14:35 SYS@testprd >> sho parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/
10:14:41 SYS@testprd >>
10:14:41 SYS@testprd >>
10:15:56 SYS@testprd >> begin
10:16:00 2 dbms_logmnr_d.build('lgwr.dict','/disk1/oradata/testprd/log_mine/');
10:16:22 3 end;
10:16:23 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.24
10:16:26 SYS@testprd >>
10:20:14 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
10:20:23 SYS@testprd >>
10:20:24 SYS@testprd >>
10:21:53 SYS@testprd >> begin
10:22:21 2 dbms_logmnr.add_logfile('/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc');
10:22:46 3 end;
10:22:48 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
10:22:48 SYS@testprd >> begin
10:22:55 2 dbms_logmnr.start_logmnr(dictfilename=>'/disk1/oradata/testprd/log_mine/lgwr.dict');
10:23:08 3 end;
10:23:10 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
10:23:11 SYS@testprd >> spool logmnr.txt
10:23:18 SYS@testprd >> select sql_undo from v$logmnr_contents where seg_name='SALGRADE' and seg_owner='scott';
SQL_UNDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('1','700','1200');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('2','1201','1400');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('3','1401','2000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('4','2001','3000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('5','3001','9999');
Elapsed: 00:00:00.32
10:23:25 SYS@testprd >> spool off
10:23:34 SYS@testprd >>
10:23:59 SYS@testprd >>
10:24:00 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
10:24:04 SYS@testprd >>
10:24:10 SYS@testprd >> !ls -ltr
total 22672
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
-rw-r--r-- 1 oracle oinstall 2274 May 15 10:23 logmnr.txt
10:24:13 SYS@testprd >>
10:24:14 SYS@testprd >>
10:24:14 SYS@testprd >> @logmnr.txt
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
10:24:21 SYS@testprd >> commit;
Commit complete.
Elapsed: 00:00:00.00
10:24:28 SYS@testprd >> select count(*) from scott.salgrade;
COUNT(*)
----------
5
Elapsed: 00:00:00.00
10:24:44 SYS@testprd >>
10:24:46 SYS@testprd >>
10:24:46 SYS@testprd >> --------------finish--------------------
10:24:52 SYS@testprd >>
----------- ALERT LOG FILE ----------
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Sun May 15 10:12:46 2016
Archived Log entry 17 added for thread 1 sequence 5 ID 0xdbe17139 dest 1:
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /disk1/oradata/testprd/data_files/redo1.log
Sun May 15 10:12:53 2016
Archived Log entry 18 added for thread 1 sequence 6 ID 0xdbe17139 dest 1:
Sun May 15 10:12:54 2016
Starting background process SMCO
Sun May 15 10:12:54 2016
SMCO started with pid=27, OS id=5637
Sun May 15 10:16:24 2016
Sun May 15 10:16:24 2016
Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 583714
Sun May 15 10:19:29 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:20:08 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:21:38 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
Sun May 15 10:23:11 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:23:25 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
Sun May 15 10:35:45 2016
------------------------------------------------------------------------------------------------------------------
oracle 4945 4909 0 09:47 pts/1 00:00:00 grep pmon
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ export ORACLE_SID=testprd
[oracle@prod ~]$
[oracle@prod ~]$ echo $ORACLE_SID
testprd
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ cd ../..
[oracle@prod /]$
[oracle@prod /]$ cd /disk1/oradata/testprd/
[oracle@prod testprd]$ mkdir -p log_mine
[oracle@prod testprd]$ ll
total 48
drwxr-xr-x 3 oracle oinstall 4096 May 3 15:20 arch_dest
drwxr-xr-x 2 oracle oinstall 4096 May 11 17:13 backup_data
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:29 data_files
drwxr-xr-x 2 oracle oinstall 4096 May 15 09:47 log_mine
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:30 rman_catalog
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$ cd
[oracle@prod ~]$
[oracle@prod ~]$ cd /oraeng/app/oracle/product/11.2.0/dbhome_2/dbs/
[oracle@prod dbs]$ ll
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 3014 May 3 15:11 inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ vi inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ cat inittestprd.ora
db_name='testprd'
db_unique_name=testprd
sga_max_size=4G
sga_target=3G
processes = 150
audit_file_dest='/oraeng/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/disk1/oradata/testprd/arch_dest/'
db_recovery_file_dest_size=2G
diagnostic_dest='/oraeng/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/disk1/oradata/testprd/data_files/trecont.ctl)
compatible ='11.2.0'
utl_file_dir = '/disk1/oradata/testprd/log_mine/' (to store logminer dictionary) -----*****imp*****
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 09:53:17 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@testprd >> create spfile from pfile;
File created.
SYS@testprd >> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 1728057176 bytes
Database Buffers 2533359616 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.
SYS@testprd >>
SYS@testprd >>
SYS@testprd >> set time on
09:54:10 SYS@testprd >>
09:54:14 SYS@testprd >> select status from v$instance;
STATUS
------------
OPEN
09:54:21 SYS@testprd >> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/
09:54:25 SYS@testprd >>
09:54:37 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
09:55:00 SYS@testprd >>
09:55:01 SYS@testprd >>
09:55:14 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
09:57:39 SYS@testprd >>
10:02:04 SYS@testprd >> !
[oracle@prod 2016_05_15]$ pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
[oracle@prod 2016_05_15]$ ll
total 20544
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$ echo $ORACLE_SID
testprd
[oracle@prod 2016_05_15]$
10:07:27 @ >> conn scott/scott
Connected.
10:07:33 scott@testprd >> select count(*) from tab;
COUNT(*)
----------
5
Elapsed: 00:00:00.09
10:07:40 scott@testprd >>
10:07:41 scott@testprd >> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Elapsed: 00:00:00.01
10:07:57 scott@testprd >>
------------------ Now connect with SYS to enable SUPPLEMENTAL LOG DATA
10:10:24 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
Elapsed: 00:00:00.00
10:10:41 SYS@testprd >>
10:10:42 SYS@testprd >> alter database add supplemental log data;
Database altered.
Elapsed: 00:00:00.02
10:10:58 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
Elapsed: 00:00:00.00
10:11:12 SYS@testprd >> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:11:21 SYS@testprd >>
10:11:22 SYS@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:11:27 @ >> conn scott/scott
Connected.
10:11:34 scott@testprd >>
10:11:35 scott@testprd >>
10:11:35 scott@testprd >> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
Elapsed: 00:00:00.01
10:11:51 scott@testprd >> select count(*) from salgrade;
COUNT(*)
----------
5
Elapsed: 00:00:00.05
10:12:02 scott@testprd >> delete from salgrade;
5 rows deleted.
Elapsed: 00:00:00.00
10:12:09 scott@testprd >> commit;
Commit complete.
Elapsed: 00:00:00.01
10:12:11 scott@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:12:19 @ >> conn sys / as sysdba
Enter password:
Connected.
10:12:32 SYS@testprd >>
10:12:32 SYS@testprd >>
10:12:33 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:12:39 SYS@testprd >> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
10:12:46 SYS@testprd >> /
System altered.
Elapsed: 00:00:02.73
10:12:53 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
10:12:57 SYS@testprd >> !
[oracle@prod 2016_05_15]$ ll
total 22664
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
[oracle@prod 2016_05_15]$ exit
exit
10:14:34 SYS@testprd >>
10:14:35 SYS@testprd >> sho parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/
10:14:41 SYS@testprd >>
10:14:41 SYS@testprd >>
10:15:56 SYS@testprd >> begin
10:16:00 2 dbms_logmnr_d.build('lgwr.dict','/disk1/oradata/testprd/log_mine/');
10:16:22 3 end;
10:16:23 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.24
10:16:26 SYS@testprd >>
10:20:14 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
10:20:23 SYS@testprd >>
10:20:24 SYS@testprd >>
10:21:53 SYS@testprd >> begin
10:22:21 2 dbms_logmnr.add_logfile('/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc');
10:22:46 3 end;
10:22:48 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
10:22:48 SYS@testprd >> begin
10:22:55 2 dbms_logmnr.start_logmnr(dictfilename=>'/disk1/oradata/testprd/log_mine/lgwr.dict');
10:23:08 3 end;
10:23:10 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
10:23:11 SYS@testprd >> spool logmnr.txt
10:23:18 SYS@testprd >> select sql_undo from v$logmnr_contents where seg_name='SALGRADE' and seg_owner='scott';
SQL_UNDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('1','700','1200');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('2','1201','1400');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('3','1401','2000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('4','2001','3000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('5','3001','9999');
Elapsed: 00:00:00.32
10:23:25 SYS@testprd >> spool off
10:23:34 SYS@testprd >>
10:23:59 SYS@testprd >>
10:24:00 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15
10:24:04 SYS@testprd >>
10:24:10 SYS@testprd >> !ls -ltr
total 22672
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
-rw-r--r-- 1 oracle oinstall 2274 May 15 10:23 logmnr.txt
10:24:13 SYS@testprd >>
10:24:14 SYS@testprd >>
10:24:14 SYS@testprd >> @logmnr.txt
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
10:24:21 SYS@testprd >> commit;
Commit complete.
Elapsed: 00:00:00.00
10:24:28 SYS@testprd >> select count(*) from scott.salgrade;
COUNT(*)
----------
5
Elapsed: 00:00:00.00
10:24:44 SYS@testprd >>
10:24:46 SYS@testprd >>
10:24:46 SYS@testprd >> --------------finish--------------------
10:24:52 SYS@testprd >>
----------- ALERT LOG FILE ----------
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Sun May 15 10:12:46 2016
Archived Log entry 17 added for thread 1 sequence 5 ID 0xdbe17139 dest 1:
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /disk1/oradata/testprd/data_files/redo1.log
Sun May 15 10:12:53 2016
Archived Log entry 18 added for thread 1 sequence 6 ID 0xdbe17139 dest 1:
Sun May 15 10:12:54 2016
Starting background process SMCO
Sun May 15 10:12:54 2016
SMCO started with pid=27, OS id=5637
Sun May 15 10:16:24 2016
Sun May 15 10:16:24 2016
Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 583714
Sun May 15 10:19:29 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:20:08 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:21:38 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
Sun May 15 10:23:11 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:23:25 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
Sun May 15 10:35:45 2016
------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment