Real life new oracle dba scenarios – Archive Log File

Scenario 1

We had a test database and it is on oracle 10g.  This database was not in archive log mode . I was asked to put the database in archive log mode. Below are the steps I did to put it in archive log mode.

**important thing is to always take a backup before you change the archivemode status of your database.

1)our database is on a unix server. First I created a folder on the unix file system where I wanted to store my archive log files.

My datafiles are on /u01/oracle/myapp/data1

So I created another folder called archive and  so the full path is  /u01/oracle/app/archive. I asked the unix team to give atleast 50 GB for the filesystem on which this folder exists. As this is a test system we don’t do lot of changes so 50 Gb is sufficient. We also have some production systems where we allocate 500 GB to the archive folder and every 4 hours we then move the files into tape using rman.ok for now we will concentrate on this test database. On the unix system you can type

df –h to find out the current size of the file system.

2)ok. In point 1 we created a folder and sufficient space was given by the unix team. now  I am logging in as sysdba and shutting down the database after speaking with my team that no one was using this test database.

     Sql> shutdown immediate;

3)Now I opened my initmyapp.ora file whch is in our $ORACLE_HOME/dbs  folder.

  Alternatively you can also log into the database and find out where your pfile is located using the       show parameter pfile             command.

I added below parameters in the initmyapp.ora

  Log_archive_dest=’ /u01/oracle/myapp/archive’

 Log_archive_format=’log%t_%s_%r.arc’

Saved the initmyapp.ora file.

4) Now I started the database but left it in the mount state.

   Startup mount pfile=initmyapp.ora

5)now I executed the below command to put the database in archivelog mode

   sql>alter database archivelog;

6)now I opened my database

   sql>alter database open;

7)then I created the spfile from the pfile

sql>create spfile from pfile;

8)then I shutdown and started the database

   sql> shutdown immediate

sql> startup

9)i expect that you know how to use spfile and pfile because sometimes if you have a spfile you can do these tasks differently. But anyway even if you have a spfile these steps will work.

Scenario 2



Now after some days we saw that the archive destination is getting full and there was a problem with our backups so we temporarily wanted ti disable the archivelog mode for our database.below were the steps that were performed.

1.shutdown the database
2comment out the log_archive_dest and log_archive_format parameters in your init file.
3. start the database in mount status using the command   Startup mount
4. Issue: "alter database noarchivelog”

sqlplus / as sysdba

shutdown immediate;

–modify ini file

startup mount pfile=initmyapp.ora

alter database noarchivelog

alter database open

create spfile from pfile;

shutdown immediate;

startup

Scenario 3

I was asked by my manager to find out the space usage of the archive logs from the last 7 days . I logged into the database using the TOAD tool and executed below statements.

–finding out  archived log space usage for 7 days .

select sum(blocks*block_size)/1024/1024 as archived_size_7_days_MB

 from v$archived_log where completion_time > sysdate -7;

select sum(blocks*block_size)/1024/1024 as archived_size_2_days_MB

 from v$archived_log where completion_time > sysdate -2;

Scenario 4

One day we saw that for our test database our archive log destination was 100% full and the database was not able to archive any more redo logs and so was giving errors like “archival internal error” .

So my manager asked me to delete the archive logs older than 4 days.

As this is a test system we don’t need these archive logs because we don’t want to recover completely in case of a database crash. so in this scenario it was ok for me to delete these archive log files.

But if this is a production system you should never never never delete archive log files unless you have a full backup taken and you got permission from your manager that deleting these archive log files are ok.

On a production system you would normally backup those archivelog files into tape using rman and then delete the files or you would temporarily move all those archive log files into another destination so the database can again work.

Ok , back to the task.

I am on a unix server .so I used below commands to find out the archive log file olderthan 4 days.

1)first I went into the archive log directory.i found out the archive destination from the parameter log_archive_dest.

2)once I went into the folder , I executed te below commands. You will see the comments about what the command is doing.

  # List all logs more than 4 days old:

$ find . \( -mtime +4 -a -name "*.arc" \) -ls | sort -k 9,10

  # DELETE all logs more than 4 days old:

$ find . \( -mtime +4 -a -name "*.arc" \) -exec rm {} \;

Scenario 5

We have a production database and we saw that  one fine morning the archive log destination was 100% full. My manager asked me to solve the error because the database was not working and continuosly throwing the

‘archival internal error’

as this is a production database I cant simply delete the archivelog files because in case of a database crash the database will not recover completely if it doesn’t find these archive log files.

Fortunately on the same server I have another bigger location where I have enough space so I am going to move the archive log files into that location.

This database is on a unix server .

These are the steps that I did to fix this issue

 move the arc files and make a symlink to the new location:

- shut down Oracle
- copy arc files from      /u01/oracle/myapp/archive to     /u02/oracle/myapp/archive   using below command

mv /u01/oracle/myapp/archive /u02/oracle/myapp/archive

- make a symlink in /u01/oracle/myapp/archive

ln -s /u02/oracle/myapp/archive /u01/oracle/myapp/archive

-         start up Oracle

so problem resolved.  So what I did is to move the archive log files into the other bigger location. Then I created a unix symbolic link in the original destination so from now onwards my archive log files will be created in the new bigger location.

In case you want to use same old location for your archive log files then just move the files to the new destination and don’t execute the 2nd symbolic link that I executed above.

Once I copied these archive log files into the new location I ran my rman script to  backup the archive logs into tape.