Difference Between RESETLOGS and NORESETLOGS

Difference between RESETLOGS and NORESETLOGS

Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

Use NORESETLOGS when doing complete recovery (when the entire redo stream was applied). Oracle will continue using the existing (valid) log files.

NORESETLOGS

The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

RESETLOGS

CAUTION: Never use RESETLOGS unless necessary.

Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

Before using the RESETLOGS option take an offline backup of the database.

The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up

Dataguard Protection Modes

Dataguard Protection Modes

Data Guard operates in three different protection modes:

Maximum Performance
===================
Default protection mode with the primary concern on the performance. In this mode, transaction commits as soon as redo data written to the local redo logs. It provides less data protection compared to other modes.

Maximum Availability
===================
Highest level of data protection while the primary database is available. Transaction commits, when redo written to the local redo logs and at least one standby location. In case of no standby location is available, the primary doesn’t shut down and continue to generate redo entries.

Maximum Protection
==================
No (Zero) data loss. Redo entries must be written to the local online redo logs and at least one standby location before a transaction commits on primary. Primary will shut down if it can’t write redo to the standby database.

Some terminology
Switch-over
Switch-over is planned role changes between primary and standby databases usually to allow maintenance activities to be performed on primary.

Fail-over
On the other hand, Fail-over occurs in the event of taking the primary database offline (can be because of unplanned failure). In this case one of the standbys becomes new primary and original primary may need to be rebuilt.

The main difference between switchover and failover is primary database is still available in the case of switchover.

Semaphores and Shared memory

Semaphores and Shared memory

Semaphores are a system resource that Oracle utilizes for inter-process communication (IPC) and they occupy a relatively small memory space, while shared memory is utilized to contain the SGA and can garner a large portion of physical memory.

Operating System kernel parameters change can take effect only after system is rebooted.

Both semaphore and shared memory errors appear primarily at instance startup (The 'startup nomount' stage specifically). This is the only time that Oracle tries to acquire semaphores and shared memory for the instance. Errors related to semaphores or shared memory rarely appears during normal database operations.

The most common circumstance in which these errors occur is during the creation of a new database.

Sometimes when an Oracle instance crashes, however, its shared memory segments may not be released by the OS. This limits the overall amount of shared memory available for the instance to start up again. In this case, you will need to remove those segments manually.


This can be avoided:

The first and most simple fix is to modify the init<sid>.ora to reduce the number of semaphores or the amount of shared memory Oracle will try to grab at instance startup.

If your situation requires that you not reduce the appropriate init<sid>.ora parameters, you will have to modify the operating system kernel to allow the OS to provide more
semaphores or allow larger shared memory segments.


With Oracle8, Oracle8i, Oracle9i and Oracle10g:

The number of semaphores required by an instance is equal to 2 times the setting of the 'processes' parameter in the init<sid>.ora for the instance.
Keep in mind, however, that Oracle only momentarily grabs 2 X 'processes' then releases half at instance startup.
This measure was apparently introduced to ensure Oracle could not exhaust a system of semaphores.

Oracle may also grab a couple of additional semaphores per instance for internal use.

On most Unix systems, current semaphore allocation can be displayed with the OS command 'ipcs -s'.
     % ipcs -s


An application requests a given amount of contiguous shared memory from the OS. The OS dictates how large of a shared memory segment it will allow with the kernel parameter SHMMAX(Shared Memory Maximum). If the amount of shared memory requested by the application is greater than SHMMAX, the OS may be granted the shared memory in multiple segments. Ideally, however, you want the amount requested by the application to be less than SHMMAX so that the application's request can be fulfilled with one shared memory segment
Since the SGA is comprised of shared memory, SHMMAX can potentially limit how large your SGA can be and/or prevent your instance from starting.

Remember that SHMMAX is a high water mark, meaning that the OS will attempt to allow up to that amount for an application.

How to clear a stuck session:

Identify the shared memory segment that is 'stuck' in memory.
Remove the 'stuck' shared memory segment using the OS command 'ipcrm'.
           Example:

             Find the shmid

               % $ORACLE_HOME/bin/sysresv

               IPC Resources for ORACLE_SID "PROD" :
               Shared Memory:
               ID              KEY
               12189717        0x7c4602e0
               Semaphores:
               ID              KEY
               41484299        0xdfc4f220
               Oracle Instance alive for sid "PROD"

             Linux:
               % ipcrm shm 12189717

             Other Unix:
               % ipcrm -m 12189717


Note: We can also use the ipcs command to obtain a list of the system current shared memory segments and semaphore sets, along with their identification numbers and owner. The ipcrm command can be used to clear the stuck session using the shmid.

RMAN Important Queries And Views

RMAN Important Queries And Views



To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the

recover catalog.  (Work in Mount stage)


select session_key,input_type,status,to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,to_char(end_time,'yyyy-mm-dd hh24:mi')  

end_time,output_bytes_display,time_taken_display from v$rman_backup_job_details order by session_key asc;



Query to see Backed Up Archive Logs in the database (Work in Mount stage):

select recid,set_stamp,sequence#,first_change#,next_change# from  v$backup_redolog;



This script will report on all backups – full, incremental and archivelog backups:


col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi')  

end_time,elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;


This script will give you RMAN backup status along with start and stop timing.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME

LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK




RMAN related views:

select table_name from dict where table_name like 'V$%RMAN%';





V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.

V$RMAN_BACKUP_SUBJOB_DETAILS è  merges similar operations within an RMAN session into a single row. For example, if there are four

BACKUP DATAFILE <n> commands, three RECOVERY COPY OF DATAFILE commands, and one BACKUP RECOVERY AREA command, this view will

contain three rows - one each for BACKUP, ROLLFORWARD, and COPY_DISK_TO_TAPE operation.

V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.
SQL> select * from V$RMAN_BACKUP_TYPE;

    WEIGHT INPUT_TYPE
---------- -------------
         1 BACKUPSET
         2 SPFILE
         3 CONTROLFILE
         4 ARCHIVELOG
         5 DATAFILE INCR
         6 DATAFILE FULL
         7 DB INCR
         8 RECVR AREA
         9 DB FULL


V$RMAN_COMPRESSION_ALGORITHM : provides descriptions of supported compression algorithms. It is used by the RMAN client.


V$RMAN_CONFIGURATION : Information about RMAN persistent configuration settings.


V$RMAN_ENCRYPTION_ALGORITHMS : displays supported encryption algorithms. It is used by the RMAN client to validate user-requested

algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is

AES128.

V$RMAN_OUTPUT : displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can

hold 32768 rows.

V$RMAN_STATUS : displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs

which are in progress are stored only in memory while the finished jobs are stored in the controlfile


V$ARCHIVED_LOG : show which archives have been created, backed up, and cleared in the database.
 

V$BACKUP_CORRUPTION : shows which blocks have been found to be corrupt during a backup of a backup set            


V$BACKUP_DATAFILE : useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also

help you find the number of corrupt blocks in the datafile


V$BACKUP_DEVICE : display information about supported backup devices. DISK is  not returned because it is always available

V$BACKUP_FILES : display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPY commands

V$BACKUP_PIECE : show backup pieces created for backup sets

V$BACKUP_REDOLOG : show archived logs stored in backup sets

V$BACKUP_SET : show backup sets that have been created

V$BACKUP_SPFILE : display information about server parameter files in backup sets

V$COPY_CORRUPTION : show which blocks have been found to be corrupt during an image copy

V$DATABASE_BLOCK_CORRUPTION : list database blocks marked as corrupt during the most recent RMAN backup

V$RMAN_CONFIGURATION : list information about RMAN persistent configuration settings

V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE : proxy settings for RMAN

V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO :  backup performance statistics

RMAN Basics

What is RMAN?

Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

What is the difference between using recovery catalog and control file?

When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.

In recovery catalog we can store scripts.

Recovery catalog is central and can have information of many databases.

Can we use same target database as catalog?

No, The recovery catalog should not reside in the target database (database should be backed up), because the database can’t be recovered in the mounted state.

How do you know that how much RMAN task has been completed?

By querying v$rman_status or v$session_longops

From where list & report commands will get input?

Both the commands command quering v$ and recovery catalog views. V$BACKUP_FILES or many of the recovery catalog views such asRC_DATAFILE_COPY or RC_ARCHIVED_LOG.

Command to delete archive logs older than 7days?

RMAN> delete archivelog all completed before sysdate-7;

How many times does oracle ask before dropping a catalog?

The default is two times one for the actual command, the other for confirmation.

How to view the current defaults for the database.

RMAN> show all;

What is the use of crosscheck command in RMAN?

Crosscheck will be useful to check whether the catalog information is intact with OS level information. This command only updates repository records with the status of the backups.

e.g. If user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not.



What are the differences between crosscheck and validate commands?

Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

Which one is good, differential (incremental) backup or cumulative (incremental) backup?

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

This is command for taking Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

What is the difference between backup set and backup piece?

Backup set is logical and backup piece is physical.

RMAN command to backup for creating standby database

RMAN> duplicate target database

You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?

Create data file and recover datafile.

SQL> alter database create datafile ‘/u01/app/oracle/oradata/xyz.dbf’ size 2G;

RMAN> recover datafile file_id;

What is obsolete backup & expired backup?

A status of “expired” means that the backup piece or backup set is not found in the backup destination.

A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

What is the difference between hot backup & RMAN backup?

For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.



How to put manual/user-managed backup in RMAN (recovery catalog)?

By using catalog command.

RMAN> CATALOG START WITH ‘/tmp/backup.ctl’;

What are the Architectural components of RMAN?

RMAN Executables
Sercer process
Channels
Target database
Recovery catalog database (optional)
Media management Layer (optional)
Backups, backup sets and backup pieces
What are channels?

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics:

Type of I/O device being read or written to, either a disk or an sbt_tape
Number of processes simultaneously accessing an I/O device
Maximize size of files created on I/O devices
Maximize rate at which database files are read
Maximize number of files open at a time
Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog schema must be stored in a database other than the target database.

What is a Backup set?

A logical grouping of backup files — the backup pieces — that are created when you issue an RMAN backup command. A backup set is RMAN’s name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

What are the benefits of using RMAN?

Incremental backups that only copy data blocks that have changed since the last backup.
Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
Detection of corrupt blocks during backups.
Parallelization of I/O operations.
Automatic logging of all backup and recovery operations.
Built-in reporting and listing commands.
What are the various reports available with RMAN

RMAN>list backup;

RMAN> list archive;

In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?

using RMAN BLOCK RECOVER command

How do you enable the autobackup for the controlfile using RMAN?

Issue command at RMAN prompt.

RMAN> configure controlfile autobackup on;

Also we can configure controlfile backup format.

RMAN> configure controlfile autobackup format for device type disk to

2> ‘$HOME/BACKUP/RMAN/ F.bkp’;

How do you identify what are the all the target databases that are being backed-up with RMAN database?

You don’t have any view to identify whether it is backed up or not. The only option is connect to the target database and give list backup this will give you the backup information with date and timing.

How do you identify the block corruption in RMAN database? How do you fix it?

Using v$block_corruption view you can find which blocks corrupted.

RMAN> block recover datafile <fileid> block <blockid>;

Using the above statement You recover the corrupted blocks. First check whether the block is corrupted or not by using this command

SQL>select file# block# from v$database_block_corruption;

file# block

2 507

the above block is corrupted…

conn to Rman

To recover the block use this command…

RMAN>blockrecover datafile 2 block 507;

the above command recover the block 507

Now just verify it…..

Rman>blockrecover corruption list;

How do you clone the database using RMAN software? Give brief steps? When do you use crosscheck command?

Check whether backup pieces proxy copies or disk copies still exist.

Two commands available in RMAN to clone database:

1) Duplicate

2) Restore.

List some of the RMAN catalog view names which contain the catalog information?

RC_DATABASE_INCARNATION RC_BACKUP_COPY_DETAILS

RC_BACKUP_CORRUPTION

RC_BACKUP-DATAFILE_SUMMARY

How do you install the RMAN recovery catalog?

Steps to be followed:

1) Create connection string at catalog database.

2) At catalog database create one new user or use existing user and give that user a recovery_catalog_owner privilege.

3) Login into RMAN with connection string

a) export ORACLE_SID

b) rman target catalog @connection string

4) rman> create catalog;

5) register database;

What is the difference between physical and logical backups?

In Oracle Logical Backup is “which is taken using either Traditional Export/Import or Latest Data Pump”. Where as Physical backup is known “when you take Physical O/s Database related Files as Backup”.

What is RAID? What is RAID0? What is RAID1? What is RAID 10?

RAID: It is a redundant array of independent disk

RAID0: Concatenation and stripping

RAID1: Mirroring

How to enable Fast Incremental Backup to backup only those data blocks that have changed?

SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

How do you set the flash recovery area?

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

What is auxiliary channel in RMAN? When do you need this?

An auxiliary channel is a link to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.

How do you use the V$RECOVERY_FILE_DEST view to display information regarding the flashrecovery area?

SQL> SELECT name, space_limit, space_used,space_reclaimable, number_of_filesFROM v$recovery_file_dest;

How can you display warning messages?

SQL> SELECT object_type, message_type,message_level, reason, suggested_actionFROM dba_outstanding_alerts;

How do you backup the entire database?

RMAN> BACKUP DATABASE;

How do you backup an individual tablespaces?

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP TABLESPACE system;

How do you backup datafiles and control files?

RMAN> BACKUP DATAFILE 3;

RMAN> BACKUP CURRENT CONTROLFILE;

Use a fast recovery without restoring all backups from their backup location to the location specified inthe controlfile.

RMAN> SWITCH DATABASE TO COPY;

RAC Administration

How to check the status of CRS on specific node?

[ora11g@racnode1 ~]$ crsctl check crs

Note: crsctl command will be present in GRID_INRA_HOME

How to check the status of services on all nodes:

[ora11g@racnode1 ~]$ srvctl status nodeapps

How to check the status of complete clusterware stack on all nodes:

[ora11g@racnode1 ~]$ crsctl status resource -t

How to check the status of clusterware servers:

[ora11g@racnode1 ~]$ crsctl status server -f

How to stop csr on all nodes of clusterware:

[root@racnode1 bin]# ./crsctl stop cluster -all

How to stop crs on specific node:

[root@racnode1 bin]# ./crsctl stop crs

How to start crs on specific node:

[root@racnode1 bin]# ./crsctl start crs

How to check the status of specific Instance across cluster nodes:

[ora11g@racnode1 ~]$ srvctl status instance -d racdb -i racdb1

How to check the status of all instances across the cluster nodes:

[ora11g@racnode1 ~]$ srvctl status instance -d racdb -i racdb1,racdb2,racdb3,racdb4

How to start specific Instance across cluster nodes:

[ora11g@racnode1 ~]$ srvctl start instance -d racdb -i racdb4

How to check the status of database across cluster nodes:

[ora11g@racnode1 ~]$ srvctl status database -d racdb

How to stop cluster database:

[ora11g@racnode1 ~]$ srvctl stop database -d racdb

-- Start/ Stop database / instance services status:
From oracle user
1) Verify instance status

$ srvctl status instance -d EHISKOL -i EHISKOL2
Instance EHISKOL2 is running on node ekrac2

2) To Stop instance

$ srvctl stop instance -d EHISKOL -i EHISKOL2

3) To start instance

$ srvctl start instance -d EHISKOL -i EHISKOL2

-- If below message found, then instance services might be runnng.

PRCC-1015 : EHISKOL was already running on ekrac2
PRCR-1004 : Resource ora.ehiskol.db is already running

4) To stop all instance services across the nodes:

$ srvctl stop database -d EHISKOL

-- Start/ Stop SCAN listeners in RAC databases:

1) Verify SCAN listener status:

$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node ekrac2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node ekrac1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node ekrac1

$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): ekrac2,ekrac1

Note: Assume 3 scan IP configured with DNS.

When single / default IP configured with SCAN listener, then status will be shown like below:

$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node host1

2) To stop the scan listener:

$ srvctl stop scan_listener

3) To start the scan listener

$ srvctl start scan_listener

1) Verify Cluster services / CRS status
   ( for single node)

# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

for all nodes)

# crsctl check cluster -all
**************************************************************
ekrac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

2) To stop Cluster services / CRS (single node)

./crsctl stop crs