2013-06-30

###exalogic

exabus #CPU & IO

traffic director

smart scan

###exadata

ipmitool sel list #system eventlog, or used to shutdown other exadata node(s)

set|grep OSS

cellsrv #oracle storage server software. cellcli. restart server;cellsrv;OSWatcher

iDB #use RDS(not UDP) protocol, low-latency protocol that bypasses kernel calls by using RDMA(remote direct memory access)

alter session set cell_offload_processing=false;

#cellcli

cellcli -e “list cell<objectType> detail<from column oriented to row oriented>”

cellcli -e “list cell attributes status, cellnumber” #cellcli -e “help list cell”. first describe cell to get the attrbutes of the cell objectType

cellcli > set echo on

cellcli > list flashcachecontent where dbUniqueName like ‘EXDB’ and hitcount > 100 attributes dbUniqueName, objectNumber, missCount

@script_name.txt #execute the commands in script_name.txt

cellcli > alter cell shutdown/startup/restart services cellsrv/ms/rs/all

cellcli > alter cell notificationPolicy=”critical,warning,clear”, notificationMethod=”mail”

#dcli, execute a single command across all cells/database servers

dcli -l root -g dbs_group/cell_group/all_group ps -ef|grep ora_pmon|grep -v grep<cellcli -e “list cell”> #shows the pmon processes running on all database servers

dcli -g mycells -k

dcli -c stsd2s2,stsd2s3 vmstat

dcli -g mycells cellcli -e alter iormplan active

dcli -g mycells -x reConfig.scl

###SQL

#others

. oraenv

export TNS_ADMIN=/u01/oracle/OracleHomes/db10g/network/admin

sqlplus sysman/password@sid

sqlplus /nolog

SQL>conn sys/password@sid as sysdba

; #show last command

/ #redo last command

set lines<ize> 120;

col file_name format a60;

set timing on

set autot on explain; #explain automatically

alter session/system set … scope #spfile(next reboot)/memory(only current)/both(this is default)

startup force #restart instance

create pfile=’xxx’ from spfile;

create spfile from pfile;

ps -ef|grep LISTENER

export ORACLE_HOME=<xxx/server>

export ORACLE_SID=<xx>

#users, privileges, passwords

SQL>show user

SQL>alter user xxx account unlocked;

SQL>password <account> unlock;

SQL>grant select on emp to hr;

SQL>grant all privileges to test; #GRANT SELECT ON hr.jobs to test;

SQL>revoke create table from joe #not cascading

#table, table space, indexes

SQL> select OWNER,TABLE_NAME from dba_all_tables th were t.TABLE_NAME like ‘OVS_OS_RESOURCE%’;

SQL> insert into OVS.OVS_OS_RESOURCE select * from OVS.OVS_OS_RESOURCE_BACKUP;

SQL>select * from tab;

SQL>create table tab1 tablespace tbs1 as select * from tab2;

SQL>truncate table emp;

SQL>select username,ACCOUNT_STATUS from  dba_users;

SQL>create index id on obj(object_name);

SQL>alter table obj move; #invalid index

SQL>alter index id rebuild; #rebuild index

SQL>create tablespace tbs1 datafile ‘+DATA’ size 100m uniform size 1m;#OMF, oracle managed files

SQL>create database link p6 connect to hr identified by oracle_4U using ‘p6′; select * from jobs@p6; #query across different hosts, cross query

SQL>select to_char(WMSYS.WM_CONCAT(dba_users.username)) user_list from dba_users where expiry_date-sysdate<15 and username not in (select username from dba_users where expiry_date<sysdate); #expire in 15 days

###table and view

user_ #current user’s, user_tables

all_ #current user’s, include granted

dba_ #all, dba_users, dba_indexes

dictionary #data dictionary’s data dictionary

v$fixed_table #dynamic view’s dynamic view

dba_tables, dba_index, dba_objects

v$session, v$lock

v$asm_disk, v$asm_diskgroup

v$database(flashback, archive log), v$instance(uptime, status, nomount corresponds to started)

v$parameter(for current session), v$system_parameter(for new sessions), v$spparameter

v$process(OS processes), v$bgprocess, v$px_process_sysstat

v$system_event

v$datafile, v$log, v$logfile, v$log_history(logrotate), v$archived_log(where put the archived log)

v$filestat, v$temp_file, v$tempseg_usage, v$segment_statistics, v$buffer_pool_statistics, v$librarycache(hit ratio)

v$sgainfo, v$pgastat, v$undostat(every 15 minutes), v$waitstat, v$latch(memory lock info)

v$sql #select * from v$sql where cpu_time>200000 #20s

#OS commands

dbca, asmca #xming/export DISPLAY. show parameter asm_power_limit #power of RBAL(1 to 11, should be set to night)

asmcmd -p #su – grid; export ORACLE_SID=+ASM; asmcmd -p ls -l; lsdg; find -t datafile DATA/ sys*(SQL> select type from V$ASM_FILE group by TYPE)

emctl status/start/stop dbconsole/agent/oms

rman, netmgr, netca, lsnrctl

orapwd #sync dictionary/password file after upgrading oracle db

adrci #manage alert/trace files; show home;set home diag/rdbms/orcl6/orcl6;show alert -p “message_text like ‘%start%’”; show alert -tail 5 -f; adrci exec=”show home”

ADR #automatical diagnotics repository, adrci

AWR #The Automatic Workload Repository is used for storing database statistics that are used for performance tuning

ADDM(automatic database diagnostic monitor):diagnose AWR report and resolve some problems automatically

#Moving Data

imp/exp scott/tiger file=scott.exp #logical export, client based

sql*loader(non-oracle DB)

expdp, impdp, can not be used when db is read only #data pump, SQL>alter tablespace tbs1 read only #drop table test; is ok, because drop only change the name in dictionary

cd /backup #directory object

SQL>create directory backup_dir as ‘/backup/’;

SQL>grant read,write on directory backup_dir to scott;

expdp scott/tiger dumpfile=scott.dmp directory=backup_dir (tables=emp);

expdp \”/ as sysdba \” schema=scott dumpfile=scott.dmp directory=backup_dir;(include sql to create user and grants)

#Patch

interim

CPU(critical patch updates, through opatch)

patch release(runInstaller)

$ORACLE_HOME/Opatch/opatch query -is_online_patch <path> OR opatch query <path> -all #opatch

opatch lsinventory(what patches have been applied)

opatch lsinventory/opatch version #. oraenv first

#undo

undo status #active, unexpired, expired

alter tablespace xxx retention guarentee #900 seconds

no guarentee #unexpired can also be overrided, so may not guarantee 900 seconds of read consistency

every instance needs one undo #even in RAC, show parameter undo/dba_tablespace

SQL>flashback table emp to before drop;

#locks

dba_objects

v$session(who blocks who, paddr)

v$process(addr)

v$lock #sid(session id)

pid(process id in oracle)

spid(server process id in OS)

alter system kill session ‘a,b’ immediate; #if still can not be killed, should kill spid.

#RMAN

startup mount

alter database archivelog;

alter database open;

alter system archive log current;

SQL> select LOG_MODE from v$database;

SQL>alter system set control_files=’xxx’,'xxx’,'xxx’ scope=spfile

SQL> archive log list

rman target /

RMAN> show all

rman>backup current controlfile;

RMAN> backup database plus archivelog;

SQL> alter database backup controlfile to ‘/u01/app/oracle/control.bak’;

SQL> alter database backup controlfile to trace as ‘/u01/app/oracle/control.trace’; #text file

rman>backup tablespace new_tbs;

<then datafile lost and error occurs while querying/create table>

SQL>col NAME for a60

SQL> select FILE#, STATUS, NAME from v$datafile;

rman>restore datafile <10>;

rman>recover datafile 10(apply redo log);

SQL>alter tablespace new_tbs online;

rman>list failure;  #Data recovery advisor

rman>advise failure <362> details;

rman>repair failure;

Show more