Enable & Disable Archive log mode

Enable archivelog mode:

1. Verify the archive mode and the Archive destination:

a.       Verify that the database is in "No archive mode”

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     25

Current log sequence           27

SQL>

b.      By default, the archive destination will be flash recovery area.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u01/app/oracle/flash_recovery

                                                 _area

db_recovery_file_dest_size           big integer 5000M

SQL>

c.       If you wish to change the archive log location, then edit “LOG_ARCHIVE_DEST_n” parameter to the path needed.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;

System altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u02/app/oracle/oradata/orcl/arch

Oldest online log sequence     25

Current log sequence           27

SQL>

2. Shut down the database and bring it up to “mount” mode.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size            2634024120 bytes

Database Buffers         1627389952 bytes

Redo Buffers               12132352 bytes

Database mounted.

SQL>

3. Enable the archive log mode.

SQL> alter database archivelog;

Database altered.

SQL>

4. Open the database.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/app/oracle/oradata/orcl/arch

Oldest online log sequence     25

Next log sequence to archive   27

Current log sequence           27

SQL>

5. Test if the archive logs are generated in the designated location by switching log files.

SQL> alter system switch logfile;

System altered.

SQL>

Disable archivelog mode:

1. Shut down the database and bring it up to “mount” mode.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size            2634024120 bytes

Database Buffers         1627389952 bytes

Redo Buffers               12132352 bytes

Database mounted.

SQL>

2. Disable the archive log mode.

SQL> alter database noarchivelog;

Database altered.

SQL>

3. Open the database and confirm if the archive log is disabled.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u02/app/oracle/oradata/orcl/arch

Oldest online log sequence     30

Next log sequence to archive   31

Current log sequence           31

SQL>

RMAN-06207, RMAN-06208 & RMAN-06214

Error:

RMAN-06207: WARNING: 3 objects could not be deleted for DISK channel(s) due

RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status

RMAN-06210: List of Mismatched objects

RMAN-06211: ==========================

RMAN-06212:   Object Type   Filename/Handle

RMAN-06213: --------------- ---------------------------------------------------

RMAN-06214: Datafile Copy   /backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp

RMAN-06214: Datafile Copy   /backup/export/control_before.ctl

Cause:

These files will not be present at the OS level but the repository has the entry for it.

[oracle@mac01 /backup]$ ls -ltrh /backup/export/control_before.ctl

mv: cannot stat `/backup/export/control_before.ctl': No such file or directory

[oracle@mac01 /backup]$

[oracle@mac01 /backup]$ ls -ltrh /backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp

mv: cannot stat `/backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp': No such file or directory

[oracle@mac01 /backup]$

Solution:

Update the repository for those backupsets/files through “CROSSCHECK” command.

1. Check for the obsolete backupsets/files for the database.

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 14 days

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy     14     31-JAN-13          /backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp

Control File Copy     15     10-FEB-14          /backup/export/control_before.ctl

RMAN>

2. Execute “CROSSCHECK” command to update the RMAN repository. In my case, it is the Control file copies are obsolete.

RMAN> crosscheck controlfilecopy '/backup/export/control_before.ctl';

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=592 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1027 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1093 device type=DISK

validation failed for control file copy

control file copy file name=/backup/export/control_before.ctl RECID=15 STAMP=839184488

Crosschecked 1 objects

RMAN> crosscheck controlfilecopy '/backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp';

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=592 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1027 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1093 device type=DISK

validation failed for control file copy

control file copy file name=/backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp RECID=14 STAMP=806121752

Crosschecked 1 objects

RMAN>

You can use anyone among the below, based on the type of file reported as obsolete.

Backup piece     à CROSSCHECK BACKUPPIECE ‘<BACKUPPIECE_NAME>’;

Backupset          à CROSSCHECK BACKUPSET ‘<BACKUPSET_NAME>’;

Datafilecopy      à CROSSCHECK DATAFILECOPY ‘<DATAFILECOPY_NAME>’;

Backup               à CROSSCHECK BACKUP;          

3. Delete the obsolete backupset/files.

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 14 days

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy     14     31-JAN-13          /backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp

Control File Copy     15     10-FEB-14          /backup/export/control_before.ctl

Do you really want to delete the above objects (enter YES or NO)? YES

deleted control file copy

control file copy file name=/backup/export/RDA/rda/output/TMP_RDA/RDA_BR_T11238_01_control.tmp RECID=14 STAMP=806121752

deleted control file copy

control file copy file name=/backup/export/control_before.ctl RECID=15 STAMP=839184488

Deleted 2 objects

RMAN>

4. Verify that there are no obsolete backupset/files.

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 14 days

no obsolete backups found

RMAN>

Hope this helps!!!

Difference between SYSDBA & SYSOPER system privileges

Even though SYSDBA & SYSOPER are two different system privileges, they share some common features:

®     Both are administrative privileges used for performing high-level administrative tasks like:

-    Create database

-    Startup/shutdown database

-    Backup database

-    Enable archivelog

-    Create spfile

-    Includes the RESTRICTED SESSION privilege

®     Both the system privileges allow connections to the database even when the database is not open.

®     Control on both these system privileges are outside the database, so that an administrator granted with any one of these system privileges will be able to start the database.

®      When you log in to the database as SYS user, you must connect to the database either as SYSDBA or as SYSOPER.

Difference between SYSDBA & SYSOPER

Sl. No.	SYSDBA	SYSOPER
1	Default schema is SYS	Default schema is PUBLIC
2	Allows most operations, including the ability to view user data. It is the most powerful administrative privilege	Allows a user to perform basic operational tasks, but without the ability to look at user data
3	Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.	Only complete recovery using "ALTER DATABASE RECOVER;"
4	Can change character set	Changing character set is not applicable here
5	Can drop database	Dropping database is not allowed for SYSOPER

Most of the DBAs confuse SYSDBA and SYSOPER system privileges with DBA. DBA is a role in the database which includes all system privileges excluding SYSDBA and SYSOPER.

Physical Standby (Redo Apply) – A detailed process flow

The below process flow deals with the Physical Standby database configured with Asynchronous redo transfer which is mostly preferred.

Physical Standby - Asynchronous redo transfer architecture:

A detailed process flow:

On Primary database:

1.        Transaction entry to Primary database’s memory:

                                  i.      As soon as the transaction starts, exclusive locks are acquired on all the buffer cache that is required for the transaction.

2.       Transaction written as redo to redo buffer:

                                  i.      In the Program Global Area (PGA) of the processes, the redo blocks that describe the changes or the change vectors are generated and stored.

                                ii.      Space is then allocated in the redo log buffer after successfully acquiring the redo allocation latch.

                               iii.      The generated redo then gets copied from processes’ PGA to the allocated space in the redo log buffer.

3.       Flush redo log buffers to disk:

                                  i.      Then, Oracle foreground process (Server process) tells LGWR to flush the redo log buffers to disk.

                                ii.      Then, the LGWR flushes the redo buffer contents to the Online Redo Log file(s), and also acknowledges the session completion. Now, the transaction is persistent on the disk without a commit.

                               iii.      The ARCH process archives the Online Redo log files (ORL) into archive log files.

                              iv.      During the checkpoint, DBWRn writes the database buffers, that were previously changed, are written into the disk.

Note: LGWR must have already written the redo buffers in to disk before the DBWRn flushes the database buffers to the disks. The “write-ahead logging protocol” enforces this explicit sequence in the database.

4.      Redo transport to Standby site:

                                  i.      The LNS process then reads the recently flushed redo from the online redo log files and sends the redo data to the Standby database using the “redo transport destination” (LOG_ARCHIVE_DEST_n  parameter) that was defined in pfile or spfile of the Primary database while configuring the Data Guard setup.

                                ii.      In ASYNC transportation method, the LGWR does not wait for any acknowledgement from LNS for transmitting the redo data over the network and also for applying the changes successfully on the Standby database.

On Standby database:

5.       Writes redo stream to Standby Redo Log files:

                                  i.      RFS process receives the redo stream on the Standby site from the network socket and stores it in the network buffers, and then writes this redo buffers into the Standby redo log files (SRLs).

6.       Archiving Standby Redo log files:

                                  i.      The ARCH process then archives the Standby redo logs into archive log files while a log switch occurs on the primary database.

                                ii.      The generated archive logs are then registered with the Standby control file.

7.       Recovery process on Standby database’s memory:

                                  i.      Initiating the actual recovery process on the Standby database starts here. The managed recovery asynchronously reads the redo from the SRLs (in case of real-time apply) or archive log files.

                                ii.      The data blocks that requires redo apply are parsed out and stored into appropriate in-memory map segments.

8.      Further recovery process on memory:

                                  i.      MRP process then ships the recovery slaves using the Parallel Query (PQ) inter-process communication framework.

                                ii.      And then, Parallel Media Recovery (PMR) reads the required data blocks into the buffer cache. Then, the redo will be subsequently applied to the buffers of that buffer cache.

9.       Data flush to database files:

                                  i.      During the checkpoint phase, the recently modified buffers (by the parallel recovery slaves) that are in the buffer cache are flushed to the disk.

                                ii.      And, the data files’ headers are updated to record the checkpoint completion.

Physical Standby - Synchronous redo transfer architecture: