log shipping implementation Steps

       1).Create user Account with same name and password on both computers. Make sure File            Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.
2. Now create a folder named “bkp” on both servers.
3. On the both Servers share bkp folder with full accsess permission. Allow the “user”access permission on bkp on both the servers
      5).Now go to Control Panel->Administrative Tools->Services and find the SQL Server               Agent service. Go to its properties and set “user” as the account on the Logon tab. Restart the service. Do this on both servers.

                                             

6.On “sa” account turn off Password Expiration Policy. This prevents sa password from expiring automatically.
7.Take full backup as follow :
1)Right click on database which you want to backed up select “TASK” option and then
“BACKUP” option.
ii)Select the option and give the format.It will help you to backup other then
default path of  backup.

iii) add the path location where you want to take backup.

8. Take the backup of transaction log as follow.
I) Right click on database which you want to backed up select “TASK” option and then
“BACKUP” option.  Select the transaction log from backup type.
iii)Select the option and give the format.It will help you to backup other then
default path of  backup.

NOTE: when taking backup of transaction log manually then don't use trn  extension
use .tra .extenstion .it will effects the procedures created for monitoring.

User tra

iv) add the path location where you want to take backup.

9. Copy the backup into c:\bkp the location of the second server (Secondary Server)
10. then restore the database as follow :
I) Select database Right click select “RESTORE DATABASE” option.

ii) select thelocation where you have copied full backup on the secondary server of primary server

ii) select the option and select Select Restore “WITH STANDBY “ Option

11. Apply the transaction log in as given below
I) select the databse which you restored from full backup Right clik on it and select
“TASK” option and then “RESTORE” and “TRANSACTION LOG” after that.

i)select the location where you have copied transation log backup on the secondary server of primary server
iii) select the option and select Select Restore “WITH STANDBY “ Option
12. Now have to create  STANDBY DATABASE on secondary server

13. To start log sipping from primary server select the option as follow
i)Right click on database for  which you want to create STAND BY
ii)select “TASK “ and then select “ SHIP TRANSATION LOGS”

 iii)Select  this as a primary Database option

iv)Give the full path of the primary server from where logs  have been generating
     
   

v)You can change the job scheduler on click the “SCHEDULER “ tab
and set the retention parameters for backup piece have to delete on secondary server  .

connect to the secondary  server using ”CONNECT TAB”

select the e Destination of the Secondary Server where you want to copy log transaction backup

ii)Use Standby mode with Disconnect users in the database when restoring backup.

Job have been created  .
14. Every thing is fine if you will see One Job  LSBackup on Primary Server

15.And Two Jobs LSCopy , LSRestore on Secondary Server

16.Then start the jobs in the sequence LSbackup,LScopy,LSRestore (they are schedule other way)

17.You can see job history if any problem accrue

Block corruption

     1)     Create a  tablespace

SQL> create tablespace abc datafile 'C:\APP\USER\ORADATA\MYDATABASE\abc.dbf' size 200k;

2) Connect the user create a table in above tablespaces

SQL> conn scott/tiger

Connected.

SQL> create table abc (name varchar(10)) tablespace abc;

Table created.

SQL> insert into abc values('currpt blk');

1 row created.

SQL> commit;

commit complete.

3) Take tablespace offline

SQL> conn / as sysdba

Connected.

SQL> alter tablespace abc offline;

Tablespace altered.

4) Open the datafile belongs to above tablespace and edit the file change 'currpt blk' to block

5)Change status of tablespace  online

SQL> alter tablespace abc online;

Tablespace altered.

6) Now  access the data from the table

SQL> conn scott/tiger

Connected.

SQL> select * from abc;

select * from abc

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: 'C:\APP\USER\ORADATA\MYDATABASE\ABC.DBF'

7)  Run the DBV utility find the corrupted block

\BIN>dbv   file =C:\APP\USER\ORADATA\MYDATABASE\abc.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Aug 10 17:41:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = C:\APP\USER\ORADATA\MYDATABASE\ABC.DBF

Page 11 is marked corrupt

Corrupt block relative dba: 0x0140000b (file 5, block 11)

Bad check value found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x0140000b

 last change scn: 0x0000.001096fc seq: 0x1 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x96fc0601

 check value in block header: 0xe8f3

 computed block checksum: 0x1a00

DBVERIFY - Verification complete

Total Pages Examined         : 25

Total Pages Processed (Data) : 4

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 10

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1087227 (0.1087227)

8) Connect the Rman and recover the block using clockrecover command

C:\app\user\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 10 17:41:50 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MYDATABA (DBID=1269509988)

RMAN> blockrecover datafile 5 block 11;

Starting recover at 10-AUG-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=142 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: reading from backup piece C:\APP\USER\FLASH_RECOVERY_AREA\MYDATABASE\BACKUPSET\2

011_08_10\O1_MF_NNNDF_TAG20110810T173411_744WXXQG_.BKP

channel ORA_DISK_1: piece handle=C:\APP\USER\FLASH_RECOVERY_AREA\MYDATABASE\BACKUPSET\2011_08_10\O1_

MF_NNNDF_TAG20110810T173411_744WXXQG_.BKP tag=TAG20110810T173411

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 10-AUG-11

RMAN>

You can find the  corrupt block for particular  object as given below

select tablespace_id,HEADER_FILE,header_block,segment_type from  SYS_dba_SEGS where segment_na

me like 'ABC'

SQL> /

TABLESPACE_ID HEADER_FILE HEADER_BLOCK SEGMENT_TYPE

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

            6           5         1547 TABLE

C:\oracle\product\10.2.0\db_1\BIN>dbv userid=scott/tiger segment_id=6.5.1547

Under Standing The Oracle RowID

Every row in every table has a physical address. The address of a row is determined from a combination of the following:

·         Datafile number

·         Block number

·         Location of the row within the block

·         Object number

You can display the address of a row in a table by querying the ROWID pseudo-column—for example:

SQL> select rowid, emp_id from emp;

Here’s some sample output:

ROWID EMP_ID

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

AAAFWXAAFAAAAlWAAA 1

The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when

you query it. The ROWID contents are displayed as base-64 values that can contain the characters A–Z, a– z, 0–9, +, and /. You can translate the ROWID value into meaningful information via the DBMS_ROWID

package. For example,to display the file number, block number, and row number in which a row is stored,issue this statement:

select emp_id  ,

dbms_rowid.rowid_relative_fno(rowid) file_num ,

dbms_rowid.rowid_block_number(rowid) block_num

,dbms_rowid.rowid_row_number(rowid) row_num

from emp;

Here’s some sample output:

EMP_ID FILE_NUM            BLOCK_NUM ROW_NUM

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

2960         4                                144     126

2961       4                                144      127

You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the

ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in

the same cluster and so contain rows with the same ROWID.

Switchover and failover in oracle 11g Data - Guard

Switchover

Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.

Failover

Changes a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected. verify the  primary database can be switched to the standby role.

Preparing for a Role Transition

        1)        Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS GAP_STATUS

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

VALID NO GAP

       2)       Ensure temporary files exist on the standby database that match the temporary files on the primary database.

        3)       Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

        4)       Before performing a switchover to a physical standby database that is in real-time query mode, consider bringing all instances of that standby database to the mounted but not open state to achieve the fastest possible role transition and to cleanly terminate any user sessions connected to the physical standby database prior to the role transition

Switchover Steps

        1)       Check Primary and standby databases are ready to  switchover

On Primary database

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

 TO STANDBY

 1 row selected

·          A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.

On Secondary Database

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY

1 row selected

·          A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly.

         2)       Initiate the switchover on the primary database.

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

 
         3) Shut down and then mount the former primary database.

SHUTDOWN ABORT;

STARTUP MOUNT;

   4)  Switch the target physical standby database role to the primary role.

  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

   5)    Open the new primary database.

ALTER DATABASE OPEN;

  6)    Start Redo Apply on the new physical standby database.

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Steps for failover

        1)       If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.

ALTER SYSTEM FLUSH REDO TO target_db_name;

       
         2)    Verify that the standby database has the most recently archived redo log file for each primary database redo thread.

   SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

   THREAD       LAST

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

         1       3555

       3)       If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it. This must be done for each redo thread.

     ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filename ';

         
          4)       Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

THREAD#    LOW_SEQUENCE# HIGH_SEQUENCE#

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

         1            3555          3555

                                                  

         5)       Stop Redo Apply.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

           
         6)       Finish applying all received redo data.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

           
         7)       Verify that the target standby database is ready to become a primary database.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY

         8)       Switch the physical standby database to the primary role.

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

         
         9)       Open the new primary database.

ALTER DATABASE OPEN;