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> }