Configure Standby in case you re-create redologs of Primary site

If you resize the primary Redolog files, and you have a Standby configured. Then you need to take care of your Standby database also.

So, the standby logs also need to be resized to match the online redo logs in the Primary database.

Follow the below steps (after you resize your primary RedoLogs)

STEPS TO ACHIEVE THIS

1. Drop and recreate online redologs on the PRIMARY site.

2. On primary site, create the standby controlfile –

SQL> alter database create standby controlfile as ‘/oradata/prim/standby01.ctl’;

3. Copy the standby control file to standby site using the scp command

4. Cancel the recovery(MRP)  &   shutdown immediate the standby database

5. Startup and mount the standby database -using the newly standby controlfile

5. Defer recovery until standby logs are created..… but do not start managed recovery right now.

6. Add the standby logs with same size as Primary online redologs.

7. The online logs on the standby side will be created at switchover time so no need to do anything now.

8. Start recovery on standby site.. (MRP)

How to Import data via a network link in Oracle

Today , I will tell you the process to Import your data via a network link between 2 Database, located remotely

In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.

The first step is to create a “database link object” to identify the source database and then provide the login credentials.

For example, a source database located in INDIA might be identified by the Oracle network service name ‘GOA’.

A user in that instance, ‘ADMIN1’ logs in using the password ‘ADMIN1’ and has the correct privileges to access the data to be imported.

Create a Database link using CREATE DATABASE LINK command, then, could be used to define the source database as follows :

SQL> CREATE DATABASE LINK INDIA

CONNECT TO admin1 IDENTIFIED BY admin1

USING ‘GOA’;

Now, The Data Pump import command, “impdp”, can now use this database link to directly access remote data.

The parameter NETWORK_LINK  points to the source database via its database link.

On the local database instance located in USA, user ‘ADMIN2’ executes the following command (all one line):

$ impdp admin2/admin2 TABLES= customers,sales,employees DIRECTORY=dpump1

NETWORK_LINK= INDIA

Tip :

You can get the information about database links in the view  :  DBA_DB_LINKS

Example :

SQL> DESC DBA_DB_LINKS

Click here to Join Our Blog and Stay Updated

How to Find out , Who / What is using your UNDO space?

Below are some views that show information related to undo activity..

* V$UNDOSTAT:                   This shows statistics for 10-minute intervals.

* V$TRANSACTION:             This provides information on current transactions.

* V$SESSTAT:                        Provides individual session statistics, which includes one for undo usage.

V$UNDOSTAT (This provides a hint regarding “who did the thing”)

This  records the longest running query for that 10-interval, through the “MAXQUERYID” column which may be linked to V$SQL and use columns “PARSING_USER_ID” or “PARSING_SCHEMA_NAME” the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions.

Check the below query :

SELECT  a.sid, a.username, b.used_urec, b.used_ublk

FROM v$session a, v$transaction b

WHERE a.saddr = b.ses_addr

ORDER BY b.used_ublk DESC

V$SESSTAT (Tells a hint “who uses the undo”)

Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value

FROM v$sesstat a, v$statname b

WHERE a.statistic# = b.statistic#

AND a.statistic# = 255    <-- Which stands for undo change vector size

ORDER BY a.value DESC

Click here to Join Our Blog and Stay Updated

Pre-check for DataGuard switchover Process

Please perform these pre-requisite checks before undertaking a switchover to primary.

Ø   ON STANDBY SITE:

The database_role must be “PHYSICAL STANDBY”  . . . check this using below query

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

Note: If you receive a reply like below

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

Then do not proceed if this message is received …in this case you are most probably on the primary site..

Next Check, Again on STANDBY SITE:

While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alertlog file,.. but make sure it is set to ENABLE before starting a switchover.

The below command can be issued to convert it to ENABLE

sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

Next Check :

If any of the datafiles are in RECOVER status, DO NOT PROCEED with the switchover…

SQL> select distinct status from v$datafile;

STATUS

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

ONLINE

ONLINE

SYSTEM

Next Check:

If the STANDBY database has been opened in READ ONLY mode, then do NOT proceed.

If the database is in mount mode, then only proceed.

(This is to ensure that the logs are being shipped and more importantly the LAST LOG archived has been applied…)

QL> select distinct open_mode from v$database;

OPEN_MODE

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

MOUNTED

Now,.. On PRIMARY site

SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED

——————

25

And  on STANDBY site.

SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED

—————-

25

Both the values are same, so you can Proceed for the Switchover.

Click here to Join Our Blog and Stay Updated