Oracle Database Experts

By Charles Kim

Categories
Archives
Calendar
September 2010
M T W T F S S
« Aug    
 12345
6789101112
13141516171819
20212223242526
27282930  
Search
Recent Posts
Meta
November 5th, 2009

Data Guard presentation at the Dallas Oracle User Group

By ckim

Here’s a recent presentation by Charles Kim, Bill Burke, and Nitin Vengurlekar on August 2009

Three of the authors of the Data Guard Handbook met in Dallas and provided a 2 hours presentation on Data Guard. Each of the authors took components of Data Guard and provided an in-depth overview of the technology.

Posted by Charles Kim, Oracle ACE

Posted in Data Guard | Comments Off
June 10th, 2009

Additional Configuration for the Logical Standby database

By ckim

Enable GUARD:

Guard the standby database from writes being able to modify tables that are being replicated:

Stop Redo Apply first 

alter database stop logical standby apply
SQL> /
Database altered.

SQL> alter database guard standby;
Database altered.

Skipping transactions for troubleshooting:
In general, you never want to skip a DML transaction with the fear of corrupting the logical standby data. If you do, it will most likely be DDL not DML. 

1  SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS
2* WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS)
    XIDUSN     XIDSLT     XIDSQN
---------- ---------- ----------
         9          2     732576
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(9,2,732576);

Additional Performance Tuning Tips:
We will add to this section on an on-going basis. As we encounter performance issues, we will add to this section of our findings.

Increase MAX Servers 

1* alter system set parallel_max_servers=27 scope=both sid='*'
SQL> /
System altered.

exec DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 27);

Increases LCR cache memory

execute dbms_logstdby.apply_set('MAX_SGA',2400);

Changes the number of apply processes. For a specific incident, you may have to change the following parameter up to 10 and then back down to 1: 

exec dbms_logstdby.apply_set('APPLY_SERVERS',1);

Posted by Charles Kim, Oracle ACE

Posted in Data Guard | No Comments
September 7th, 2007

Hot Standby – Perform Graceful Switchover

By ckim

Just to verify that the switchback actually works. As ckim account, create a table:

1* create table ckim_tables as select * from dba_tables;

Table created.

SQL> alter system switch logfile;

System altered.

Shutdown clean all the databases

shutdown the database on the primary

shutdown the standby database

Copy redo, control file and archive log from new primary to old primary (now standby)

cd /data/oracle/DBATEST

scp -rp redo01 oracle@rac1.dbaexpert.com:/data/oracle/DBATEST

scp -rp control01 oracle@rac1.dbaexpert.com:/data/oracle/DBATEST

scp -rp control02 oracle@rac1.dbaexpert.com:/data/oracle/DBATEST

Copy any archivelog that has not been applied yet:

rac2.dbaexpert.com:/apps/oracle/admin/DBATEST/arch

DBATEST > scp DBATEST_1_7.arc oracle@rac1.dbaexpert.com:/apps/oracle/admin/DBATEST/arch

Mount and recover the database and OPEN the database to make it the new primary DB

SQL> startup mount;

ORACLE instance started.

Total System Global Area 471303912 bytes

Fixed Size 743144 bytes

Variable Size 352321536 bytes

Database Buffers 117440512 bytes

Redo Buffers 798720 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

1* select file_name,status from dba_data_files;

FILE_NAME STATUS

——————————————— ———

/data/oracle/DBATEST/data01/system01.dbf AVAILABLE

/data/oracle/DBATEST/data01/undotbs01.dbf AVAILABLE

/data/oracle/DBATEST/data01/drsys01.dbf AVAILABLE

/data/oracle/DBATEST/data01/indx01.dbf AVAILABLE

/data/oracle/DBATEST/data01/tools01.dbf AVAILABLE

/data/oracle/DBATEST/data01/users01.dbf AVAILABLE

/data/oracle/DBATEST/data01/xdb01.dbf AVAILABLE

7 rows selected.

Check for ckim table

SQL> select table_name from dba_tables where owner=’CKIM’;

TABLE_NAME

——————————

CKIM_TABLES

create standby controlfile from the new primary – which is now the original primary

alter database create standby controlfile as ‘/tmp/control202.ctl’;

Copy the control file to build DR – which is the original DR

scp control202.ctl oracle@rac2:/tmp

On the DR server, copy the control file to the control file specified in the init.ora and start standby database

rac2.dbaexpert.com:/data/oracle/DBATEST/control01

DBATEST > cp /tmp/control.ctl control01.ctl

rac2.dbaexpert.com:/data/oracle/DBATEST/control01

DBATEST > cd ../control02

rac2.dbaexpert.com:/data/oracle/DBATEST/control02

DBATEST > cp /tmp/control.ctl control02.ctl

Start the standby database:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 471566056 bytes

Fixed Size 743144 bytes

Variable Size 352321536 bytes

Database Buffers 117440512 bytes

Redo Buffers 1060864 bytes

SQL> alter database mount standby database;

Database altered.

On the primary, generate some activity and redo

SQL> create table ckim_extents as select * from dba_extents;

Table created.

SQL> alter system switch logfile;

System altered.

On the DR site, perform standby recovery

SQL> recover standby database until cancel;

ORA-00279: change 26597319 generated at 09/07/2007 22:57:46 needed for thread 1

ORA-00289: suggestion : /apps/oracle/admin/DBATEST/standby_arch/DBATEST_1_8.arc

ORA-00280: change 26597319 for thread 1 is in sequence #8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 26601409 generated at 09/07/2007 23:17:33 needed for thread 1

ORA-00289: suggestion : /apps/oracle/admin/DBATEST/standby_arch/DBATEST_1_9.arc

ORA-00280: change 26601409 for thread 1 is in sequence #9

ORA-00278: log file ‘/apps/oracle/admin/DBATEST/standby_arch/DBATEST_1_8.arc’

no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

Verify Data Propagation

SQL> alter database open read only;

Database altered.

DBATEST > sqlplus ckim/oracle123

SQL*Plus: Release 9.2.0.5.0 – Production on Sat Sep 7 23:20:35 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production

JServer Release 9.2.0.5.0 – Production

SQL> select table_name from user_tables;

TABLE_NAME

——————————

CKIM_EXTENTS

CKIM_TABLES

Posted by: Charles Kim @ DBAExpert.com

Posted in Data Guard | No Comments
August 13th, 2007

Data Guard Broker Setup

By ckim

Data Guard Broker Preparation

Repeat these steps on both the primary and standby databases:

SQL> alter system set dg_broker_config_file1=’+DATA/dba11g/dba11g.dat’ scope=both;

System altered.

 

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both;

System altered.

 

Data Guard Broker Configuration

DBA11g > dgmgrl sys/oracle123

DGMGRL for Linux: Version 11.1.0.6.0 – Production

 

Copyright (c) 2000, 2005, Oracle. All rights reserved.

 

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> create configuration ‘DBA_DG’ as primary database is ‘DBA11g’ connect identifier is DBA11g.dbaexpert.com;

Configuration “DBA_DG” created with primary database “DBA11g”

DGMGRL> show configuration

 

Configuration

Name: DBA_DG

Enabled: NO

Protection Mode: MaxPerformance

Databases:

DBA11g – Primary database

 

Fast-Start Failover: DISABLED

 

Current status for “DBA_DG”:

DISABLED

 

DGMGRL> show configuration verbose;

 

Configuration

Name: DBA_DG

Enabled: NO

Protection Mode: MaxPerformance

Databases:

DBA11g – Primary database

 

Fast-Start Failover: DISABLED

 

Current status for “DBA_DG”:

DISABLED

DGMGRL> add database ‘DBA11gDR’ as connect identifier is DBA11gDR maintained as physical;

Database “DBA11gDR” added

DGMGRL> show configuration

 

Configuration

Name: DBA_DG

Enabled: NO

Protection Mode: MaxPerformance

Databases:

DBA11g – Primary database

DBA11gDR – Physical standby database

 

Fast-Start Failover: DISABLED

 

Current status for “DBA_DG”:

DISABLED

 

DGMGRL> show configuration verbose;

 

Configuration

Name: DBA_DG

Enabled: NO

Protection Mode: MaxPerformance

Databases:

DBA11g – Primary database

DBA11gDR – Physical standby database

 

Fast-Start Failover: DISABLED

 

Current status for “DBA_DG”:

DISABLED

 

DGMGRL> show configuration

 

Configuration

Name: DBA_DG

Enabled: NO

Protection Mode: MaxPerformance

Databases:

DBA11g – Primary database

DBA11gDR – Physical standby database

 

Fast-Start Failover: DISABLED

 

Current status for “DBA_DG”:

DISABLED

Posted in Data Guard | No Comments
August 13th, 2007

Data Guard 11g Database Duplication Over Network Logfile

By ckim

orapwd file=$ORACLE_HOME/dbs/orapwDBA11g entries=25 password=oracle123

DBA11gDR > ./dup.ksh

 

Recovery Manager: Release 11.1.0.6.0 – Production on Mon Aug 13 21:46:38 2007

 

Copyright (c) 1982, 2007, Oracle. All rights reserved.

 

RMAN>

connected to target database: DBA11G (DBID=231973368)

 

RMAN>

connected to auxiliary database: DBA11GDR (not mounted)

 

RMAN>

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32>

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=110 device type=DISK

 

allocated channel: prmy2

channel prmy2: SID=132 device type=DISK

 

allocated channel: prmy3

channel prmy3: SID=111 device type=DISK

 

allocated channel: prmy4

channel prmy4: SID=109 device type=DISK

 

allocated channel: stby

channel stby: SID=98 device type=DISK

 

Starting Duplicate Db at 13-AUG-07

 

contents of Memory Script:

{

backup as copy reuse

file ‘/apps/oracle/product/11.1.0/DB/dbs/orapwDBA11g’ auxiliary format

‘/apps/oracle/product/11.1.0/DB/dbs/orapwDBA11gDR’ file

‘+DATA/dba11g/spfiledba11g.ora’ auxiliary format

‘/apps/oracle/product/11.1.0/DB/dbs/spfileDBA11gDR.ora’ ;

sql clone “alter system set spfile= ”/apps/oracle/product/11.1.0/DB/dbs/spfileDBA11gDR.ora””;

}

executing Memory Script

 

Starting backup at 13-AUG-07

Finished backup at 13-AUG-07

 

sql statement: alter system set spfile= ”/apps/oracle/product/11.1.0/DB/dbs/spfileDBA11gDR.ora”

 

contents of Memory Script:

{

sql clone “alter system set audit_file_dest =

”/apps/oracle/admin/DBA11gDR/adump” comment=

”” scope=spfile”;

sql clone “alter system set dispatchers =

”(PROTOCOL=TCP) (SERVICE=DBA11gDRXDB)” comment=

”” scope=spfile”;

sql clone “alter system set db_unique_name =

”DBA11gDR” comment=

”” scope=spfile”;

sql clone “alter system set db_file_name_convert =

”/DBA11g/”, ”/DBA11gDR/” comment=

”” scope=spfile”;

sql clone “alter system set log_file_name_convert =

”/DBA11g/”, ”/DBA11gDR/” comment=

”” scope=spfile”;

sql clone “alter system set control_files =

”/nfs02/oradata/DBA11gDR/control.ctl” comment=

”” scope=spfile”;

sql clone “alter system set log_archive_max_processes =

5 comment=

”” scope=spfile”;

sql clone “alter system set fal_client =

”DBA11gDR” comment=

”” scope=spfile”;

sql clone “alter system set fal_server =

”DBA11g” comment=

”” scope=spfile”;

sql clone “alter system set standby_file_management =

”AUTO” comment=

”” scope=spfile”;

sql clone “alter system set log_archive_config =

”dg_config=(DBA11g,DBA11gDR)” comment=

”” scope=spfile”;

sql clone “alter system set log_archive_dest_1 =

‘’service=DBA11g LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=DBA11g” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount ;

}

executing Memory Script

 

sql statement: alter system set audit_file_dest = ”/apps/oracle/admin/DBA11gDR/adump” comment= ”” scope=spfile

 

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=DBA11gDRXDB)” comment= ”” scope=spfile

 

sql statement: alter system set db_unique_name = ”DBA11gDR” comment= ”” scope=spfile

 

sql statement: alter system set db_file_name_convert = ”/DBA11g/”, ”/DBA11gDR/” comment= ”” scope=spfile

 

sql statement: alter system set log_file_name_convert = ”/DBA11g/”, ”/DBA11gDR/” comment= ”” scope=spfile

 

sql statement: alter system set control_files = ”/nfs02/oradata/DBA11gDR/control.ctl” comment= ”” scope=spfile

 

sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile

 

sql statement: alter system set fal_client = ”DBA11gDR” comment= ”” scope=spfile

 

sql statement: alter system set fal_server = ”DBA11g” comment= ”” scope=spfile

 

sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile

 

sql statement: alter system set log_archive_config = ”dg_config=(DBA11g,DBA11gDR)” comment= ”” scope=spfile

 

sql statement: alter system set log_archive_dest_1 = ‘’service=DBA11g LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=DBA11g” comment= ”” scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area 318054400 bytes

 

Fixed Size 1299624 bytes

Variable Size 96471896 bytes

Database Buffers 213909504 bytes

Redo Buffers 6373376 bytes

 

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format ‘/nfs02/oradata/DBA11gDR/control.ctl’;

sql clone ‘alter database mount standby database’;

}

executing Memory Script

 

Starting backup at 13-AUG-07

channel prmy1: starting datafile copy

copying standby control file

output file name=/apps/oracle/product/11.1.0/DB/dbs/snapcf_DBA11g.f tag=TAG20070813T214752 RECID=3 STAMP=630539277

channel prmy1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 13-AUG-07

 

sql statement: alter database mount standby database

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

 

contents of Memory Script:

{

set newname for tempfile 1 to

“+data”;

switch clone tempfile all;

set newname for datafile 1 to

“+data”;

set newname for datafile 2 to

“+data”;

set newname for datafile 3 to

“+data”;

set newname for datafile 4 to

“+data”;

set newname for datafile 5 to

“+data”;

set newname for datafile 6 to

“+data”;

backup as copy reuse

datafile 1 auxiliary format

“+data” datafile

2 auxiliary format

“+data” datafile

3 auxiliary format

“+data” datafile

4 auxiliary format

“+data” datafile

5 auxiliary format

“+data” datafile

6 auxiliary format

“+data” ;

sql ‘alter system archive log current’;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to +data in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting backup at 13-AUG-07

channel prmy1: starting datafile copy

input datafile file number=00001 name=+DATA/dba11g/datafile/system.270.630497885

channel prmy2: starting datafile copy

input datafile file number=00002 name=+DATA/dba11g/datafile/sysaux.276.630497887

channel prmy3: starting datafile copy

input datafile file number=00005 name=+DATA/dba11g/datafile/example.268.630498257

channel prmy4: starting datafile copy

input datafile file number=00006 name=+DATA/dba11g/datafile/tools.280.630536173

output file name=+DATA/dba11gdr/datafile/example.259.630539237 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy3: datafile copy complete, elapsed time: 00:00:46

channel prmy3: starting datafile copy

input datafile file number=00003 name=+DATA/dba11g/datafile/undotbs1.274.630497891

output file name=+DATA/dba11gdr/datafile/tools.258.630539237 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:47

channel prmy4: starting datafile copy

input datafile file number=00004 name=+DATA/dba11g/datafile/users.273.630497891

output file name=+DATA/dba11gdr/datafile/users.256.630539283 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy4: datafile copy complete, elapsed time: 00:00:07

output file name=+DATA/dba11gdr/datafile/undotbs1.257.630539281 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy3: datafile copy complete, elapsed time: 00:00:17

output file name=+DATA/dba11gdr/datafile/sysaux.260.630539237 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy2: datafile copy complete, elapsed time: 00:02:25

output file name=+DATA/dba11gdr/datafile/system.261.630539237 tag=TAG20070813T214807 RECID=0 STAMP=0

channel prmy1: datafile copy complete, elapsed time: 00:02:35

Finished backup at 13-AUG-07

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=630539391 file name=+DATA/dba11gdr/datafile/system.261.630539237

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=630539391 file name=+DATA/dba11gdr/datafile/sysaux.260.630539237

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=630539391 file name=+DATA/dba11gdr/datafile/undotbs1.257.630539281

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=630539391 file name=+DATA/dba11gdr/datafile/users.256.630539283

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=630539391 file name=+DATA/dba11gdr/datafile/example.259.630539237

datafile 6 switched to datafile copy

input datafile copy RECID=8 STAMP=630539391 file name=+DATA/dba11gdr/datafile/tools.258.630539237

Finished Duplicate Db at 13-AUG-07

 

sql statement: alter system set log_archive_config=”dg_config=(DBA11g,DBA11gDR)”

 

sql statement: alter system set log_archive_dest_1=’’service=DBA11gDR LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=DBA11gDR”

 

sql statement: alter system set log_archive_max_processes=5

 

sql statement: alter system set fal_client=DBA11g

 

sql statement: alter system set fal_server=DBA11gDR

 

sql statement: alter system set standby_file_management=AUTO

 

sql statement: alter system set log_archive_dest_state_1=enable

 

sql statement: alter system archive log current

 

allocated channel: stby

channel stby: SID=146 device type=DISK

 

sql statement: alter database recover managed standby database using current logfile disconnect

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

Posted in Data Guard | No Comments
|