########################################
#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 ...
#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-