Oracle笔记

########################################
#logmnr
mkdir /logmnr
chown -R oracle:dba /logmnr
alter system set utl_file_dir='/logmnr' scope=spfile;
shutdown immediate; startup;
rman cmdfile='....rcv' log='...log' append
alter system switch logfile;
select * from v$archived_log;
execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/logmnr');
begin \
  dbms_logmnr.add_logfile(logfilename=>'/rman/arch/1_7_727818858.dbf',options=>dbms_logmnr.new); \
end;
select * from v$logmnr_logs;
execute dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dictionary.ora');
select * from v$logmnr_contents;
execute dbms_logmnr.end_logmnr;


######################################
#手工冷备份恢复
------
set pagesize 0 linesize 32767 feedback off verify off trimspool on termout off trimout on serveroutput on
define fil=/rman/cold/back.sql
define log=/rman/cold/back.log
spool &fil
prompt connect /as sysdba;
prompt shutdown immediate;
select 'host cp '||file_name||' /rman/cold'||substr(file_name,instr(file_name,'/',-1,1)) from dba_data_files;
prompt startup;
spool off
spool &log
@&fil;
spool off
------
#no-system tablespace
recover datafile 5;
alter database datafile 5 online;
#system tablespace
shutdown immediate;  startup mount;
recover database;
alter database open;


#undo tablespace
alter system checkpoint;
recover datafile 3;


#redo log  #backup control file
alter database backup controlfile to trace;
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
recover database using backup controlfile until cancel;
select * from v$log;
select * from v$logfile;


#control file
select * from v$controlfile;
脚本创建control
recover database using backup controlfile until cancel;
recover database using backup controlfile;


#temp tablespace
select tablespace_name from dba_tablespaces where contents='TEMPORARY';
explain plan for select * from table_name order by xxx;
select * from table(dbms_xplan.display);
alter tablespace temp add tempfile '' size 20M autoextend off;
alter tablespace temp drop tempfile 1;


######################################
#热备份恢复 
archive log list;
startup mount; alter database noarchivelog; alter database archivelog;
alter tablespace users begin backup;
alter tablespace users end backup;
------
set pagesize 0 feedback off heading off verify off linesize 32767 trimspool on termout off serveroutput on
remark create backup dir
define dir='/rman/hot'
define fil='&dir/hot_back.sql'
define log='&dir/hot_back.log'
spool &fil
prompt connect /as sysdba
prompt alter system switch logfile;
declare
  cursor cur_tablespace is
    select tablespace_name from dba_tablespaces where status<>'READ ONLY' and contents<>'TEMPORARY';
  cursor cur_datafile(tn varchar) is
    select file_name from dba_data_files where tablespace_name=tn;
begin
  for ct in cur_tablespace loop
    dbms_output.put_line('alter tablespace '||ct.tablespace_name||' begin backup;');
    for cd in cur_datafile (ct.tablespace_name) loop
      dbms_output.put_line('!cp '||cd.file_name||' &dir;');
    end loop;
    dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup;');
  end loop;
end;
/
prompt alter system switch logfile;
prompt alter database backup controlfile to '&dir/control.ctl' reuse;
prompt alter database backup controlfile to trace;
spool off;
remark exec
spool &log
@&fil
spool off
remark v$backup v$archived_log
remark select max(sequence#) from v$archived_log where first_change# < (select min(change#) from v$backup);
------
#复制数据库
复制参数文件、创建目录、创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwCAT password=oracle entries=10
必要的时候创建controlfile
-----
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "CAT" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/oradata/CAT/redo01.log'  SIZE 102000K BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/CAT/redo02.log'  SIZE 102000K BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/CAT/redo03.log'  SIZE 102000K BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/oradata/CAT/system01.dbf',
  '/u01/oracle/oradata/CAT/sysaux01.dbf',
  '/u01/oracle/oradata/CAT/undotbs01.dbf',
  '/u01/oracle/oradata/CAT/users01.dbf'
CHARACTER SET AL32UTF8
;
-----
拷贝备份文件到相应的目录
recover database using backup controlfile until cancel;
alter database open resetlogs;
!ipcs
select instance_name from v$instance;


###########################################
#RMAN catalog
create tablespace rman11 datafile '/u01/oracle/oradata/CAT/rman1101.dbf' size 100M;
create user rman11 identified by rman11 temporary tablespace temp default tablespace rman11 quota unlimited on rman11;
grant connect, recovery_catalog_owner to rman11;
select * from session_privs;
select * from session_roles;
rman connect catalog rman11/rman11@cat;  create catalog tablespace rman11;
rman connect target /; connect catalog rman11/rman11@cat;  register database;
list incarnation;  report schema;  list copy;(列出archive)  list backup by file;
crosscheck copy;  delete expired copy;  crosscheck copy of archivelog all;
crosscheck archivelog all; list archivelog all;
change backupset ...;  validate backupset ...;
------------------
run {
allocate channel d1 device type disk;
delete noprompt force obsolete recovery window of 15 days;
backup as compressed backupset incremental level=0 format='/rman/inc0_%d_%T_%s_%p' tag='inc0' channel=d1 database;
sql "alter system archive log current";
backup as compressed backupset format='/rman/arch_%d_%T_%s_%p' tag='arch' channel=d1 archivelog all delete input;
backup as compressed backupset format='/rman/ctl_%d_%T_%s_%p' tag='ctl' channel=d1 current controlfile;
backup as compressed backupset format='/rman/spfile_%d_%T_%s_%p' tag='spfile' channel=d1 spfile;
release channel d1;
}
------------------
增量备份：默认为增量型备份，cumulative为累计型增量备份
--------
run {
allocate channel d1 device type disk;
backup as compressed backupset format='/rman/tbs_%d_%T_%s_%p' tag='tbs' channel=d1 tablespace users,system;
release channel d1;
}
--------
--------
run {
allocate channel d1 device type disk;
backup as compressed backupset format='/rman/dfile_%d_%T_%s_%p' tag='dfile' channel=d1 datafile 1;
release channel d1;
}
--------
--------
run {
allocate channel d1 device type disk;
backup as compressed backupset format='/rman/arch_%d_%T_%s_%p' tag='arch' channel=d1 archivelog from scn 1161320;
release channel d1;
}
--------
--------
run {
allocate channel d1 device type disk;
backup as copy format='/rman/copy_%d_%T_%s_%p' tag='copy' channel=d1 database;
release channel d1;
}
--------


###########################################
#RMAN crontab
Differential incremental & Cumulative incremental
External script(调用文件用@@) & Catalog script(Global,Current)
rman cmdfile & rman list global script names; & print [global] script script_name;
channel readrate/kbytes & set limit channel d1 readrate=10240;
delete obsolete recovery window of x days;
rman> replace [global] script script_name comment "xxxx" {...}
run {execute global script script_name;}
nohup $ORACLE_HOME/bin/rman cmdfile=xxx log=xxx append &  #full path
#experience
-------
backup controlfile in each script's tail
delete obsolete backupset in each script's threshold
switch logfile before backup database
chmod u+x *.sh
-------
-------
replace global script g_back_inc0 comment "backup incremental level 0" {
execute global script g_del;
allocate channel d1 device type disk;
set limit channel d1 readrate=10240;
set limit channel d1 kbytes=102400;
backup as compressed backupset incremental level=0 format='/rman/inc0_%d_%T_%s_%p' tag='inc0' channel=d1 database;
release channel d1;
execute global script g_back_arch;
execute global script g_back_ctl;
execute global script g_back_spfile;
}
-------


###########################
#RMAN restore & recover
Physical failure : Datafile,controlfile,redo log
Logical mistake : object.
Mount or open : Non-system & Undo tablespace
Nomount : Controlfile, all database file
Mount : System tablespace & online redo log file
validate backupset 1518,1519;
validate backupset 1518,1519 check logical;
#non-system tablespace
------
run {
allocate channel d1 device type disk;
sql "alter database datafile 4 offline";
restore datafile 4;
recover datafile 4;
sql "alter database datafile 4 online";
}
------
#undo tablespace
------
 run {
 allocate channel d1 device type disk;
 restore datafile 3;
 recover datafile 3;
}
SQL> recover datafile 3;
SQL> alter database datafile 3 online;
------
#system tablespace
------
startup mount;
run {
 allocate channel d1 device type disk;
 restore datafile 1;
 recover datafile 1;
 sql "alter database open";
}
------
#controlfile
-----
startup nomount;
run {
allocate channel d1 device type disk;
restore controlfile;
sql "alter database mount";
recover database;
sql "alter database open noresetlogs";
}
-----
-----
run {
allocate channel d1 device type disk;
restore database;
recover database;
}
-----
#online redo logfile  #mount
run {
allocate channel d1 device type disk;
restore database;
recover database;
}
recover database until logseq 32;
alter database open resetlogs;
#All database
nomount : restore database
mount   : recover database  & recover database until logseq xx;
alter database open resetlogs;


###############################
#RMAN duplicate db to ASM
whoami ; fdisk -l ; install asmrpm(oracleasmlib,oracleasm,oracleasm-support)
#create dir(adump bdump cdump udump) & password & pfile
orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=oracle entries=10
vi $ORACLE_HOME/dbs/init+ASM.ora
-----
*.asm_diskstring=''
*.background_dump_dest='/u01/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/oracle/admin/+ASM/cdump'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='SHARED'
*.user_dump_dest='/u01/oracle/admin/+ASM/udump'
-----
select * from v$asm_disk;
select * from v$asm_diskgroup;
select * from v$asm_alias;
select * from v$asm_template;
select * from v$asm_file;
select * from v$asm_disk_stat;
select * from v$asm_operation;  #显示当前的平衡(发布负载均衡的命令),平常没数据
select * from v$fixed_table;
select sum(bytes)/1024/1024/1024 || ' G' from v$datafile;
select sum(bytes)/1024/1024/1024 || ' G' from v$tempfile;
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm listdisks
$ORACLE_HOME/bin/localconfig add
#createdisk(要分区) or raw(裸设备映射，可以不用分区，也可分区) #两个一样的只是两种方法
/etc/init.d/oracleasm createdisk xx
vi /etc/sysconfig/rawdevices
-----
/dev/raw/raw1 /dev/sdb   #/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc   #/dev/raw/raw2 /dev/sdc1
/dev/raw/raw3 /dev/sdd   #/dev/raw/raw3 /dev/sdd1
-----
vi /etc/udev/permissions.d/50-udev.permissions  #修改raw的权限给oracle
-----
# raw devices
ram*:root:disk:0660
raw/*:oracle:dba:0660   #修改这一行
-----
service rawdevices restart
raw -qa & ll /dev/raw/*
#asm instance 不能使用用户名登录，因为他没有数据字典
sqlplus /as sysdba
create diskgroup dg1 external redundancy disk '/dev/raw/raw1';
#如果只有1个scsi总线，normal意义不大
create diskgroup dg2 normal redundancy failgroup fg1 disk '/dev/raw/raw2' failgroup fg2 disk '/dev/raw/raw3';
drop diskgroup dg1;
alter diskgroup dg1 mount;
alter diskgroup dg1 dismount;
select * from v$log_history;
asm数据库的参数文件
----
....
*.control_files='+DG1/dgasm/controlfile/control.ctl'
*.log_archive_dest_1='LOCATION=+DG1/dgasm/'
....
*.db_create_file_dest='+DG1'
#同一台机器duplicate需要，做完转换就不再需要了
*.db_file_name_convert=("clonedb","dgasm")
*.log_file_name_convert=("clonedb","dgasm")
----
复制脚本
----
replace global script g_duplicate comment "duplicate database" {
allocate auxiliary channel d1 device type disk;
set until logseq 3 thread 1;
set newname for datafile 1 to '+DG1';
set newname for datafile 2 to '+DG1';
set newname for datafile 3 to '+DG1';
set newname for datafile 4 to '+DG1';
set newname for tempfile 1 to '+DG1';
duplicate target database to dgasm logfile
group 1('+DG1') size 50M reuse,
group 2('+DG1') size 50M reuse,
group 3('+DG1') size 50M reuse;
}
----
----
run {
connect auxiliary /;
execute global script g_duplicate;
}
----


#########################
#RMAN & DBMS_backup_restore异地复制
service --status-all | grep nfs
service nfs start
exportfs *:/rman
exportfs -ua
exportfs -o rw *:/rman
mount -t nfs -o rw ip:/rman /nfsdir
umount /nfsdir
change backupset 2578,2579,2679 delete;
change archivelog all delete;
#相同的SID
----
run {
allocate auxiliary channel d1 device type disk;
duplicate target database to clonedb nofilenamecheck [until logseq 7];
}
----
#不同的SID
----
connect auxiliary /;
run {
allocate auxiliary channel d1 device type disk;
set until logseq 7 thread 1;
set newname for datafile 1 to '/oradata/digoal/system.dbf';
set newname for datafile 2 to '/oradata/digoal/undotbs1.dbf';
set newname for datafile 3 to '/oradata/digoal/sysaux.dbf';
set newname for datafile 4 to '/oradata/digoal/users.dbf';
set newname for tempfile 1 to '/oradata/digoal/temp.dbf';
duplicate target database to digoal logfile
group 1 ('/oradata/digoal/redo01_1.ora') size 50M reuse,
group 2 ('/oradata/digoal/redo02_1.ora') size 50M reuse,
group 3 ('/oradata/digoal/redo03_1.ora') size 50M reuse;
}
----
#DBMS_backup_restore
----
DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');
  sys.dbms_backup_restore.restoreSetDatafile;
  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/oradata/dg/system.dbf');
  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/oradata/dg/undotbs1.dbf');
  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/oradata/dg/sysaux.dbf');
  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/oradata/dg/users.dbf');
  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/rman/inc0_xxxx',params=>null);
  sys.dbms_backup_restore,deviceDeallocate;
END;
/
----
----
DECLARE
  devtype varchar2(256);
  done boolean;
BEGIN
  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');
  sys.dbms_backup_restore.restoreSetArchivedLog(destination=>'oradata/dg/archive/');
  sys.dbms_backup_restore.restoreArchivedLogRange;
  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/rman/arch_xxxx',params=>null);
  sys.dbms_backup_restore,deviceDeallocate;
END;
/
----
----
recreate controlfile & backup controlefile for standby
----
set logsource '/oradata/dg/archive';
recover database using backup controlfile until cancel;  #cancel
alter database open resetlogs;


######################################
#statspack  $ORACLE_HOME/rdbms/admin
spcreate.sql
spdrop.sql
spdoc.txt
spauto.sql
sprepcon.sql #参数文件
sptrunc.sql
drop user perfstat cascade;
drop tablespace perfstat including contents and datafiles;
create tablespace perfstat datafile '/u01/oracle/oradata/ORCL/perfstat01.dbf' size 200M autoextend off;
@?/rdbms/admin/spcreate.sql
conn perfstat/perfstat
execute statspack.snap;
select * from stats$statspack_parameter;
select * from stats$snapshot;
vi spauto.sql
----
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/
----
select * from uses_jobs;
execute dbms_job.interval(23,'sysdate+1/(24*60)'); commit;
execute dbms_job.remove(23);
define
#report Instance report & sql report
spreport.sql , sprepins.sql(multi-instance) & sprepsql.sql, sprsqins.sql(multi-instance)
@?/rdbms/admin/spreport.sql
show parameter statistics
execute statspack.snap(i_snap_level=>7);
execute statspack.snap(i_snap_level=>7,i_modify_parameter=>'true');
execute statspack.modify_statspack_parameter(i_snap_level=>6);
#maintenance
execute statspack.make_baseline(i_begin_snap=>1,i_end_snap=>16);
execute statspack.purge(i_begin_snap=>1,i_end_snap=>72);
execute statspack.clear_baseline(i_begin_snap=>1,i_end_snap=>16,i_snap_range=>false);
exp userid=perfstat/perfstat owner=perfstat direct=y consistent=y file=~/perfstat.dmp grants=y indexes=y
@?/rdbms/admin/sptrunc.sql
@?/rdbms/admin/spdrop.sql  # sysdba
#dbms_stats
execute dbms_stats.gather_schema_stats(ownname=>'PERFSTAT',cascade=>true);
#upgrade
spup10.sql




#################################
#ASM 提供条带和镜像的功能
将磁盘总空间划分为统一大小的1MB单元
磁盘组冗余：外部(进行硬件镜像)，正常(两向镜像)，高(三向镜像)
asm在区的级别上镜像，在每个磁盘上混合元区与镜像区
asm故障组：是共享公用资源的一组磁盘，区的冗余副本存储在单独的故障组中
ASM实例 SGA=64M
ASMLib install http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm disable
/etc/init.d/oracleasm enable
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm scandisks
#分区
fdisk sdb ; n ; p ; 1; w;
/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
vi $ORACLE_HOME/dbs/init+ASM.ora
-----
*.asm_diskstring='ORCL:VOL*'
*.background_dump_dest='/u01/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/oracle/admin/+ASM/cdump'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='SHARED'
*.user_dump_dest='/u01/oracle/admin/+ASM/udump'
-----
.bash_profile  ; stty erase ^h
create diskgroup dgroup1 normal redundancy failgroup fgroup1 disk 'ORCL:VOL1','ORCL:VOL2' failgroup fgroup2 disk 'ORCL:VOL3','ORCL:VOL4';
select * from v$asm_diskgroup;
#alter diskgroup dgroup1 mount;
show parameter asm_diskgroups
#automatic rebalance ; add disk; remove disk
alter diskgroup dgroup1 add failgroup fgroup1 disk 'ORCL:VOL5' failgroup fgroup2 disk 'ORCL:VOL6';
alter diskgroup dgroup1 drop disk VOL4;
alter diskgroup dgroup1 rebalance;
select * from v$asm_diskgroup;
select * from v$asm_disk;
select * from v$asm_operation;


################################
#RAC


################################
#Golden Gate


########################################
PLSQL
SQL引擎在服务器端，PLSQL引擎可以在服务器端，也可以在客户端
[DECLARE] BEGIN [EXCEPTION] END;
Anonymous Procedure Function
set serveroutput on; help set;
dbms_output.put_line();
v_event := q'!Father's day!';  or v_event := 'Father''s day'
PLSQL variables(Scalar/Composite/Reference/Large object/bfile) & Non-PLSQL variables(Bind variables)
%TYPE & %ROWTYPE : table.colmun_name or 其他变量
Bind variables(host variables) : sqlplus define by VARIABLE 前面加:使用
-----------
VARIABLE b_emp_salary NUMBER
BEGIN
  SELECT salary INTO :b_emp_salary FROM employees WHERE employee_id=178;
END;
/
PRINT b_emp_salary
SELECT first_name,last_name FROM employees WHERE salary=:b_emp_salary;
-----------
set autoprint on
替换变量
TO_CHAR TO_DATE TO_NUMBER TO_TIMESTAMP
LABLE : BEGIN <<outer>> ... END outer;
vi技巧: :set autoindent(自动缩进) ; :set tabstop=4 (tab换成4个空格)
PLSQL SQL Statements : SELECT DML COMMIT ROLLBACK SAVEPOINT
v$transaction
Cursor : Implicit & Explicit
SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT
UPDATE xxx RETURNING xxx INTO xxx
UPDATE emp SET salary = salary*1.5 WHERE empoyee_id=196 RETURNING salary,last_name INTO mysal,myname;
IF CASE(case表达式END; case语句END CASE;) LOOP (LOOP WHILE FOR)
TRUE & FALSE NULL
NULL; 可以作为独立的语句使用
CONTINUE [WHEN xxx]
GOTO label_name;
RECORD
UPDATE table_name SET ROW = v_emp_rec(%ROWTYPE类型) WHERE empno=v_employee_number;
Collect : Associative Arrays(INDEX BY Tables),嵌入表，可变长数组
For strings : NLS_SORT & NLS_COMP
TYPE type_name IS TABLE OF ...
Methods : xxx.FIRST  xxx.NEXT xxx.COUNT x.DELETE x.EXISTS ...
INDEX by Strings : hash
SUBTYPE l_loca IS varchar2(64)
Hash Functions : MD5(128bits) & SHA1(160bits)
md5sum & sha1sum
#CURSOR
FOR .. IN .. LOOP .... END LOOP;
%ISOPEN %NOTFOUND %FOUND %ROWCOUNT
参数CURSOR
SELECT .. FOR UPDATE
WHERE CURRENT OF cursor;
BULK COLLECT INTO ...
#EXCEPTION
raise
TOO_MANY_ROWS NO_DATA_FOUND ...
WHEN OTHERS
-------
自制exception
DECLARE 
  e_insert_execp EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_insert_execp,-01400);
BEGIN
  ...
EXCEPTION
  WHEN e_insert_execp THEN
    DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-------
SQLCODE & SQLERRM
PRAGMA AUTONOMOUS_TRANSACTION; #自制事务，一般使用与记录错误日志
自定义exception
------
DECLARE 
  e_invalid_department EXCEPTION;
BEGIN
  ...
  IF SQL%NOFOUND THEN
    RAISE e_invalid_department;
  END IF;
  ...
EXCEPTION
  WHEN e_invalid_department THEN
    DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
------
RAISE_APPLICATION_ERROR(error_number,message[,{TRUE|FALSE}])
error_number范围：-20000..-20999
Propagating Exceptions #异常的扩散
Tips
#Create Procedure
CREATE OR REPLACE ...
IN & OUT & IN OUT
SHOW ERRORS & USER_ERRORS
USER_SOURCE
#Create Function
CREATE OR REPLACE ... RETURN datatype IS|AS ...

PARALLEL_ENABLE #并发执行

-The End-