Posts Tagged by Oracle

Oracle RMAN backups

29-Jun-2009 Posted by Sonia Hamilton under
No Comments

More notes on Oracle RMAN backups, see also Oracle – how to purge old RMAN backups. Thanks Raoul for your email…

Are the backups being deleted using RMAN or simply 'rm'?

Oracle's RMAN can be set up to manage this for you based on RETENTION POLICY.  Eg, set retention policy to recovery window of 14 days and
it will regard any backups older than 14 days to be OBSOLETE. Obsolete backups can be removed regularly using this:
 DELETE NOPROMPT OBSOLETE;
in a script that RMAN runs, for example, the daily backup script.

Here's a quick rundown of the rman commands you might find useful:

-- list all configuration parameters
SHOW ALL;

-- check for spurious files in FRA (backup location)
-- typically, these can be safely deleted
-- but check messages/output first.
CATALOG RECOVERY AREA NOPROMPT;

-- check to see what backups are available in the backup
-- destination area (refresh with what's available)
-- missing ones are marked as EXPIRED
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;

-- delete all obsolete backups
-- this physically removes the backup pieces from the backup
-- destination and removes knowledge of them from the control
-- file this is performed based on the RETENTION policy
DELETE NOPROMPT OBSOLETE;

-- delete all expired backups, etc
-- this is clearing the knowledge of such EXPIRED (missing)
-- backups from the control file
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;

Once you've set the retention policy, then the following backup script (or one like it) will remove old backups automatically.

-- full back, deleting archive logs after backing them up
backup device type disk tag '%TAG' database;
backup device type disk tag '%TAG' archivelog all not backed up delete all input;
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

Example of what to do if system runs out of disk space due to archive logs:

crosscheck archivelog all;
delete expired archivelog all;
backup archivelog all delete input;
backup database;

Oracle and shmmax and shmall

16-Feb-2009 Posted by Sonia Hamilton under
1 Comment

A good article on Oracle and shmmax and shmall. In a nutshell:

  • shmall it is not the maximum amount of memory which can be allocated, it is the maximum size of any shared memory chunk – how big a bite you want per bite from free memory.
  • shmmax – its value determines the maximum amount of memory that ALL shared memory can take. the actual setting is derived… the maximum amount of memory = shmall * pagesize where pagesize = getconf PAGE_SIZE and shmall = cat /proc/sys/kernel/shmall

Shutdown/Startup Oracle

12-Jan-2009 Posted by Sonia Hamilton under
No Comments

Some notes on shutting down and starting up Oracle, taken from an email.

Please be corrected in Note Section if you do need to do abort shutdown.
After abort shutdown, we do need to startup and shutdown database again.
Then second shutdown will bring database down cleanly.
After that you can backup database files.
Logon UNIX as “oracle”
================
TO STOP DATABASE
================
1. Invoke SQL*Plus
$ sqlplus ” / as sysdba”
SQL > shutdown immediate;
…
…
Shutdown messages
…
Database closed.
SQL > exit;
Note : If there are connections to DB, Oracle may take some time to rollback each connection‘s job and then close the session. Once all connections are disconnected, it will bring database down.
During shutting down, Oracle will write message to in log file.
Please tail the file to see progression messages.
tail -f /opt/oracle/db_1020/admin/psms01/bdump/alert_psms01.log
If database is not down at reasonable time, you can terminate command by Ctrl+C, then
SQL> exit;
$ sqlplus ” / as sysdba”
SQL > shutdown abort;
SQL > exit;
$ sqlplus ” / as sysdba”
SQL > shutdown immediate;
SQL > exit;
The above steps should be done as the last choice and only when
database is not down as normal.
2. Shutdown Database Listener
$ lsnrctl stop
3. Verify Oracle Processes
$ ps -ef |grep pmon
$ ps -ef |grep tnslsnr
No such process shown if step 1 & 2 are successfully done.
=================
TO START DATABASE
=================
1. Start Database Listener
$ lsnrctl start
2. Invoke SQL*Plus
$ sqlplus ” / as sysdba”
SQL > startup;
…
…
Starting up messages
…
Database open.
SQL > exit;
3. Verify Oracle Processes
$ ps -ef |grep pmon
$ ps -ef |grep tnslsnr
One process is expected for each ‘ps’ command.
4. Test Connnection
$ sqlplus ” / as sysdba”
SQL > select instance_name, host_name,
to_char(startup_time, ‘DD-MON-YY HH24:MI’), status
from v$instance;
Status must be OPEN.

Oracle – how to purge old RMAN backups

18-Dec-2008 Posted by Sonia Hamilton under
7 Comments

Oracle runs on Linux, therefore I must be an Oracle expert (so goes management thinking). Here’s how to correctly purge old RMAN backups when storage runs out of space (thanks Nakrob):

Do not use rm to remove files. You must do it via RMAN.

$ export NLS_DATE_FORMAT='DD-MM-YY HH24:MI'

$ rman target / NOCATALOG

RMAN > crosscheck backupset;
This command will verify whether backup file is still on media.
If it is unavailable, RMAN will mark it as UNAVAILABLE or EXPIRED.

RAMN > delete expired backupset;
or
RMAN > delete expired backup;

Note : If you manually rename or zip RMAN backup files, you must manually remove it from disk since RMAN
does not recognize them.

RMAN > report obsolete;
The command lists all backups rendered obsolete based on rentention policy.
Current Retention Policy is 'Recovery WINDOW OF 30 DAYS'.

RMAN > delete obsolete;

RMAN > list backup summary;
It will show all backupset info kept in RMAN repository.
If you want to see what RMAN keeps in each backupset, run 'list backupset N' where N is Backupset ID.

RMAN > delete backupset N;
or
RMAN > delete backupset; (to delete all backups)

Once fair amount of space is reclaimed, do full backup.
Not sure how much full back space is needed but not less than 20G.

RMAN > backup database;

RMAN > list backup;
Full backup may have more than one backupset.
Look for last backupsets. It will list backup db files.

RMAN > delete obsolete;
Rerun this command to delete unwanted existing backupsets (if have) after completed full backup.