Listener log parsing for unique connections

A small one liner to check out the unique IP addresses (or hostnames) from the listener.log.

cat /opt/oracle/10R2/network/log/listener.log | awk -F"=" {'print $10'} | sed -e 's/......$//g' | sort -k 1,1 -u
cat /opt/oracle/10R2/network/log/listener.log | awk -F"=" {'print $12'} | sed -e 's/......$//g' | sort -k 1,1 -u

Parsing alert log (gegrep) - Solaris 10

You can easily parse the Oracle alert log with gegrep to find out the times as well as the parsed line.

# Show two lines before the matching string

gegrep -B 2 ORA- alert_DB1.log

# Show how many times the database has been started up and when

gegrep -B 1 "Completed: ALTER DATABASE OPEN" alert_DB1.log

Restore database to a different path - set newname for datafile script (UNIX/Linux)

This script helps you to set the new name for datafiles during restoration to a different path. Script will ask two inputs: new path and restoration SCN.


set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  newScn number(15);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the set newname path (omit trailing ''/''):');
  newPath := '&PATH';
--  dbms_output.put_line('Enter the SCN:');
  newScn := &SCN;
  dbms_output.put_line('run');
  dbms_output.put_line('{');
  dbms_output.put_line('set until scn = '||newScn||';');

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('set newname for datafile '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  dbms_output.put_line('restore database;');
  dbms_output.put_line('switch all;');
  dbms_output.put_line('}');

  close df;
  close df2;
end;
/

Example output:

SQL>@plsql_set_newname_for_datafile.sql
Enter value for path: /path/to/the/new/localtion
old  27:   newPath := '&PATH';
new  27:   newPath := '/path/to/the/new/localtion';
Enter value for scn: 1234123
old  29:   newScn := &SCN;
new  29:   newScn := 1234123;
run
{
set until scn = 1234123;
set newname for datafile '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
set newname for datafile '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
set newname for datafile '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
set newname for datafile '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
set newname for datafile '/current/datafile/path/temp01.dbf' to '/path/to/the/new/localtion/temp01.dbf';
set newname for datafile '/current/datafile/path/redo01_1.rdo' to '/path/to/the/new/localtion/redo01_1.rdo';
set newname for datafile '/current/datafile/path/redo02_1.rdo' to '/path/to/the/new/localtion/redo02_1.rdo';
set newname for datafile '/current/datafile/path/redo03_1.rdo' to '/path/to/the/new/localtion/redo03_1.rdo';
restore database;
switch all;
}

PL/SQL procedure successfully completed.

How to easily rename all Oracle database datafiles (UNIX/Linux)

The following script helps to rename the Oracle database datafiles to a different location. It will generate executable output as 'alter database rename file..'. You just need to specify the path (without the trailing '/'), where you want to rename the datafiles.

set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the renamed database path (omit trailing ''/''):');
  newPath := '&PATH';

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('alter database rename file '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  close df;
  close df2;
end;
/

You get for example the following output.

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 9 14:19:51 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@plsql_alter_database_rename_file.sql
Enter value for path: /path/to/the/new/localtion
old  26:   newPath := '&PATH';
new  26:   newPath := '/path/to/the/new/localtion';
alter database rename file '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
alter database rename file '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
alter database rename file '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
alter database rename file '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
alter database rename file '/current/datafile/path/temp_small01.dbf' to '/path/to/the/new/localtion/temp_small01.dbf';
alter database rename file '/current/datafile/path/redo14_2.rdo' to '/path/to/the/new/localtion/redo14_2.rdo';
alter database rename file '/current/datafile/path/redo15_2.rdo' to '/path/to/the/new/localtion/redo15_2.rdo';
alter database rename file '/current/datafile/path/redo16_2.rdo' to '/path/to/the/new/localtion/redo16_2.rdo';
alter database rename file '/current/datafile/path/redo13_1.rdo' to '/path/to/the/new/localtion/redo13_1.rdo';
alter database rename file '/current/datafile/path/redo14_1.rdo' to '/path/to/the/new/localtion/redo14_1.rdo';
alter database rename file '/current/datafile/path/redo15_1.rdo' to '/path/to/the/new/localtion/redo15_1.rdo';
alter database rename file '/current/datafile/path/redo16_1.rdo' to '/path/to/the/new/localtion/redo16_1.rdo';

PL/SQL procedure successfully completed.