Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Monday, 7 December 2009

Check last applied archive log

SELECT max(sequence#) from v$log_history;

Also, the following

archive log list;

Friday, 13 November 2009

Resize Online Redo Logs - Rough Notes

To resize redo log files, we have to drop and recreate them. The status of these files is critical.

Files with a status of:

  • CURRENT - cannot/must not be dropped. To change the status of a current file, we have to initiate a log switch (ALTER SYSTEM SWITCH LOGFILE;).
  • ACTIVE - Is likely to return an error; ORA-01624: log 1 needed for crash recovery of instance. This can be overcome by generating a checkpoint (ALTER SYSTEM CHECKPOINT GLOBAL;).
  • INACTIVE - May be dropped.
  • UNUSED - May be dropped.

    Once again, the status of these files can be changed by initiating a log switch (ALTER SYSTEM SWITCH LOGFILE;).

    The following SQLs assist with the task of:

    Checking pertinent redo log file details
    SELECT a.group#
         , a.member
         , b.bytes / 1024 / 1024 MB
         , b.status
      FROM v$logfile a
         , v$log b 
     WHERE a.group# = b.group#
    order by a.group#;
    Dropping redo log file groups

    ALTER DATABASE DROP LOGFILE GROUP n;
    Resizing redo log file

    ALTER DATABASE ADD LOGFILE GROUP n 
    ('/member_path/redo_logfile_name1',  
     '/member_path/redo_logfile_name2') SIZE nM REUSE;

    For a standby database the following additional steps are needed before dropping a online redo log group ...

    Stop Standby Recovery
    ALTER DATABASE RECOVER MANAGED STANDBY CANCEL;
    Set Standby File Management to Manual
    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
    Clear Logfile Group
    If the STATUS of the logfile group is CLEARING or CLEARING_CURRENT, you can set it to unused by running command ALTER DATABASE CLEAR LOGFILE GROUP n;
  • Tuesday, 3 November 2009

    Logon Audit Trigger

    This is my version of a logon audit trigger. It picks up every iteration of a logon, even to the point of when a session switches from ACTIVE/INACTIVE mode.

    The more conventional way of creating a logon audit trigger would be to simply capture environment variables using the SYS_CONTEXT function, utilising USERENV, environment variables, for example ..

    sys_context('USERENV','CURRENT_USER')
    sys_context('USERENV','CURRENT_USERID')
    sys_context('USERENV','HOST')
    sys_context('USERENV','OS_USER')

    Create a table to store the information provided by the trigger

    create table nazim_logon_audit 
    (  sid number
     , pid number
     , spid varchar2(12)
     , process varchar2(12)
     , status varchar2(8)
     , username varchar2(30)
     , osuser varchar2(30)
     , program varchar2(48)
     , client_info varchar2(64)
     , module varchar2(48)
     , logon_time varchar2(30)
     , last_call_et varchar2(30)
    );
    

    Create the trigger

    create or replace trigger
       nazim_logon_audit_trigger
    AFTER LOGON ON DATABASE
    BEGIN
    insert into nazim_logon_audit
    (select s.sid 
         , p.pid 
         , p.spid
         , s.process
         , s.status
         , s.username
         , s.osuser
         , s.program
         , s.client_info
         , s.module
         , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
         , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss') 
      from v$session s
         , v$process p
     where s.paddr = p.addr
       and s.username=user 
       and s.logon_time = sysdate
     );
    commit;
    END;
    /

    Monday, 2 November 2009

    Oracle Process Details with SQL Text

    A useful query to check database processes alongwith the executed SQL statement:

    select s.sid
         , p.pid
         , p.spid
         , s.process
         , s.status
         , s.username
         , s.osuser
         , s.program
         , a.name
         , s.client_info
         , s.module
         , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
         , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss')
         , sq.sql_text
      from v$session s
         , v$process p
         , audit_actions a
         , v$sql sq
     where s.paddr = p.addr
       and s.command = a.action
       and sq.address (+) = decode(s.sql_address,'00', s.prev_sql_addr, s.sql_address)
     order by s.status;
    Field Key
  • Oracle Session ID
  • Oracle Process ID
  • OS Process ID
  • OS Client Process ID
  • Status of Session
  • Oracle Username
  • OS Username
  • OS Program Name
  • Command in Progress
  • Client Info from DBMS_APPLICATION_INFO.SET_CLIENT_INFO proc
  • Module Info from DBMS_APPLICATION_INFO.SET_MODULE proc
  • Time of Logon
  • Last Call
  • SQL Text
  • Thursday, 1 October 2009

    Oracle - Media Recovery

    A gem of a tip from my friend Mike N. which I was unaware of. Had a database in noarchivelog mode. Copied it across to a new server. Due to a minor mishap encountered "ORA-01113: file needs media recovery" on the target database.

    To fix this, ran the command RECOVER DATABASE UNTIL CANCEL and when prompted to specify log, in the absence of any archivelogs, pointed it to the redo log files and was able to recover. Following this off with a ALTER DATABASE OPEN NORESETLOGS and saved myself the hassle of recopying all the datafiles again.

    Another tip along the same lines provided by my mate Ken W. Once again, performed a RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. Supplied the archivelogs I had available but attempting to do ALTER DATABASE OPEN RESETLOGS found it required another archivelog which I did not have. Therefore recreated the CONTROLFILE using syntax CREATE CONTROLFILE SET DATABASE "DBNAME" RESETLOGS FORCE LOGGING NOARCHIVELOG REUSE. Followed this up with a ALTER DATABASE OPEN RESETLOGS - problem solved.

    Friday, 25 September 2009

    Note to Self - Move Existing Database

    To move an Oracle database from one server to another ...

  • Shutdown source database.
  • Create directories on target (/oracle/SID/datafiles, /oracle/admin/SID/sysfiles, /oracle/env/dbs, /oracle/pass)
  • Copy across datafiles, tempfiles, controlfiles, pfile, spfile, password file redo logs and environment files.
  • Add an entry in /etc/oratab.
  • Modify the parameter db_domain (ALTER SYSTEM SET DB_DOMAIN=domain SCOPE=spfile)
  • Alter database rename global_name.
  • Check the parameter service_names.
  • Update oracle names.
  • Setup cron entries / scheduled tasks.
  • Monday, 31 August 2009

    Oracle 10g - Flashback (Schema)

    Technically there is no schema level flashback feature available. However, provided your recovery period does not include any data dictionary changes (DDL) then you could actually use datapump to flashback export the schema and import it back in.

    To achieve this you would use syntax like ...

    expdp username/password directory=data_pump_dir dumpfile=data_pump_dir:dumpfile.dmp logfile=data_pump_dir:logfile.log schemas=SCHEMA flashback_time=\"to_timestamp('timestamp', 'yyyy-mm-dd hh24:mi')\"

    The import process would use syntax like ...

    impdp username/password directory=data_pump_dir dumpfile=DUMPFILE.DMP remap_schema=SCHEMA:SCHEMA

    In this instance, I'm remapping the schema to another schema, so I can keep both in play for comparison purposes. You may choose to overwrite it instead.

    Thursday, 27 August 2009

    Oracle 10g - Flashback (Table)

    The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

    First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

    How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

    To execute a FLASHBACK TABLE command, you need the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege. In addition, you must have INSERT, DELETE and ALTER privileges on all the specified tables.

    Retrieving a Table with Flashback Query

    SQL> SELECT * FROM PAKISTAN_CRICKET;
    
    FIRST_NAME           LAST_NAME           
    -------------------- --------------------
    Khan                 Imran               
    Akram                Wasim               
    Younis               Waqar               
    Afridi               Shahid              
    Miandad              Javed               
    
    5 rows selected.
    
    Drop the table.
    
    SQL> DR0P TABLE PAKISTAN_CRICKET;
    
    Table dropped.
    
    The default action associated with the drop command in 10g is to place the object in a recycle bin.  You can view the contents in the recycle bin using ..
    
    SQL> SELECT * FROM RECYCLEBIN;
    
    You may choose to permanently delete the table by purging it from the recyclebin or you could recover it via the following flashback command .. 
    
    SQL> FLASHBACK TABLE PAKISTAN_CRICKET TO BEFORE DROP;
    
    Flashback successful.
    
    Other options available with the FLASHBACK TABLE command are illustrated below .. 
    
    
    
    SQL> SELECT * FROM PAKISTAN_CRICKET;
    
    FIRST_NAME           LAST_NAME           
    -------------------- --------------------
    Khan                 Imran               
    Akram                Wasim               
    Younis               Waqar               
    Afridi               Shahid              
    Miandad              Javed               
    
    5 rows selected.
    
    
    

    Oracle 10g - Flashback (Row)

    The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

    First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

    How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

    Retrieving a Row with Flashback Query

    SQL> SELECT * FROM AUSTRALIA_CRICKET;
    
    FIRST_NAME           LAST_NAME
    -------------------- --------------------
    Ricky                Ponting
    Michael              Clarke
    Stuart               Clark
    Brad                 Haddin
    Brett                Lee
    
    5 rows selected.
    
    Delete a row of data - make a note of the deletion time for the purpose of this exercise.
    
    SQL> DELETE FROM AUSTRALIA_CRICKET WHERE FIRST_NAME='Brett';
    
    1 row deleted.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM AUSTRALIA_CRICKET;
    
    FIRST_NAME           LAST_NAME           
    -------------------- --------------------
    Ricky                Ponting             
    Michael              Clarke              
    Stuart               Clark               
    Brad                 Haddin              
    
    4 rows selected.
    
    Now to use a flashback query to view the data as it was prior to the delete.
    
    SQL> SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI');
    
    
    FIRST_NAME           LAST_NAME           
    -------------------- --------------------
    Ricky                Ponting             
    Michael              Clarke              
    Stuart               Clark               
    Brad                 Haddin              
    Brett                Lee                 
    
    5 rows selected.
    
    You may restore the deleted row as follows
    
    SQL> INSERT INTO AUSTRALIA_CRICKET(SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI') WHERE FIRST_NAME='Brett');
    
    1 row inserted.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM AUSTRALIA_CRICKET;
    
    FIRST_NAME           LAST_NAME           
    -------------------- --------------------
    Ricky                Ponting             
    Michael              Clarke              
    Stuart               Clark               
    Brad                 Haddin              
    Brett                Lee                 
    
    5 rows selected.
    
    

    Thursday, 25 June 2009

    PeopleSoft - Install as a Service

    In PeopleTools 8.49 we can now install the Windows Application Servers and Process Schedulers as a Service.

    This can easily be setup using new a menu option on psadmin.exe 4) Service Setup.

    In my case, I only wanted to configure the Process Scheduler as a Service. This was easily achieved by Editing the Service Configuration File (psntsrv.cfg) and commenting out the line - Application Server Domains=.

    Once the service has been configured, use the 2) Install a Service menu option and you're ready to rock!

    Thursday, 18 June 2009

    Oracle/Unix - Pipe to Gzip

    As an example, I wish to spool/pipe out an oracle export file straight to gzip on unix.

  • Create a Pipe
    mkfifo ppipe
  • In the background send the pipe contents to gzip
    cat ppipe | gzip -c > filename.dmp.gz &
  • Run the export to the pipe
    exp username/password file=ppipe log=filename.log
  • Friday, 5 June 2009

    Data Pump - Dynamic List of Tables to Export

    In PeopleSoft, I needed to export out all configuration/system-owned tables as a backup each week. To achieve this, my first step was to create a view listing all these tables, by filtering out those which are not prefixed with "PS_"

    CREATE OR REPLACE VIEW EXPORT_WEEKLY (TABLE_NAME) AS
    SELECT table_name
      FROM all_tables
     WHERE owner = 'SYSADM' 
       AND NOT REGEXP_LIKE (table_name, '^PS(_)');
    

    I then used Oracle datapump to export the tables listed in this view ..

    expdp username/password parfile=parfilename
    
    parfile contents
    DIRECTORY=dpump_dir1
    DUMPFILE=dpump_dir1:dumpfile.dmp
    LOGFILE=dpump_dir1:logfile.log
    SCHEMAS=SCHEMANAME
    INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM EXPORT_WEEKLY)"
    CONTENT=DATA_ONLY
    

    Thursday, 4 June 2009

    PeopleSoft List Of System Tables

    To obtain a list of all "system owned" tables in PeopleSoft, i.e. those which are not prefixed by a PS_, run the following SQL:

    SELECT table_name
      FROM all_tables
     WHERE owner = 'SCHEMA_OWNER'
       AND NOT REGEXP_LIKE (table_name, '^PS(_)');
    

    Tuesday, 5 May 2009

    Temp Tablespace Usage

    A useful script to check on users/processes using up temp tablespace.
    SELECT distinct s.username
         , s.sid
         , s.serial#
         , s.osuser
         , u.tablespace
         , u.contents
         , u.segtype
         , u.extents
         , u.blocks
      FROM v$session s
         , v$sort_usage u
     WHERE s.saddr=u.session_addr
    order by s.username, s.osuser;
    

    Note: blocks * size of blocks = mbytes

    Create User Scripts

    There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.

    I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.

    select 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' from dba_users where username in (&USERS);

    select 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from dba_role_privs where grantee IN (&USERS);

    select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') from dba_tab_privs where grantee IN (&USERS);

    select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') from dba_ts_quotas where username in (&USERS);

    Monday, 4 May 2009

    DAD Config - Note To Self

    In Oracle 10, the modplsql Database Access Descriptors may be set in the following file:

    $ORACLE_HOME/Apache/modplsql/conf/dads.conf

    <Location /pls/dad name>
    SetHandler pls_handler
    Order deny,allow
    Allow from all
    AllowOverride None
    PlsqlDatabaseConnectString server:1521-port:dbname.server ServiceNameFormat
    PlsqlAuthenticationMode Basic
    PlsqlDefaultPage homepage
    </Location>

    Friday, 1 May 2009

    Report Manager - Note to Self

    Data source for Report Manager Administration page is PS_CDM_LIST_VW.

    Data source for Report Manager List Page is PSRF_PGLET_VW. This requires EIPs to be working.

    Related: PSRF_REPORT_FOLDERS channel/queue. PSRF_REPORT_xxxx messages.

    Tuesday, 7 April 2009

    PeopleSoft - Find Job Applicants

    I have little exposure to the PeopleSoft Human Resources module, so I cannot be certain that this SQL would work globally.

    Anyhow, to obtain information about job applicants, you could run SQL.

    select a.job_req_nbr
         , a.posting_title
         , c.first_name || ' ' || c.last_name   
         , a.position_nbr
      from ps_job_requisition a
         , ps_posn_appliedfor b
         , ps_names c
     where a.job_req_nbr = b.job_req_nbr
       and b.applid = c.emplid
       -- use one or more of the following conditions
       and posting_title like ?
       and c.last_name like ?
       and a.job_req_nbr= ?
       and a.position_nbr= ?
    

    Saturday, 21 February 2009

    Fish Pics

    I've yet to learn the art of aquarium photography, but here goes nothing ....

    Some of the fish I've got in together are technically incompatible and are a product of circumstance.

    Sunday, 8 February 2009

    The Ups and Downs of Fish Keeping

    I've kept tropical fish ever since I was a teenager. I wasn't very good at it until a friend gave me a colony of Convict cichlids back in 1992. He was using them as feeder fish for his Oscars. With a whole colony of hardy convicts to look after, I was able to hone my skills and within a short period of time they were breeding like rabbits. I ended up selling them a few years later when I moved to the US.

    Upon my return to Oz, I decided to try my hand again at keeping fish. I got myself a 4ft tank and started slowly. However, no matter how hard I tried, I just couldn't get water chemistry and balance of it all sorted. Fortunately a friend of mine with vastly more experience lent me a hand and got me over this hurdle. In 2 short years, I once again had a vibrant tank full of breeding pairs of bristlenose catfish and a range of colourful cichlids.

    Unfortunately, in 2005, during renovations to my home, we had a thunderstorm whilst a section of the roof was off. This caused water to seep into the wiring and tripped the circuit, cutting off the air supply to my fishtank. Overnight it wiped out my entire population of cichlids. The bristlenose survived as they probably were able to swim to the surface and take a breath.

    I decided to press on with the hobby and replenished with some maingano cichlids and also got myself a small ghost knife fish. Another 3 years passed and by this time the mainganos were breeding as much as the bristlenose were and my pride and joy, the ghost knife fish had grown immensely. I had the perfect setup when lightening struck again. This time an appliance malfunctioned during the night and tripped the circuits. By the morning all except the bristlenose and a handful of maingano fry (babies) died.

    At this point it would have been easy to give up. However, I decided to mull it over and whilst browsing at a local petshop, I came across 4 Tropheus Duboisis. These fish, from Lake Tanganyika, are quite unlike the Lake Malawi cichlids and South American Cichlids I have been accustomed to. They are supposedly quite difficult to look after; Finicky with diet (herbivores) and water conditions and tend to be quite expensive. On the spur of the moment I decided to ask the petshop owner if he would trade the duboisis for a bunch of my bristlenose catfish and later on I even managed to get the details of the breeder he bought them from, so I could acquire some more. They do better as a larger colony.

    So I'm once again dusting myself off and getting on the saddle. I'll still miss the fantastic ghost knife fish I lost this year, but these marvellous new fish take some of the sting away. Maybe this time I'll invest in some battery powered air pumps or something!