How to solve ORA-00354: corrupt redo log block header

How to solve ORA-00354: corrupt redo log block header 

To resolve ORA-00354 :

SQL>alter system checkpoint;
SQL>alter system switch logfile;
SQL>alter database backup controlfile to trace;
SQL>alter system drop logfile group n;

If the above command fail with some archiving issue then use the below mentioned command

SQL>alter database clear unarchived logfile group <n>;
SQL>alter database drop logfile group <n>;
SQL>alter database add logfile group n ('/dev/................') size nnnnM reuse;

To check the status
SQL>select * from v$logfile;

It should show unused as the status of the recently added log file
Now, switch log file 3-4 times and check the v$logfile again
SQL>alter system switch logfile;  
SQL>select * from v$logfile;

Privileges assigned to roles and available to the currently logged in user

Data Dictionary Views Describing Roles


Privileges assigned to roles and available to the currently logged in user

SQL> desc ROLE_SYS_PRIVS;


SQL> connect scott/tiger
Connected.

SQL> select * from ROLE_SYS_PRIVS;

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

9 rows selected.

Delete duplicate rows

DELETE FROM <table> A WHERE ROWID > (
SELECT min(rowid) FROM <table> B
WHERE A.key_column = B.key_column);

Oracle 11g Home Cloning

Oracle 11g Home Cloning

1- Shutdown the database and other services of the source database

2- Make a zip file of the source Oracle home (only home folder like DB_1, or DBHOME_1)

3- Copy it over to the target machine

4- Unzip the zipped file

5- Go to the $ORACLE_HOME/oui/bin directory

6- From the $ORACLE_HOME/oui/bin directory, run Oracle Universal Installer in clone mode for the unzipped Oracle home. Use the following syntax:

$ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_BASE="/app/administrator" ORACLE_HOME="/app/administrator/product/11.2.0/dbhome_2" ORACLE_HOME_NAME="dbhome_2"

It will clone the Oracle home, check Oracle Inventory to verify.

Archival Backup

Archival Backup

Archival backup is a new RMAN feature in Oracle 11g, which allows to create a backup that will not be affected by the normal retention policy defined for backups taken using RMAN.

-- This backup consists of every datafile, controlfile, server parameter file needed for restore and recovery in a single disk or tape
-- It also automatically generates archive log backup to ensure the consistency of backup
-- Automatic archive log backup ensures database can be restored and recoverd to a consistent state.
-- Overrides the existing backup retention policy.
-- These backups can be kept for upto a certain date or forever.
-- The existing full database backup can be modified to be made Archival backup by simple commands.
-- This can be implemented by using KEEP FOREVER caluse (requires a recovery catalog) or KEEP UNTIL TIME caluse of backup command.
-- It will not be listed as obsolete through RMAN reporting commands.
-- Purpose of these kinds of backup could be business policy or legal.
-- The backup should be in non_FRA area, Flash Recovery Area cannot be used to keep the backup because retention policy applies to FRA. It will give ORA-19811 error

Such backups once created may be exempted from the normal backup and recovery strategy.

These backups may be created on separate backup media other than the flash recovery area to be retained for a long time. These kinds of backups may require to be maintained for business or legal requirements. They will not be displayed when you view the obsolete backups based on your retention policy.

This backup is "all-inclusive" and every datafile of the database, control file, server parameter file needed for restore and recovery is backed up to a single disk or tape device. It also automatically generates an archive redo log backup to ensue that the database backup can be recovered to a consistent state. Typically these backups are done to tape.

An archival backup are created with the BACKUP…KEEP command. The KEEP option indicates that the backupset should be exempt from the retention policy either forever (KEEP FOREVER clause) or for a specified time period (KEEP UNTIL TIME). The KEEP FOREVER clause requires the use of a recovery catalog database.

The RMAN command for archival backup:

KEEP {FOREVER| UNTIL TIME 'SYSDATE +n'}
         RESTORE POINT restore_point_name

where the restore point is a user-defined name associated with an SCN of the database corresponding to the time of the creation of the restore point. The restore point would act as a label for the SCN to which the backup must be recovered to be made consistent. The restore point option is not allowed with the CHANGE…KEEP command described later.

Example:

RMAN> BACKUP TAG FORBKP
           DATABASE KEEP FOREVER;

RMAN>BACKUP DATABASE
          FORMAT '<path>/%U.bkp'
          KEEP UNTIL TIME 'sysdate + 365';

The CHANGE command and archival backups :

The CHANGE... NOKEEP may be used to remove the KEEP attributes of a backup, making it subject to the backup retention policy.

The CHANGE…KEEP command may be used to change a consistent backup into an archival backup.

Example:

RMAN> CHANGE BACKUP TAG 'fulldb_backup' KEEP FOREVER;

Archival Backups may be created using Enterprise Manager:

Database Control Home Page
-> Availability tab
-> Schedule Backup
-> Schedule Customized Backup (Wizard)
-> Setting Page
-> Override Current Settings Button
-> Policy
-> Over Retention Policy Section
-> Select to keep a backup for a specified number of days.

Server Control Utility (SRVCTL)

Server Control Utility (SRVCTL) 

Login as oracle user to Linux

oracle@rac1$

Start and Stop database:

srvctl stop database -d racdb
srvctl start database -d racdb

Start and Stop instance

srvctl stop instance -d racdb -i racdb2
srvctl start instance -d racdb1 -i racdb2

start and Stop ASM

srvctl stop asm -n rac2
srvctl start asm -n rac2

Start and Stop Listener

srvctl stop listener -n rac2
srvctl start listener -n rac2

Start and Stop nodeapps

srvctl stop nodeapps -n rac2
srvctl start nodeapps -n rac2

How to add service using srvctl

srvctl add service -s TEST1 -d racdb -r racdb1 -a racdb2 -r preferred -a available

srvctl start service -s TEST1 -d racdb

this step is to add TNS entry for the service created through srvctl

srvctl config database -d racdb -t

How to modiy the service

srvctl config service -s TEST1 -d racdb

Make racdb2 also as preferred for service TEST1

srvctl config service -s TEST1 -d racdb -i racdb2 -r
srvctl config service -s TEST1 -d racdb

$srvctl stop/start/disable service -s TEST1 -d racdb -i racdb1

Changing between manual and auto

srvctl config database -d racdb -a
srvctl modify database -d racdb -y MANUAL

Oracle 11g Total Recall Feature

Oracle 11g Total Recall Feature

The Flashback Data Archive (FDA) feature makes it possible to automatically and transparently track all the changes to any set of tables, and to easily query data in those tables as of any point in time over any interval within the specified period with minimal performance impact.

FDA can be used in

- data forensic: find and revert changes made by a disgruntled employee
- retention policy enforcement: automatically purge history older than  years.
- Historical reporting: analyze product changes over time
- Error recovery: restore records that were erroneously removed or updated
- Employee fraud detection: find assets tht were deleted but never sold
- Tamper-proof history: retain a SecureFile file even when the original is destroyed.

Benefits:

Flash Data Archive overcomes the limitations inheritent in current apporach as historical data management. It automatically tracks every change made to FDA enabled data stored in the database, amd maintains a secure, efficient and easily accessible archive of historical data. The historical data can be returned as long as needed, and is easily accessible with standard SQL statements. Flashback Data Archive can be enabled on both existing and new tables instantaneously and in a completely application transparent manner.

- Application transparency :

Enabling FDA on one or more tables can be done instanttaneously with no application changes.

- Seamless Access

Applications can seamlessly query the historical data as it existed at any point in time or during any times interval within the retention period.

- Security :
Access to internal historical tables is restricted to read only. No DML is allowed on historical data for any users including administrators.

- Minimal performance overhead:
FDA employes a lightweight mechanism to mark DML operations on FDA enabled tables. The actual history generation and archiving is done asynchronously with background processes.

- Storage mechanism

Both compression and partitioning in FDA are managed automatically and require no special administration

- Centralized management:

we can create group of tables and set a common retention policy for each group. when a new table is added to a FDA, it automatically inherits the retention parameter from archive. The Oracle database automatically purges aged out history data for all the tracked tables based on the specified retention policy.

Flashback Data Archive requirements :

1- FDA tablespaces must be managed with automatic segment space mamanegment (ASSM).
2- Automatic Undo Management must be enabled

Implementation:

The are 3 steps process to FDA
1- Either create a new tablespace or use an existing tablespace( ASSM)

2- create a flashback data archive:
CREATE FLASHBACK ARCHIVE fda1
TABLESPACE fdatbs
RETENTION 5 YEARS;

This will create a new FDA calls fda1 on tablespace tbs1 with a retention period of 5 years, with an unlimited quota on the tablespace.

--FLASHBACK_ARCHIVE ADMINISTRATOR system privilege is required for creating a new flashback data archive.

--  FDA dictionary views

DBA/USER_FLASHBACK_ARCHIVE - Displays information about flashback data archives

DBA/USER_FLASHBACK_ARCHIVE_TS - Displays tablespaces and the mapping to flashback data archive

3. Enable flashback data architect on the desired table

ALTER TABLE EMPLOYEE FLASHBACK ARCHIVE fda1;

The employee table is now enabled for flashback data archive, and its history data will be stored in the flashback data archive fda1. when the first DML operation occurs, the internal history table is created in tbs1 tablespace.

DBA/USER_FLASHBACK_TABLES - views displays information about tables that are enabled for flashback data archive.

Querying a FDA table:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
WHERE TIMESTAMP TP_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD:HH24:MI:SS')
WHERE EMPLOYEE_ID=110;

Version query:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
VERSION BETWEEN TIMESTAMP
TO_TIMESTAMP('2011-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
WHERE EMPLOYEE_ID=110;

Voting disk and OCR Management

Voting disk and OCR Management

The recommendations for the addition or removal of a voting disk is shutdown Oracle Clusterware first on all nodes and then utilize the commands below as root user were path is completely qualified path for the additional voting disk. In case of new voting disk over the network file system NFS, create an empty voting disk file location with the correct owner and permissions before execute the commands mentioned below. Three extra raw devices partitions /dev/raw/raw3, /dev/raw/raw4, /dev/raw/raw5 have been created to practice following exercises:

$ crsctl query css votedisk
on Node1
$ crsctl stop crs
on Node2
$crsctl stop crs

$crsctl add css votedisk /dev/raw/raw3 force

after successful addition of votedisk

Node1
$crsctl start crs

Node2
$crsctl start crs

$crsctl query css votedisk
[oracle@crs1 ~]$ crsctl query css votedisk
 0.     0    /dev/raw/raw2
 1.     0    /dev/raw/raw3

located 2 votedisk(s).

---deleting a votedisk
# crsctl delete css votedisk path

-- backup and recovery of votedisk
dd if=voting_disk_name of=backup_file_name

dd if=/dev/raw/raw2 of=/home/oracle/backup/votedisk1

recovering votedisk

dd if=backup_filename of=voting_disk_name

--administering Oracle Cluster registry

OCR file location is in following file

Solaris /var/opt/oracle.ocr.loc
Linux /etc/oracle/ocr.loc

If ocr files resides on a cluster file system file or if the ocr is on network file system, then create the target OCR file before adding OCR.

Use OCRCHECK utility to verify ocr integrity

It will display
- the version of the OCR's block format
- total space used and available
- OCRID
- and OCR locations that have been configured

A block by block checksum operation for all the blocks in all the configured OCRs is performed by OCRCHECK. It also returns an individual status for each file as well as a result OCR integrity check


[oracle@crs1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     524184
         Used space (kbytes)      :       4592
         Available space (kbytes) :     519592
         ID                       : 1059761621
         Device/File Name         : /dev/raw/raw1
                                    Device/File integrity check succeeded

                                    Device/File not configured

         Cluster registry integrity check succeeded


OCRCHECK also create a log file in CRS_HOME/log/hostname/client.
to change amoun of logging edit the CRS_HOME/srvm/admin/ocrlog.ini

-- Run the following command to add OCR mirror location using either destination_file or disk to designate the target location of the additional OCR

-- First make sure that proper backup of OCR is available --

#ocrconfig -showbackup

#ocrconfig -replace ocr destination_file or disk
#ocrconfig -replace ocrmirror destination_file or disk

First select the raw device

If your OCR configuration were altered while a particular node is stopped, you would be required to repair OCR configuration on that particular node, for instance, there may be need to repair the OCR on a node that was not up at the time of removing, replacing or adding an OCR.
For repairing an OCR configuration, run the following command on the node on which Oracle Clusterware daemon was stopped.

#ocrconfig -repaire ocrmirror device_name