Using FTP Get in Shell Script in Solaris

Just sharing a small but useful shell script which gets a file from remote server.

#!/bin/sh
HOST=''
USER=''
PASSWD=''
FILE=''
ftp -n $HOST <
quote USER $USER
quote PASS $PASSWD
bin
get $FILE
quit
END_SCRIPT
exit 0

Script to Resize Oracle Data Files

Following is script from notes to generate the queries to resize the data files:

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum        
      from dba_extents        
      group by file_id) b,
      dba_extents c,
     (select value db_block_size        
      from v$parameter        
      where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Ref: OTN

Online Redo Logs and Archived Redo Logs in RAC Environment

In a typical RAC environment, if you query the v$ views for a instance, the views show the information from that instance. If you query gv$views, then it shows the information from all the RAC instances.

For example if you want to know information about sessions connected to your RAC database through a single RAC instance to which you are logged in right now, you would query v$session view. But if you want to know about all the sessions connected through all the instances to the cluster database then you would use gv$views.

But that is not true when it comes to the online redo logs and archived redo logs. Every RAC instance maintains its own thread of online redo logs and archived redo logs. When you query v$log or v$archived_log view from a single RAC instance, it shows the information from all RAC instances. If you query gv$log or gv$archived_log, it shows redundant information from all instances.

For example, see this:

Code snippet 1:


[fahd@fahdrac ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Oct 10 20:04:51 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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



SQL> select thread#,count(1)
  2  from V$ARCHIVED_LOG
  3  group by thread#;

   THREAD#   COUNT(1)
---------- ----------
         1       1151
         2       1198

Code snippet 2:

SQL> select thread#,count(1)
  2  from gV$ARCHIVED_LOG
  3  group by thread#;

   THREAD#   COUNT(1)
---------- ----------
         1       2302
         2       2396


In code snippet one, it displayed the correct information. It takes information from both threads of both instances.

In code snippet two, first it takes information from local instance (which in return also gets information from other instance), and then it queries the other thread, which returns again the same value, and gv$ aggregates the values and return the wrong result.

Same is the case with online redo logs:

SQL> select thread#,group# from v$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         2          3
         2          4


SQL> select thread#,group# from gv$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         2          3
         2          4
         1          1
         1          2
         2          3
         2          4

8 rows selected.


So in case of online redo logs and archived redo logs, always use the v$log, v$logfile, and v$archived_log views, instead of corresponding gv$ views.

Equivalent of Solaris Commands in Oracle Enterprise Linux: CommandComparo

Today's DBAs manager their databases in disparate operating systems. Linux and Solaris are one of the most common utilized platform for the Oracle databases and other products. Solaris based DBAs are getting lots and lots of demands of managing the databases and middleware and Fusion Apps on Linux or Oracle Linux systems.

The similarities of commands at both the platform add lots of confusion and the DBAs tend to waste lots of time in googling the syntax for Solaris which worked for them in Linux. E.g. whoami in Linux is 'id' on Solaris and there are hundreds of such small tidbits.

OTN comes to rescue here. Command Comparo Wiki is their answer to this common hassle. According to OTN this gives equivalent command syntax in Oracle Solaris 10 and Oracle Enterprise Linux 5 for common administrative tasks – especially tasks that have tricky syntax or that you frequently need to doublecheck. It acts as a quick reference for administrators who operate in these two OS environments.

What is exactly stored in an archived redo log ?

Archived redologs are one of the most important distinguishing feature of Oracle. They are the most basic component and an essential part of any production and self-respecting database.

They are used everywhere. From media recovery to log miner, and from standby database to golden gate.

What exactly do they contain?

They store, redo records, composed of change vectors describing changes made to database blocks. Each change vector describes a single transactional change to a database block. Each change vector is marked by a unique number called as System Change Number (SCN). Change vectors also are stored for changes to the rollback segment data blocks and the transaction table of the rollback segments.