30. Live project day 1 Introdcution to Production Database, Archive Log Monitoring(Getting more space by deleting previous unedded archive log files or increasing the FRA size), File System Monitoring(deleting audit trail files, trace files, trm files), Session independent jobs(how to run) - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

Production database server:

In production database we login through SSH by **putty ** or other application software, in any organzation - we dont get VM access. In some cases the client will not give the oracle user password but will give another user to login then add them to sudoer list, using that user can go to oracle user (sudo su - oracle). "whoami" it shows oracle , after that we set the oraenv.

In production servers there is a thing called bash profile, basically its file containing in the home directry of the user which will set the oracle encironment which we do in VMs(from . oraenv) by itself. There are separate bash profile files(containing the paths and directories like using export dir), one file for each database, by running it by ` /filename we can set the environment for each of the database.

Archived Log Monitoring:

You can see which envo or which database is set

$> echo $ORACLE_SID

If a customer asks you that , database is not running , then check First 3 things:

1.in $ ps -ef | grep pmon

This will give the process monitor background processes which is currently running. This essestially shows that theb database is curently running.

2. Then check if the listsner is up and running or not.

ps -ef | grep tns

lsnrctl status listsner

3. Then check the alert log. If the error contains the word archiver, the understand the error comes in archive log.

Then check the status of the archive log space. If the space for fast recovery area got filled then it will not allow new transactions after the redo log files got filled.

Check the total space of the FRA

SQL> show parameter reco

Then check the total size of archive log from a view v$recovery_file_dest

SELECT name, space_limit/1024/1024/1024 AS total_gb, space_used/1024/1024/1024 AS used_gb, space_reclaimable/1024/1024/1024 AS reclaimable_gb, number_of_files (SPACE_USED/SPACE_LIMIT)*100 as percentage_used FROM v$recovery_file_dest;

In our database only 59% is used, if the percentage gets to 100% then the database will not allow to do any tranactiona further . For that there are 2 option.

1. Deleting some existing files:

Inn FRA there are many files like the backup files, archive log file, 2nd control file. We can delete the archive log files, in the company this can vary, some client can give permission to delete the archive log, some won't give the permission. Always ask this from serniors. We can delete the archive log after the last backup was taken. Go to rman

 delete archivelog until time 'sysdate-1';

delete archivelog until time 'sysdate-4/24';

See the space has increased.

Another thing, if we need more storage itself from the cloud we will also have the authority to get more space from the cloud, of course we need approvals from the management first.

File System Monitoring:

File system is another word for mount point which we see by df -h

If any of these becomes more than 80% we will get an email alert and its the dba's job to make it less.

**We can check which file is containing which space by du -sh ***

The app will have the highest lest go there.

Then go to oracle as its the highest. Now we can't delete anything from oradata, and the product which contains the database binary files and the aplication respectively. Lets go to admin.

Now in production database we can delete the audit trail files (a log which records everything that happens in the databse like this user executed this sql etc), every company has a retention policy that they will keep the audit trail files upto 1 months. We we can delete the previous audit trail files.

See there are huge number of files, of course we can; do this manually

find . -maxdepth 1 -type f -name "*.aud" | wc -l (command for knowing the count)

---

Now we can also delete another 2 things.

---

---

Check the locaction where the alert logs are present. Now we see many other files here too. There are called trace files (.trc) files and the (.trm) files are metedata about these trace files.

Now what is a trace file, we know that alert log legs every thing that happens in the database every errors, but they don;t contain the full picture. These full picture is saved in this trace files, these are highly specific and detailed files containing this error, there are multiple trace files for mutiple things, and trm are the metedata about this files. Now if any error occurs in the database and the DBA can't correct this then we have to go to the oracle company, and they will also first alert logs. If they can't correct the error using alert log then they will ask for this trace and trm files. It is mainly for them.

There is a tool called TFA (Trace file Analyser) which will automatically compress the required trace and trm files into one file for convinience for uploading, we just have to provide the timeframe.

Now companies also have retention policies on these trace and trm files that they will keep the files upto 14 days. We can delete the previous ones after their retention period has passes using the same command.

backup >>> run {

    allocate channel ch1 device type disk;

    allocate channel ch2 device type disk;

    allocate channel ch3 device type disk;

    allocate channel ch4 device type disk;

    crosscheck archivelog all;

    DELETE FORCE noprompt expired archivelog all;

    backup as compressed backupset current controlfile format '$BACKUP_DIR/full_backup_pvprddb_$date/%d_CTRL_%U';

    BACKUP as compressed backupset INCREMENTAL LEVEL 0 DATABASE format '$BACKUP_DIR/full_backup_pvprddb_$date/%d_DB_INC_%U';

    backup as compressed backupset archivelog all not backed up format '$BACKUP_DIR/full_backup_pvprddb_$date/%d_arch_%U';

 crosscheck archivelog all;

 DELETE FORCE noprompt expired archivelog all;

 crosscheck backup;

 delete noprompt obsolete;

 release channel ch1;

 release channel ch2;

 release channel ch3;

 release channel ch4;

 }