日志挖掘
SQL> create table tt as select * from dba_users;

Table created.

SQL> select * from v$log where status='CURRENT';

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         2          1          6   52428800          1 NO  CURRENT
      1474311 28-JUN-11


1 row selected.

SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=1;

MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/redo01.log

1 row selected.

SQL> exec dbms_logmnr.add_logfile('/oracle/oradata/orcl/redo01.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> desc v$logmnr_contents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCN                                                NUMBER
 CSCN                                               NUMBER
 TIMESTAMP                                          DATE
 COMMIT_TIMESTAMP                                   DATE
 THREAD#                                            NUMBER
 LOG_ID                                             NUMBER
 XIDUSN                                             NUMBER
 XIDSLT                                             NUMBER
 XIDSQN                                             NUMBER
 PXIDUSN                                            NUMBER
 PXIDSLT                                            NUMBER
 PXIDSQN                                            NUMBER
 RBASQN                                             NUMBER
 RBABLK                                             NUMBER
 RBABYTE                                            NUMBER
 UBAFIL                                             NUMBER
 UBABLK                                             NUMBER
 UBAREC                                             NUMBER
 UBASQN                                             NUMBER
 ABS_FILE#                                          NUMBER
 REL_FILE#                                          NUMBER
 DATA_BLK#                                          NUMBER
 DATA_OBJ#                                          NUMBER
 DATA_OBJD#                                         NUMBER
 SEG_OWNER                                          VARCHAR2(32)
 SEG_NAME                                           VARCHAR2(256)
 TABLE_NAME                                         VARCHAR2(32)
 SEG_TYPE                                           NUMBER
 SEG_TYPE_NAME                                      VARCHAR2(32)
 TABLE_SPACE                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(18)
 SESSION#                                           NUMBER
 SERIAL#                                            NUMBER
 USERNAME                                           VARCHAR2(30)
 SESSION_INFO                                       VARCHAR2(4000)
 TX_NAME                                            VARCHAR2(256)
 ROLLBACK                                           NUMBER
 OPERATION                                          VARCHAR2(32)
 OPERATION_CODE                                     NUMBER
 SQL_REDO                                           VARCHAR2(4000)
 SQL_UNDO                                           VARCHAR2(4000)
 RS_ID                                              VARCHAR2(32)
 SEQUENCE#                                          NUMBER
 SSN                                                NUMBER
 CSF                                                NUMBER
 INFO                                               VARCHAR2(32)
 STATUS                                             NUMBER
 REDO_VALUE                                         NUMBER
 UNDO_VALUE                                         NUMBER
 SQL_COLUMN_TYPE                                    VARCHAR2(30)
 SQL_COLUMN_NAME                                    VARCHAR2(30)
 REDO_LENGTH                                        NUMBER
 REDO_OFFSET                                        NUMBER
 UNDO_LENGTH                                        NUMBER
 UNDO_OFFSET                                        NUMBER
 DATA_OBJV#                                         NUMBER
 SAFE_RESUME_SCN                                    NUMBER
 XID                                                RAW(8)
 PXID                                               RAW(8)
 AUDIT_SESSIONID                                    NUMBER

SQL> select sql_redo from v$logmnr_contents
  2  where sql_redo like '%create%tt%';

SQL_REDO
--------------------------------------------------------------------------------
create table tt as select * from dba_users;

1 row selected.

SQL>

===============================================================================================
冷备
SQL> ho vim cold.sql

set heading off
set feedback off
select 'ho cp '||member||' /oracle/bak/'  from v$logfile
union select 'ho cp '||name||' /oracle/bak/'  from v$tempfile
union select 'ho cp '||name||' /oracle/bak/' from v$datafile
union select 'ho cp '||name||' /oracle/bak/' from v$controlfile;


SQL> @cold.sql

ho cp /oracle/oradata/orcl/control01.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/control02.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/control03.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/example01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/redo01.log /oracle/bak/
ho cp /oracle/oradata/orcl/redo02.log /oracle/bak/
ho cp /oracle/oradata/orcl/redo03.log /oracle/bak/
ho cp /oracle/oradata/orcl/sysaux01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/system01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs1.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs2.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs3.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/temp01.dbf /oracle/bak/

ho cp /oracle/oradata/orcl/undotbs01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/users01.dbf /oracle/bak/


SQL> ho vim coldmv.sql

ho cp '/oracle/db/dbs/spfileorcl.ora'  /oracle/bak/
ho cp '/oracle/db/dbs/orapworcl'  /oracle/bak/

ho cp /oracle/oradata/orcl/control01.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/control02.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/control03.ctl /oracle/bak/
ho cp /oracle/oradata/orcl/example01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/redo01.log /oracle/bak/
ho cp /oracle/oradata/orcl/redo02.log /oracle/bak/
ho cp /oracle/oradata/orcl/redo03.log /oracle/bak/
ho cp /oracle/oradata/orcl/sysaux01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/system01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs1.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs2.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/tbs3.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/temp01.dbf /oracle/bak/

ho cp /oracle/oradata/orcl/undotbs01.dbf /oracle/bak/
ho cp /oracle/oradata/orcl/users01.dbf /oracle/bak/

SQL> @coldmv.sql

[oracle@aa bak]$ ls /oracle/bak/
control01.ctl  orapworcl   spfileorcl.ora  tbs2.dbf       users01.dbf
control02.ctl  redo01.log  sysaux01.dbf    tbs3.dbf
control03.ctl  redo02.log  system01.dbf    temp01.dbf
example01.dbf  redo03.log  tbs1.dbf        undotbs01.dbf

================================================================================================
exp和imp
create user ly identified by ly account unlock;
create user ly02 identified by ly02 account unlock;
create user ly03 identified by ly03 account unlock;
grant connect,resource to ly;
grant connect,resource to ly02;
grant connect,resource to ly03;
grant exp_full_database to ly03;
grant imp_full_database to ly03;


[oracle@aa exp]$ exp tables=ly.meixi file=/oracle/exp/meixi.dmp
Export: Release 10.2.0.1.0 - Production on Tue Jun 28 16:56:31 2011

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


Username: ly
Password:

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

About to export specified tables via Conventional Path ...
. . exporting table                          MEIXI      50336 rows exported
Export terminated successfully without warnings.

[oracle@aa exp]$ exp tables=ly.meixi file=/oracle/exp/meixi.dmp        覆盖了

Export: Release 10.2.0.1.0 - Production on Tue Jun 28 16:56:41 2011

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


Username: sys as sysdba
Password:

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

About to export specified tables via Conventional Path ...
Current user changed to LY
. . exporting table                          MEIXI      50336 rows exported
Export terminated successfully without warnings.


SQL> drop table ly.meixi purge;

Table dropped.


[oracle@aa exp]$ imp tables=meixi file=/oracle/exp/meixi.dmp fromuser=ly touser=ly          因为是用DBA用户导出的,所以只能DBA来进行导入。但是因为属主又是ly,所以后面加上fromuser=ly touser=ly

Import: Release 10.2.0.1.0 - Production on Tue Jun 28 16:59:15 2011

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing LY's objects into LY
. . importing table                        "MEIXI"      50336 rows imported
Import terminated successfully without warnings.


[oracle@aa exp]$ imp tables=meixi file=/oracle/exp/meixi.dmp indexfile=/oracle/exp/a.txt fromuser=ly touser=ly    只看结构信息

Import: Release 10.2.0.1.0 - Production on Tue Jun 28 17:04:23 2011

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . skipping table "MEIXI"                           

Import terminated successfully without warnings.
[oracle@aa exp]$ cat /oracle/exp/a.txt

REM  CREATE TABLE "LY"."MEIXI" ("OWNER" VARCHAR2(30), "OBJECT_NAME"
REM  VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER,
REM  "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE,
REM  "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7),
REM  "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY"
REM  VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM  STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM  ... 50336 rows


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

SQL> create table ly.aa as select * from dba_objects;

Table created.


[oracle@aa exp]$ exp tables=ly.aa file=/oracle/exp/lyaa.dmp

Export: Release 10.2.0.1.0 - Production on Tue Jun 28 18:39:35 2011

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


Username: ly03
Password:

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

About to export specified tables via Conventional Path ...
Current user changed to LY
. . exporting table                             AA      50337 rows exported
Export terminated successfully without warnings.


[oracle@aa exp]$ imp tables=aa file=/oracle/exp/lyaa.dmp fromuser=ly touser=ly02
 
Import: Release 10.2.0.1.0 - Production on Tue Jun 28 18:41:03 2011

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

Username: ly03
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing LY's objects into LY02
. . importing table                           "AA"      50337 rows imported
Import terminated successfully without warnings.

------------------------------------------------------------------------------------------------
SQL> conn ly/ly
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
AA                             TABLE
MEIXI                          TABLE
ABC                            TABLE

4 rows selected.

SQL> select * from abc;

        ID
----------
         1
         2
         3

3 rows selected.


[oracle@aa exp]$ exp tables=ly.aa,ly.abc file=/oracle/exp/q.dmp

Export: Release 10.2.0.1.0 - Production on Tue Jun 28 18:46:55 2011

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


Username: ly03
Password:

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

About to export specified tables via Conventional Path ...
Current user changed to LY
. . exporting table                             AA      50337 rows exported
. . exporting table                            ABC          3 rows exported
Export terminated successfully without warnings.


SQL> conn ly02/ly02
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
MEMP                           TABLE

1 row selected.

[oracle@aa exp]$ imp tables=abc file=/oracle/exp/q.dmp fromuser=ly touser=ly02

Import: Release 10.2.0.1.0 - Production on Tue Jun 28 18:51:43 2011

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

Username: ly03
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing LY's objects into LY02
. . importing table                          "ABC"          3 rows imported
Import terminated successfully without warnings.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ABC                            TABLE
MEMP                           TABLE

2 rows selected.