Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for December, 2010

ReConfigure Data Guard using Cold Backup from Primary Database

Posted by Hendry chinnapparaj on December 20, 2010

Hendry Dasan

Monday, 20 December 2010

 

ReConfigure Data Guard using Cold Backup from Primary Database

 

The current status is

 

Primary001 – primary server

Standby001 – Standby server, but no database running.

 

Primary Server Details

 

Server name: Primary001

 

LOG_MODE     FORCE_LOGGING

———— ————-

ARCHIVELOG   YES

1 row selected.

Time End: 16/12/2010 5:59:54 PM

Elapsed Time for Script Execution: 1 sec

 

SELECT DBID,

NAME,

DB_UNIQUE_NAME,

LOG_MODE,

OPEN_MODE,

PROTECTION_MODE,

PROTECTION_LEVEL,

DATABASE_ROLE,

FLASHBACK_ON,

CURRENT_SCN

FROM V$DATABASE;

Time Start: 16/12/2010 5:15:40 PM

 

DBID NAME      DB_UNIQUE_NAME                 LOG_MODE     OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE

———- ——— —————————— ———— ———- ——————– ——————– —————-

FLASHBACK_ON       CURRENT_SCN

—————— ———–

224883408 REPLICA    REPLICA_fc                      ARCHIVELOG   READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY

YES                 2.6969E+10

 

1 row selected.

Time End: 16/12/2010 5:15:41 PM

Elapsed Time for Script Execution: 1 sec

 

 

SELECT COUNT(*) TOTAL_DATAFILES FROM  V$DATAFILE;

SELECT COUNT(*) TOTAL_LOGFILES_AND_STBY_FILES FROM  V$LOGFILE;

SELECT COUNT(*) TOTAL_CONTROLFILES FROM  V$CONTROLFILE;

 

Time Start: 16/12/2010 5:16:24 PM

 

TOTAL_DATAFILES

—————

625

1 row selected.

 

TOTAL_LOGFILES_AND_STBY_FILES

—————————–

13

1 row selected.

 

TOTAL_CONTROLFILES

——————

3

1 row selected.

Time End: 16/12/2010 5:16:25 PM

Elapsed Time for Script Execution: 1 sec

 

 

SELECT SUM(BYTES)/(1024*1024*1024) DF_SIZE_IN_GB FROM V$DATAFILE;

SELECT SUM(BYTES)/(1024*1024*1024) LOGFILE_SIZE_IN_GB FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP#;

SELECT SUM(BYTES)/(1024*1024*1024) TEMPFILE_IN_GB FROM V$TEMPFILE WHERE STATUS=’ONLINE’;

 

Time Start: 16/12/2010 5:17:11 PM

 

DF_SIZE_IN_GB

————-

1995.90629

1 row selected.

 

LOGFILE_SIZE_IN_GB

——————

16

1 row selected.

 

TEMPFILE_IN_GB

————–

13.8212891

1 row selected.

Time End: 16/12/2010 5:17:19 PM

Elapsed Time for Script Execution: 8 secs

 

 

SELECT SUM(BYTES) /1024/1024/1024 “Read only in GB” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;

 

Time Start: 16/12/2010 5:17:55 PM

 

Read only in GB

—————

985.458984

1 row selected.

Time End: 16/12/2010 5:17:56 PM

Elapsed Time for Script Execution: 1 sec

 

 

— SHOW THE DATAFILES WHICH ARE OFFLINE

select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

 

 

Time Start: 16/12/2010 5:19:05 PM

no rows selected.

Time End: 16/12/2010 5:19:06 PM

Elapsed Time for Script Execution: 687 msecs

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/10.2/db_1/

dbs/spfileREPLICA.ora

 

Select * from v$tempfile;

 

Time Start: 16/12/2010 5:20:29 PM

 

FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- —————- ————- ———- ———- ——- ———- ———- ———- ———— ———-

NAME

——————————————————————————————————————————————–

1         78931268 06-AUG-08              3          1 ONLINE  READ WRITE 1.4840E+10    1811584   1.4840E+10       8192

/u02/oradata/REPLICA/temp01.dbf

 

1 row selected.

Time End: 16/12/2010 5:20:30 PM

Elapsed Time for Script Execution: 641 msecs

 

Select * from dba_temp_files;

Select count(*) from dba_data_files;

 

 

 

SELECT NAME “Read only” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;

 

 

Time Start: 16/12/2010 5:22:03 PM

 

Read only

——————————————————————————————————————————————–

/u04/oradata/REPLICA/CB001_01.DBF

/u04/oradata/REPLICA/CB002_01.DBF

/u04/oradata/REPLICA/CB003_01.DBF

/u04/oradata/REPLICA/CB004_01.DBF

/u04/oradata/REPLICA/CB005_01.DBF

/u04/oradata/REPLICA/CB006_01.DBF

/u04/oradata/REPLICA/CB007_01.DBF

/u04/oradata/REPLICA/CB008_01.DBF

/u04/oradata/REPLICA/CB009_01.DBF

/u04/oradata/REPLICA/CB010_01.DBF

/u04/oradata/REPLICA/CB011_01.DBF

/u04/oradata/REPLICA/CB012_01.DBF

/u04/oradata/REPLICA/CB013_01.DBF

/u04/oradata/REPLICA/CB014_01.DBF

/u04/oradata/REPLICA/CB015_01.DBF

/u04/oradata/REPLICA/CB016_01.DBF

/u04/oradata/REPLICA/CB017_01.DBF

/u04/oradata/REPLICA/CB018_01.DBF

/u04/oradata/REPLICA/CB019_01.DBF

/u04/oradata/REPLICA/CB020_01.DBF

/u04/oradata/REPLICA/CB021_01.DBF

/u04/oradata/REPLICA/CB022_01.DBF

/u04/oradata/REPLICA/CB023_01.DBF

/u04/oradata/REPLICA/CB024_01.DBF

/u04/oradata/REPLICA/CB025_01.DBF

/u04/oradata/REPLICA/CB026_01.DBF

/u04/oradata/REPLICA/CB027_01.DBF

/u04/oradata/REPLICA/CB028_01.DBF

/u04/oradata/REPLICA/CB029_01.DBF

/u04/oradata/REPLICA/CB030_01.DBF

/u04/oradata/REPLICA/CB031_01.DBF

/u04/oradata/REPLICA/CB032_01.DBF

/u04/oradata/REPLICA/CB033_01.DBF

/u04/oradata/REPLICA/CB034_01.DBF

/u04/oradata/REPLICA/CB035_01.DBF

/u04/oradata/REPLICA/CB036_01.DBF

/u04/oradata/REPLICA/CB037_01.DBF

/u04/oradata/REPLICA/CB038_01.DBF

/u04/oradata/REPLICA/CB039_01.DBF

/u04/oradata/REPLICA/CB040_01.DBF

/u04/oradata/REPLICA/CB041_01.DBF

/u04/oradata/REPLICA/CB042_01.DBF

/u04/oradata/REPLICA/CB043_01.DBF

/u04/oradata/REPLICA/CB044_01.DBF

/u04/oradata/REPLICA/CB045_01.DBF

/u04/oradata/REPLICA/CB046_01.DBF

/u04/oradata/REPLICA/CB047_01.DBF

/u04/oradata/REPLICA/CB048_01.DBF

/u04/oradata/REPLICA/CB049_01.DBF

/u04/oradata/REPLICA/CB050_01.DBF

/u04/oradata/REPLICA/CB051_01.DBF

/u04/oradata/REPLICA/CB052_01.DBF

/u04/oradata/REPLICA/CB053_01.DBF

/u04/oradata/REPLICA/CB054_01.DBF

/u04/oradata/REPLICA/CB055_01.DBF

/u04/oradata/REPLICA/CB056_01.DBF

/u04/oradata/REPLICA/CB057_01.DBF

/u04/oradata/REPLICA/CB058_01.DBF

/u04/oradata/REPLICA/CB059_01.DBF

/u04/oradata/REPLICA/CB060_01.DBF

/u04/oradata/REPLICA/CB061_01.DBF

/u04/oradata/REPLICA/CB062_01.DBF

/u04/oradata/REPLICA/CB063_01.DBF

/u04/oradata/REPLICA/CB064_01.DBF

/u04/oradata/REPLICA/CB065_01.DBF

/u04/oradata/REPLICA/CB066_01.DBF

/u04/oradata/REPLICA/CB067_01.DBF

/u04/oradata/REPLICA/CB068_01.DBF

/u04/oradata/REPLICA/CB069_01.DBF

/u04/oradata/REPLICA/CB070_01.DBF

 

70 rows selected.

Time End: 16/12/2010 5:22:04 PM

Elapsed Time for Script Execution: 797 msecs

 

 

 

 

 

Standby Server Details

 

 

Server name: standby001

 

 

 

OPERARTIONS

 

 

Shutdown the PRIMARY Database, Listeners and Standby

 

Login to primary001

 

SQL> shutdown immediate

 

oracle@primary001 scptest>ps -ef|grep tns

oracle  831554       1   0   Mar 12      – 167:37 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_fc_dg -inherit

oracle 1122458       1   0   Jul 22      – 64:15 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_public –inherit

 

lsnrctl stop REPLICA_public

lsnrctl stop REPLICA_fc_dg

 

Login to standby server – standby001 and stop the Listener is any running

 

oracle@standby001 oracle>ps -ef|grep tns

oracle  659472  880772   0 10:15:42  pts/1  0:00 grep tns

oracle  950348       1   0   Jul 16      –  9:53 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_js_dg –inherit

 

lsnrctl stop REPLICA_js_dg

 

 

 

Copy / scp the datafiles to DR Server – standby001

 

Do a cleanup on the DR Server if required – check the relevant directories

 

scp -pr /u04/oradata/REPLICA/* oracle@standby001:/u04/oradata/REPLICA

 

scp -pr /u02/oradata/REPLICA/* oracle@standby001:/u02/oradata/REPLICA

 

pwd

lpwd

cd

lcd

 

Note:- scp by default is binary copy, so no worries

 

 

if required you can use TOAD to compare the files under both server directories after capturing them on the excel sheet

 

 

 

Create STANDBY Controlfile in Primary Server / Database

 

 

Login to primary001 Primary Server

 

SQL> startup

 

SQL> Alter database create standby controfile as ‘/u01/oracle/control01.ctl’;

 

Do few Log switches on Primary Server

 

SQL> alter system switch logfile;

/

/

 

 

SQL>

 

 

 

Copy / sftp this file to /u02/oradata/REPLICA

 

sftp  oracle@standby001

pwd: *****

cd /u02/oradata/REPLICA

lcd /u01/oracle

 

 

Also Copy / sftp the Archive logs to DR Server – standby001

 

 

Login to standby001 – DR Server and create the Archivelog Directory

 

cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

oracle@standby001 archivelog>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

mkdir 2010_12_19

 

 

 

 

 

 

sftp oracle@standby001

pwd:****

cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19

lcd /u03/oradata/flash_recovery_area/REPLICA_FC/archivelog/2010_12_19

 

sftp> mput *

 

oracle@standby001 archivelog>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

oracle@standby001 archivelog>ls -lrt

total 8

drwxr-xr-x   2 oracle   dba            2048 Dec 19 22:10 2010_12_19

drwxr-x—   2 oracle   dba            2048 Dec 20 12:55 2010_12_20

oracle@standby001 archivelog>cd 2010_12_19

oracle@standby001 2010_12_19>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19

oracle@standby001 2010_12_19>ls -lrt

total 3212664

-r–r—–   1 oracle   dba          167424 Dec 19 15:31 o1_mf_1_1802_1E4rhl3O2_.arc

-r–r—–   1 oracle   dba        45288960 Dec 19 15:31 o1_mf_1_1803_1E4s16McD_.arc

-r–r—–   1 oracle   dba         2191360 Dec 19 15:31 o1_mf_1_1805_1E4sC2jT2_.arc

-r–r—–   1 oracle   dba        76392960 Dec 19 15:31 o1_mf_1_1804_1E4sBwGUH_.arc

-r–r—–   1 oracle   dba        45288960 Dec 19 15:38 o1_mf_1_1803_1E4sbI1M8_.arc

-r–r—–   1 oracle   dba         2191360 Dec 19 15:38 o1_mf_1_1805_1E4sbkaFE_.arc

-r–r—–   1 oracle   dba        76392960 Dec 19 15:38 o1_mf_1_1804_1E4sbT251_.arc

 

 

Startup the Standby Database in DR Server

 

Login to standby001 and recheck the initREPLICA.ora parameters if needed for stby parameters

Ensure the controlfile is pointing to the just copied standby controlfile under “/u02/oradata/REPLICA” directory.

 

Sqlplus / as sysdba

 

 

Startup nomount pfile=initREPLICA.ora

Alter database mount standby database;

 

 

Create Standby Log files and temp file

 

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

‘/u02/oradata/REPLICA/temp01.dbf’ SIZE 14153M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED

TABLESPACE GROUP ”

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

alter system set standby_file_management=manual;

 

.alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_09.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_10.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_11.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_12.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_13.log’ SIZE 2G reuse;

 

alter system set standby_file_management=auto;

 

 

Enable Block Change Tracking in standby server

 

SQL> alter database enable block change tracking using file ‘/u01/oracle/product/10.2.0/db_1/dbs/REPLICA_bct.dbf’ reuse;

 

 

 

Stop the job_queue_processes  STANDBY database by setting them to 0

 

SQL> alter system set job_queue_processes=0 scope=memory;

 

SQL> alter database recover managed standby database disconnect from session

 

 

IF needed , do check in Primary Server / database

– CHECK THE STATUS IF VALID FOR STBY

select dest_id, status from v$archive_dest;

show parameter dest_state_2

 

 

— TO disable log shipping to stby site

alter system set log_archive_dest_state_2=defer scope=memory;

 

— TO disable log shipping to stby site

alter system set log_archive_dest_state_2=enable scope=memory;

 

 

Check the open_mode, protection_mode, database_role, Flashback Status in the STANDBY Server

 

SQL> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;

 

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE    FLASHBACK_ON

——— ———- ——————– —————- ——————

REPLICA    MOUNTED    MAXIMUM AVAILABILITY PHYSICAL STANDBY YES

 

SQL> select * from v$archive_gap;

 

 

Check the open_mode, protection_mode, database_role, Flashback Status in the PRIMARY Server

 

SQL> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;

 

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE    FLASHBACK_ON

——— ———- ——————– —————- ——————

REPLICA    READ WRITE MAXIMUM AVAILABILITY PRIMARY          YES

 

 

 

Also run the ControlM Job and verify if the standby database is in sync with Primary

Run the Script from the PRIMARY Database

 

oracle@primary001 logs>cat REPLICA.stby_check.20101220_1134.log

 

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 11:34:58 2010

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK

 

 

INST_NAME HOST_NAME  PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED   LOG_GAP

——— ———- ——————– ———————- ———— ———– ————– ——-

REPLICA    primary001 MAXIMUM AVAILABILITY                     OK         1855        1855 20/12 11:30:57       0

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

Making initREPLICA.ora changes permanent in Standby

 

Alter system job_queue_processes=0 scope=spfile

….

 

Create spfile from pfile;

 

Shutdown immediate

Startup nomount

Alter database mount standby database

alter database recover managed standby database disconnect from session

 

 

 

 

 

TEST THE STANDBY IN READ ONLY MODE

 

 

In Primary – primary001

 

Login as db user

 

Bb15908

 

Create table test as select * from dba_users;

 

Alter system switch logfile;

 

/

 

Login to standby server – standby001

Note:- if you have enable DB Auditing , then the following is required to open the database in read only mode.

 

SQL> Recover managed standby database cancel;

Media recovery complete.

SQL> alter database open readonly;

alter database open readonly

*

ERROR at line 1:

ORA-02288: invalid OPEN mode

 

 

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16006: audit_trail destination incompatible with database open mode

 

 

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      DB

 

 

SQL> alter system set audit_trail=OS scope=spfile;

 

System altered.

 

Shutdown immediate

 

 

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 3321888768 bytes

Fixed Size                  2087680 bytes

Variable Size            1828717824 bytes

Database Buffers         1476395008 bytes

Redo Buffers               14688256 bytes

SQL> alter database mount standby database;

 

Database altered.

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      OS

 

 

 

SQL> alter database open read only;

 

SQL> select * from bb15908.test;

 

Make sure you set back the audit_trail=DB after this

 

 

 

Shutdown immediate

Startup nomount

Alter database mount standby database

 

 

SQL> alter system set audit_trail=DB scope=spfile;

 

System altered.

 

Shutdown immediate

Startup nomount

Alter database mount standby database;

alter database recover managed standby database disconnect from session;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

Posted in Data Guard and DR | Leave a Comment »

Manually Switchback the OAT Database from primary001 to standby001

Posted by Hendry chinnapparaj on December 20, 2010

 

Hendry Dasan

Monday, 20 December 2010

 

 

 

Manually Switchback the OAT Database from primary001 to standby001

 

The current status is

Standby001 – standby

Primary001 – primary

 

 

 

 

PRIMARY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

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

 

Database altered.

 

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2801795072 bytes

Fixed Size                  2086680 bytes

Variable Size            1711278312 bytes

Database Buffers         1073741824 bytes

Redo Buffers               14688256 bytes

Database mounted.

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE

———————————— ——————————–

VALUE

——————————

job_queue_processes                  integer

0

 

 

STANDBY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SWITCHOVER LATENT

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

TO PRIMARY

 

 

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

———-

MOUNTED

 

SQL> alter database open;

 

Database altered.

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

 

 

STANDBY

SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Media recovery complete.

 

PRIMARY

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     4

SQL>

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 DEFERRED

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

 

System altered.

 

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 VALID

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

 

STANDBY

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

WAIT_FOR_LOG

IDLE

IDLE

 

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

APPLYING_LOG

IDLE

IDLE

 

 

PRIMARY

oracle@standby001 logs>cat replica.stby_check.20101220_1224.log

 

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 12:24:23 2010

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK

 

 

INST_NAME HOST_NAME  PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED   LOG_GAP

——— ———- ——————– ———————- ———— ———– ————– ——-

replica    standby001 MAXIMUM AVAILABILITY                     OK         1863        1863 20/12 12:20:37       0

 

 

DATAGUARD STATUS CHECK RETURNED NO ROWS – CHECK V$ARCHIVED_LOG FOR POSSIBLE CAUSE

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

oracle@standby001 logs>

 

 

 

QL> SHOW PARAMETER JOB_QUEUE

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

Posted in Data Guard and DR | Leave a Comment »

Get High Water Mark (HWM) stats for a table – Oracle9i

Posted by Hendry chinnapparaj on December 15, 2010

What is High Water Mark(HWM)?

HWM is boundry between used block and unused block. Blocks below HWM is used blocks and above HWM is unused blocks. Used blocks might or might not have the data. In normal operations (Insert/update), the HWM is mostly bump up and never go down. Oracle tracks the HWM for the segment in segment header.

The Following script can be run as a privileged user to get the stats

set serveroutput on
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name=’OLS’) LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => ‘OLSUSER’,
segment_name => ‘OLS’ ,
segment_type => ‘TABLE’ ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( ‘Data for ‘|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD(‘*’,LENGTH(c1_row.table_name) + 10,’*’));
DBMS_OUTPUT.PUT_LINE( ‘Total Blocks……………..’|| alc_bks );
DBMS_OUTPUT.PUT_LINE( ‘Total Bytes………………’||alc_bts );
DBMS_OUTPUT.PUT_LINE( ‘Unused Blocks…………….’|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( ‘Unused Bytes……………..’||unsd_bts );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext File Id……..’|| luefi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext Block Id…….’|| luebi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Block…………..’||lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

steps if you’re rebuilding

1) Run the script above, collect the pre-build stats

2) Rebuild the table

3) Run the script again, collect the post-build stats

4) space reclaimed after the rebuild

For Example

HWM before Rebuild

 

Data for TABLE X
****************************
Total Blocks……………..5248
Total Bytes………………42991616
Unused Blocks…………….64
Unused Bytes……………..524288
Last Used Ext File Id……..13
Last Used Ext Block Id…….428681
Last Used Block…………..64
PL/SQL procedure successfully completed.

HWM = Total Blocks – Unused Blocks
HWM=5248-64=5184
LN10 – after

Data for TABLE X
****************************
Total Blocks……………..1536
Total Bytes………………12582912
Unused Blocks…………….114
Unused Bytes……………..933888
Last Used Ext File Id……..13
Last Used Ext Block Id…….149257
Last Used Block…………..14
PL/SQL procedure successfully completed.

HWM = Total Blocks – Unused Blocks

HWM=1536-114=1422

When ever optimizer takes full table scan, it scan all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

Oracle does not release the free space under HWM for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The additional benefit of resetting HWM is a more efficient utilization of space because the space freed is available for other objects.

Resetting the HWM?

Option 1
Alter table OLS move tablespace

This option requires rebuilding the indexes. since the index will be invalid/unusable after running the above command. The downside is, rebuilding the index is additional overhead when we use this option. Also users can not use the application or reports while rebuilding the index.

Option 2

1. Export the data
2. truncate the table
3. import the table
4. Analyze the table

Option 3
1. copy the table data
2. truncate the original table
3. insert back.

Option 4
Use DBMS_REDEFINITION package to copy the table.

Posted in Oracle9i | Leave a Comment »