Virtualbox Networking (1) - NAT

I had a bunch of problems trying to understand the networking options on VIRTUALBOX. There is lots written about it, but very little to say... do this...

So when you what to get on the network to use Firefox....

1. Create a NAT Network. Before starting the VM, goto Settings, Network, then enable one of the Adaptors and attached to NAT.
2. Start the VM
3. Log in as root
4. goto System > Administration > Network
5. Create or edit one of the Devices eth0, eth1 etc. Ethernet, the eth0 etc should match the adaptor you chose in step 1
6. Choose DCHP. This is the key. Don't try to manually set any of the ip values.

This should be all you need to piggy back your PCs internet.

Happyjohn

Oracle Data Guard Troubleshooting

Just a couple of SQL scripts that help with Data Guard problems.

This first is for the Primary and shows details of the current logfiles.

SELECT
 (SELECT name FROM V$DATABASE
 ) name,
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 ) max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'
 ), 0)
 FROM DUAL
 ) "To be applied",
 (
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) -
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
 )) "To be Shipped"
FROM DUAL;

Next is for the Standby database and shows apply progress.

SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log )
/

And lastly one to tell you if there are errors. I used rman duplicate to create a standby which of course didn't work. When I ran this
I immediately saw that I had an error on the LOG_ARCHIVE_DEST_2 parameter. Typo of course.

col archive_dest for a30
set lines 132
SELECT DEST_ID "ID",
STATUS "DB_status",
DESTINATION "Archive_dest",
ERROR "Error"
FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

This is a good guide too.

http://docs.oracle.com/cd/B19306_01/server.102/b14239/troubleshooting.htm#i634242

Happyjohn.

RMAN - Recovering a data file loss

If you lose a data file, it will be reported. To recover it try these steps.

rman target / catalog rman/rman@rmandb
list failure;
(this displays details of the problem unless you've just deleted the file yourself when it might not yet have been reported)

advise failure;
(this is really useful because not only does it give you possible strategies for recovery, but it creates the script to)

In the test I did, I removed a datafile manually (users01.dbf). Then I tried to create a table in that tablespace which resulted in an error.
Then turning to RMAN, list failure showed 'one or more datafiles missing'. Advise failure told me to restore and recover datafile 6.

So I did this in rman

sql 'alter database datafile 6 offline';
recover datafile 6;
restore datafile 6;
sql 'alter database datafile 6 online';

But that's exactly what the 'hm' script, produced by advise failure, did. So just run it.


Happyjohn

Creating an RMAN Catalog

This is just a note to remind me of the steps involved.

1. You need 2 databases. You've got your main database, and you've got a small database to act as the catalog. Once the catalog database is created, don't forget to run catalog and catproc, then create a user called rman and ....

grant recovery_catalog_owner to rman;

2. Make sure that the catalog DB is available from the main database server where you're going to run ran from.

3. rman
connect catalog rman/rman/rmandb
create catalog;

4. connect target a/b@c
register database;

5. connect target / catalog rman/rman@rmandb
backup database plus archivelog;


To backup the catalog database....

Rman target /
shutdown immediate
startup mount
backup full database;
alter database open;


Happyjohn

Difference Between EXPIRED and OBSOLETE backups in Oracle

Nice and simple but so much harder to remember.

EXPIRED is where Oracle think is has a backup but the file is no longer available, ie deleted.

OBSOLETE is where backups exist but are no longer required for a complete recovery. If you're using a FRA then the space occupied by the obsolete backups is available to be overwritten. OBSOLETE backups depend upon several things in the CONFIGURE settings such as the retention policy.

CROSSCHECK is used to identify EXPIRED backups and they can be deleted using the DELETE EXPIRED command.

Happyjohn

UNIX - More of searching for files

From the previous post....

In order to locate large directories, I extended the idea of the find statement and an executable script (ascr2)

In this example, I use the du -sm command to locate directories exceeding a certain size in MB.

File - ascr2 Parameter 1 in the directory name, parameter 2 is the minimum size we want to display.

if [ `du -sm $1 | cut -f1` -gt $2 ]; then
echo "$1 - `du -sm $1 | cut -f1`"
fi

The File Command. This will only display directories over 100Mb. Obviously the parent directories will be larger.

find /app/oracle -type d -exec ./ascr2 {} 100 \;


Happyjohn

UNIX - finding directories with more than x number of files

I had to scratch my head a little when I was supporting Oracle in a Unix/Linux/AIX environment. We would keep getting tickets for partition /app/oracle exceeds 90%.

So I'd log into the server and do a df -h /app/oracle or df -g /app/oracle (in AIX) and sure enough it exceed 90% full.
But which files should I delete or gzip?

So I'd do this...

find /app/oracle -type f -size +100000 -exec ls -l {} \;

to locate large files, and this to locate dump files.

find /app/oracle -name '*.dmp' -exec ls -l {} \;

but the biggest problem is with large numbers of small log files clogging up directories. Icouldn't find a suitable unix command so I wrote one.

File - ascr1 (this will display the count of files in directory $1 but only if it exceed the number in $2). Give it execute privs.

if [ `ls $1 | wc -l` -gt $2 ]; then
echo "$1 - `ls $1 | wc -l`"
fi

then use it in a find command. In this example it will only display directories exceeding 1000 files.

find /app/oracle -type d -exec ./ascr1 {} 1000 \;



Happyjohn.