Oracle : Restart Grid Control 11GR2

export ORACLE_SID=OEM #Where oracle home is /u01/app/oracle/Middleware/oms11g . oraenv emctl stop oms -all # The -all stops the web tier as well export ORACLE_SID=AGENT . oraenv emctl stop agent emctl start agent export ORACLE_SID=OEM . oraenv emctl start oms

Oracle : Deinstall 11.2 Single-node Cluster on ASM

* Remove oracle home for databases using deinstall usign downloaded deinstall  cd /u01/app/oracle/media/deinstall  ./deinstall -home /u01/app/oracle/product/11.2/db_1   * Stop and remove the asm service (?)   srvctl stop asm -force -n bangor   srvctl  remove asm  -f -n bangor   * Using local  deinstaller - run local deinstall   cd /u01/grid/product/grid   ./deinstall -local   # Answer various questions by accepting all defaults   # Prompted to run the folloiwng as root (all one line)   /u01/app/oracle/media/deinstall/./perl/bin/perl -I/u01/app/oracle/media/deinstall/./perl/lib \    -I/u01/app/oracle/media/deinstall/./crs/install \    /u01/app/oracle/media/deinstall/./crs/install/rootcrs.pl -force  -deconfig -paramfile \    "/local/home/grid/deinstall_noHomeName.rsp"   # Process continues and finally prompts to run followng as root   rm -rf /etc/oraInst.loc   rm -rf /opt/ORCLfmap* Remove oracle home for databases using deinstall usign downloaded deinstall  cd /u01/app/oracle/media/deinstall  ./deinstall -home /u01/app/oracle/product/11.2/db_1   * Stop and remove the asm service (?)   srvctl stop asm -force -n bangor   srvctl  remove asm  -f -n bangor

Oracle : Startup 9.2 dataguard

startup nomount alter database mount standby database; alter database recover managed standby database disconnect;

Oracle : Dropping queue tables

alter session set events '10851 trace name context forever, level 2'" , followed by a DROP TABLE tname

Oracle : Clone grid control agent

In Grid Control->Deployments->Install Agent->Clone Agent On source server in dir above the agent home  zip -r agent11g_201105v1.zip agent11g Copy to target server or the OEM server Take care to pick the correct OS in the drop down, linux != linux x64!

Linux : Faster file transfer via ssh and tar compression

time scp  ARCHIVE_01.dbf oracle@remote.server.com:/b04/volbackupnew/oradata/NEWDEV5/dataguard/test real    41m5.699s time tar cvfz - ARCHIVE_01.dbf | ssh oracle@remote.server.com "tar zxvf - --directory /b04/volbackupnew/oradata/NEWDEV5/dataguard/test " real    0m57.987s

Jira : Dragging issues direct from the (Oracle) db

col c1 format a900 set lines 2000 pages 0 head off long 2000 longchunksize 2000 wrap on trimspool on trim on with base_data as ( select i.id iid, -1 aid, to_char('######### '||i.pkey ||' - '||i.assignee ||' - '||i.summary || ' #########') c1 from jira_dbo.jiraissue i where 1=1 and i.assignee like 'data%' union all select i.id iid, 0 aid, to_char(substr(i.DESCRIPTION,0,1000)) c1 from jira_dbo.jiraissue i where 1=1 and i.assignee like 'data%' union all select i.id, a.id, to_char(substr(a.ACTIONBODY,0,1000)) c1 from  jira_dbo.JIRAISSUE i, jira_dbo.jiraaction a where 1=1 and a.issueid = i.id and i.assignee like 'data%' ) select c1 from base_data  order by iid,aid

Oracle : oraenv gives XPointer evaluation failed

In 11G+ this is due ORACLE_BASE not being set in the course of running oraenv What is happening is $ORACLE_HOME/bin/orabase is being run to update $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml and erroring as the particular key it is looking for is not in the file ( PROPERTY NAME="ORACLE_BASE" I guess) - however the error is ignored resulting in $ORACLE_BASE being unset Made a one line change to oraenv to check the return value and use the OLD_ORACLE_BASE - which will probably work most of the time ;) ORACLE_BASE=`$ORABASE_EXEC` [ "$?" -ne 0 ] && { echo "Warning - failure of $ORABASE_EXEC"; ORACLE_BASE=$OLD_ORACLE_BASE; }  # New line

Oracle : Auditing object access in 9i

Created new audit tablespace AUDIT CREATE TABLESPACE "AUDIT" DATAFILE '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_01.dbf' SIZE 4096M REUSE , '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_02.dbf' SIZE 4096M REUSE NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Moved audit table - nb. original audit table is left in place create table audx tablespace "AUDIT" storage (initial 50k next 50k<br />pctincrease 0) as select * from aud$ where 1 = 2<br />rename AUD$ to AUD$$;<br />rename audx to aud$;<br />create index i_aud2;<br />on aud$(sessionid, ses$tid)<br />tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)<br /> Turned on audit of all procedural code AUDIT EXECUTE PROCEDURE BY SESSION;<br />-- To remove<br />--NOAUDIT EXECUTE PROCEDURE;<br /> Turned on audit of all table access AUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br />-- To remove<br />--NOAUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br /> Review space usage carefully [ Show » ] Peter Lawes added a comment - 19/Aug/10 10:47 AM Created new audit tablespace AUDIT CREATE TABLESPACE "AUDIT" DATAFILE '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_01.dbf' SIZE 4096M REUSE , '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_02.dbf' SIZE 4096M REUSE NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Moved audit table - nb. original audit table is left in place create table audx tablespace "AUDIT" storage (initial 50k next 50k pctincrease 0) as select * from aud$ where 1 = 2 rename AUD$ to AUD$$; rename audx to aud$; create index i_aud2; on aud$(sessionid, ses$tid) tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0) Turned on audit of all procedural code AUDIT EXECUTE PROCEDURE BY SESSION; -- To remove --NOAUDIT EXECUTE PROCEDURE; Turned on audit of all table access AUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION; -- To remove --NOAUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION; Review space usage carefully [ Permlink | Edit | Delete | « Hide ] Peter Lawes added a comment - 19/Aug/10 11:40 AM Audit query with base_data as <br />(<br />select <br />nvl(rtrim(decode(substr(ses_actions, 1,1),'S','ALTER/')<br />||decode(substr(ses_actions, 2,1),'S','AUDIT/')<br />||decode(substr(ses_actions, 3,1),'S','COMMENT/')<br />||decode(substr(ses_actions, 4,1),'S','D/')<br />||decode(substr(ses_actions, 5,1),'S','GRANT/')<br />||decode(substr(ses_actions, 6,1),'S','INDEX/')<br />||decode(substr(ses_actions, 7,1),'S','I/')<br />||decode(substr(ses_actions, 8,1),'S','LOCK/')<br />||decode(substr(ses_actions, 9,1),'S','RENAME/')<br />||decode(substr(ses_actions,10,1),'S','S/')<br />||decode(substr(ses_actions,11,1),'S','U/')<br />||decode(substr(ses_actions,12,1),'S','REFERENCES/')<br />||decode(substr(ses_actions,13,1),'S','X/') <br />,'/'),'?') action<br />,a.*<br />from DBA_AUDIT_OBJECT a<br />)<br />select <br />username,os_username,timestamp,owner,obj_name,<br />b.* <br />from base_data b<br />where 1=1<br />and (owner not in ('SYS','ODL_DBADMIN_DBO','XDB')<br />and username not in ('SYS','DBSNMP')<br />and owner||'.'||obj_name not in ('SYSTEM.SQLPLUS_PRODUCT_PROFILE','SYSTEM.PRODUCT_PRIVS'))<br />or action like '%?%' <br />order by b.timestamp desc<br /> [ Show » ] Peter Lawes added a comment - 19/Aug/10 11:40 AM Audit query with base_data as ( select nvl(rtrim(decode(substr(ses_actions, 1,1),'S','ALTER/') ||decode(substr(ses_actions, 2,1),'S','AUDIT/') ||decode(substr(ses_actions, 3,1),'S','COMMENT/') ||decode(substr(ses_actions, 4,1),'S','D/') ||decode(substr(ses_actions, 5,1),'S','GRANT/') ||decode(substr(ses_actions, 6,1),'S','INDEX/') ||decode(substr(ses_actions, 7,1),'S','I/') ||decode(substr(ses_actions, 8,1),'S','LOCK/') ||decode(substr(ses_actions, 9,1),'S','RENAME/') ||decode(substr(ses_actions,10,1),'S','S/') ||decode(substr(ses_actions,11,1),'S','U/') ||decode(substr(ses_actions,12,1),'S','REFERENCES/') ||decode(substr(ses_actions,13,1),'S','X/') ,'/'),'?') action ,a.* from DBA_AUDIT_OBJECT a ) select username,os_username,timestamp,owner,obj_name, b.* from base_data b where 1=1 and (owner not in ('SYS','ODL_DBADMIN_DBO','XDB') and username not in ('SYS','DBSNMP') and owner||'.'||obj_name not in ('SYSTEM.SQLPLUS_PRODUCT_PROFILE','SYSTEM.PRODUCT_PRIVS')) or action like '%?%' order by b.timestamp desc

Oracle : Impdp - objects that can be included/excluded

-- for database level export/import:  SELECT named, object_path, comments    FROM database_export_objects   WHERE object_path NOT LIKE '%/%';  -- for table schema export/import:  SELECT named, object_path, comments    FROM schema_export_objects   WHERE object_path NOT LIKE '%/%';  -- for table level export/import:  SELECT named, object_path, comments    FROM table_export_objects   WHERE object_path NOT LIKE '%/%';

Oracle : Drop a temp tablespace temp file

alter database tempfile '/u03/volnewdev7data/oradata/NEWDEV3/temp_02.dbf' drop including datafiles;

Oracle : Password file

At command line orapwd file=./PWDFILE password=password entries=10 In sqlplus: select * from <a href="http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html#pwfile_users">v$pwfile_users</a>; USERNAME SYSDB SYSOP ------------------------------ ----- ----- <a href="http://www.adp-gmbh.ch/ora/misc/sys_system_internal.html">SYS</a> TRUE TRUE <a href="http://www.adp-gmbh.ch/ora/sql/grant.html">grant</a> <a href="http://www.adp-gmbh.ch/ora/admin/sysdba.html">SYSDBA</a> to USERNAME; <a href="http://www.adp-gmbh.ch/ora/sql/grant.html">grant</a> <a href="http://www.adp-gmbh.ch/ora/admin/sysdba.html">SYSOPER</a> to USERNAME;

Oracle : Remove 11.1 RAC cluster

Use cluster remove tool found : http://download.oracle.com/otndocs/products/clustering/deinstall/clusterdeconfig.zip This is a command line tool - unzip on node and de-install database home then crs home ./clusterdeconfig -home /u01/app/crs/11.1.0/crs -checkonly ./clusterdeconfig -home /u01/app/crs/11.1.0/crs  ./clusterdeconfig -home /u01/app/oracle/product/11.1.0/db_1 -checkonly  ./clusterdeconfig -home /u01/app/oracle/product/11.1.0/db_1 Need to run a 3 root scripts on each node

RMAN : Simple restore of archive log to default location

$ORACLE_HOME/bin/rman nocatalog connect target dba_cron/password run { ALLOCATE CHANNEL c1 DEVICE TYPE DISK; restore archivelog sequence 214629; restore archivelog sequence 214630; }

MS Sql : Ctas = SELECT INTO

In Oracle create table T1 as select * from T1 The equivalent of that in Sql Server is SELECT INTO select * into T2 from T1

Oracle Dataguard : dgmgrl - various commands

<b>Start dgmgrl </b> export ORACLE_SID=dbdg; . oraenv; dgmgrl sys/<password> <b>Check the configuration status<br /></b> SHOW CONFIGURATION; SHOW DATABASE VERBOSE 'North_Sales' SHOW DATABASE 'North_Sales'; SHOW DATABASE 'North_Sales' 'StatusReport'; SHOW DATABASE 'North_Sales' 'InconsistentProperties'; SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps'; Change properties EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc'; EDIT DATABASE 'DR_Sales' SET STATE='READ-ONLY'; <b>Create/enable configuration<br /></b> CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'North_Sales' CONNECT IDENTIFIER IS North_Sales.foo.com; ADD DATABASE 'DR_Sales' AS CONNECT IDENTIFIER IS DR_Sales.foo.com MAINTAINED AS PHYSICAL; SHOW CONFIGURATION; ENABLE CONFIGURATION; <b>Stop transmit of dbs from primary <br /></b> EDIT DATABASE North_Sales SET STATE=LOG-TRANSPORT-OFF <b>Temporarily stop broker managing and monitoring a standby database.<br /></b> DISABLE DATABASE 'DR_Sales'; Switchover SWITCHOVER TO DR_Sales; SHOW CONFIGURATION; <b>Manual Failover<br /></b> FAILOVER TO "DR_Sales"; SHOW CONFIGURATION;

Oracle Dataguard : Bring standby in line using rman

* ON STANDBY {noformat} sqlplus / as sysdba SQL>startup mount SQL>select current_scn from v$database; CURRENT_SCN -----------   151214061 {noformat} * ON LIVE {noformat} export ORACLE_SID=FOSDB . oraenv $ORACLE_HOME/bin/rman nocatalog RMAN>connect target DBA_CRON/DBA_CRON RMAN>backup incremental from scn 151214061 database format '/b01/volbackupfos/FOSDB/201002/ForStandby_%U' tag 'FORSTANDBY'; RMAN>backup current controlfile for standby format '/b01/volbackupfos/FOSDB/201002/ForStandby_ctl.bkp'; scp * oracle@slofosdb01:/u03/volfoslivedata01/oradata/201002 {noformat} OR - actually did a full backup and transfer {noformat} RMAN>backup full database format '/b01/volbackupfos/FOSDB/201002/ForStandby_%U' tag 'FORSTANDBY'; RMAN>backup current controlfile for standby format '/b01/volbackupfos/FOSDB/201002/ForStandby_ctl.bkp'; scp * oracle@slofosdb01:/u03/volfoslivedata01/oradata/201002 {noformat} {noformat} * Must stop dataguard or recovery complains cannot get unique lock on files sqlplus / as sysdba SQL> alter database recover managed standby database cancel; {noformat} * ON STANDBY {noformat} $ORACLE_HOME/bin/rman nocatalog RMAN> connect target DBA_CRON/DBA_CRON RMAN> shutdown immediate; RMAN> startup nomount; RMAN> restore standby controlfile from '/u03/volfoslivedata01/oradata/201002/ForStandby_ctl.bkp'; RMAN> shutdown immediate; RMAN> startup mount RMAN> -- Catalog the copied files RMAN> catalog start with '/u03/volfoslivedata01/oradata/201002/' RMAN> restore database; RMAN> recover database noredo; RMAN> exit {noformat} {noformat} sqlplus / as sysdba SQL>startup mount SQL> alter database recover managed standby database disconnect; {noformat} * Needed to recreate STANDBY LOGFILEs as named differently from source db {noformat} sqlplus / as sysdba SQL> alter database recover managed standby database cancel; Database altered. select 'alter database drop standby logfile group ' group# ';' from v$standby_log; --Run generated script --This plus others ALTER DATABASE ADD STANDBY LOGFILE '/u02/volfoslivelogs01/oradata/FOSDGFOSDB_1srl2.f' SIZE 100M REUSE; {noformat} sqlplus / as sysdba SQL>startup mount SQL> alter database recover managed standby database disconnect; {noformat}

Oracle Dataguard : Check status from SQL

--On secondary select current_scn from v$database --On primary select scn_to_timestamp(287481398) from v$database union all select scn_to_timestamp(current_scn) from v$database; If too far apart may need to force log switch on primary ALTER SYSTEM SWITCH LOGFILE; Also see select * from  V$DATAGUARD_STATUS;

Oracle : Rolling sequences forward

declare dummy number; begin     for sq in (     select sequence_owner||'.'||sequence_name nm     from dba_sequences     where sequence_owner        in ( 'CLIENTDBO','DEALERTOOLS_DBO','GAINDBO','IT_JOB_LOG','MXDBO','MYBROKERDBO',             'NEWTOPSDBO','ODFSDBO','ODL_DBADMIN_DBO','ODL_DBSERVICES_DBO','ODL_USERAPPS_DBO',             'PRODUCTDBO','RECONCILIATIONSDBO','SETTLEMENTSDBO','USER_ADMIN_SYS','WEB')             )         loop         dbms_output.put_line(sq.nm);         for i in 1..10000 loop            execute immediate 'select  '||sq.nm||'.nextval from dual' into dummy;         end loop;     end loop; end;

Oracle : SQL Developer Data Modeller

Pros Full oracle support Also SQL Server (2005?) and DB2 Java based so linux too Data model v.similar to Designer Reverse engineer and generate scripts Diff ? No db required - xml files - so portable Repository in the offing Cons Expensive - $3k list Unable to draw arbitrary lines for unsupported relationships No customisation Process flows - unable to do functional decomoposition (?)