LogMiner 笔记2

alter system set log_Archive_dest_1='location= /home/oracle/app/oracle/product/11.2.0/dbhome_2/logmnr' scope=both

alter system set log_Archive_dest_1='location=e:\arch' scope=both;

@/home/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/dbmslms.sql

alter system set utl_file_dir='/home/oracle/app/oracle/product/11.2.0/dbhome_2/logmnr' scope=spfile
exec dbms_logmnr_d.build(dictionary_filename=>'logminer',dictionary_location=>'/home/oracle/app/oracle/product/11.2.0/dbhome_2/logmnr');
exec dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location => '/home/oracle/app/oracle/product/11.2.0/dbhome_2/logmnr');

exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo03.log',options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo01.log',options=>dbms_logmnr.andfile);

exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo02.log',options=>dbms_logmnr.addfile)；

/home/oracle/app/oracle/oradata/orcl/redo03.log

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/app/oracle/product/11.2.0/dbhome_2/logmnr/dictionary.ora')

select session_info from v$logmnr_contents where username='SCOTT' and  operation=upper('DELETE') and TABLE_NAME='T1'
select sql_redo from v$logmnr_contents where username='SCOTT' and  operation=upper('DELETE') AND TABLE_NAME='T1'
select COMMIT_TIMESTAMP ,USERNAME,STATUS,REDO_VALUE    from v$logmnr_contents where username='SCOTT' and  operation=upper('DELETE') AND TABLE_NAME='T1'

execute dbms_logmnr.end_logmnr;

create profile p1 limit FAILED_LOGIN_ATTEMPTS 2;
alter user u1 profile p1;
select USERNAME,PROFILE from dba_users;
select USERNAME,ACCOUNT_STATUS from dba_users where username=‘U1’;
DROP PROFILE P1 CASCADE
Drop user u1 cascade
grant select any table to u2;
select dbms_metadata.get_ddl('TABLE','EMP') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_EMP') FROM DUAL;

exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo01.log',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/oradata/orcl/redo02.log',options=>dbms_logmnr.addfile);

02:53:42 SQL> select versions_starttime,versions_operation,empno,sal from scott.t1 versions between timestamp minvalue and maxvalue where empno=7698;

rman>show all

 

startup nomount
rman target /

restore controlfile from 'E:/oracle/product/10.2.0/flash_recovery_area/AIB
O/BACKUPSET/2010_11_02/O1_MF_NCSNF_TAG20101102T113914_6DZ2153F_.BKP';
alter database mount;
restore database;
recover database;
> alter database open resetlogs;

 

limit exceeded for recovery files问题解决
show parameter db_recovery_file_dest;
ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both;

零级备份

    backup incremental level 0 database;
   
一级差异增量
      backup incremental level 1 database;
   
一级累计增量
 
    backup incremental level 1 cumulative database;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/app/oracle/bak/ctf/%d_ctf_bak_%F';

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   '/home/oracle/app/oracle/bak/disk1/%d_datafile_bak_%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   '/home/oracle/app/oracle/bak/disk2/%d_datafile_bak_%U';

 

select instance_name,version,status,archiver,database_status from v$instance
 --查看数据文件及状态信息
    SQL> select file_name,tablespace_name,status,online_status from dba_data_files;

select name from v$datafile;
select name from v$tempfile
select member from v$logfile
select name from v$controlfile;

ORACLE 冷备份

#rem script.:coldbak.sql
#rem desc:offline full backup database
#enter database
$ORACLE_HOME/bin/sqlplus "/as sysdba" <<EOF
#--shutdown database
shutdown immediate
#--Copy  file
!cp /home/oracle/app/oracle/oradata/orcl/*.ctl /home/oracle/tmp/
#--startup database
startup

 

--下面启用级增量备份
        RMAN> run{

        2> allocate channel ch1 type disk;

        3> backup incremental level 0 database

        4> format '/u01/app/oracle/rmanbak/db_%d_%U'

        5> tag=db_inc_0;

        6> release channel ch1;

        7> }

 

        SQL> select sid,sofar,totalwork from v$session_longops;  --查询备份情况
 

        --下面启用级差异增量备份
        RMAN> run{

        2> allocate channel ch1 type disk;

        3> backup incremental level 1 database

        4> format '/u01/app/oracle/rmanbak/db1_%d_%U'

        5> tag=db_inc_1;

        6> release channel ch1;

        7> }

 

转载于:https://www.cnblogs.com/andybox/archive/2013/05/29/3106277.html