Oracle Hints

Anúncios
Publicado em HINTS, PLSQL SCRIPTS, VIDEOS | Marcado com , | Deixe um comentário

Change Management Pack

Publicado em PLSQL SCRIPTS, RECOMENDAÇÕES, TUTORIAL, VIDEOS | Marcado com | 1 Comentário

Real Application Testing 2/3 – YouTube

Publicado em REPLAY, TUTORIAL, VIDEOS | Deixe um comentário

Real Application Testing 1/3

Publicado em REPLAY, VIDEOS | Deixe um comentário

Oracle SQL Tuning Set

Publicado em PLSQL TUNING, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Data Masking

Publicado em DATA MASKING, PLSQL TUNING, TUTORIAL, VIDEOS | Deixe um comentário

Diagnostic and Tuning Pack

Publicado em AWR, STATSPACK, VIDEOS | Marcado com | Deixe um comentário

AWR Text Report HOWTO

Publicado em AWR, TUTORIAL, VIDEOS | Marcado com | Deixe um comentário

Getting AWR Report from SQLPlus

Publicado em AWR, PLSQL TUNING, VIDEOS | Marcado com | Deixe um comentário

Oracle Performance Monitoring in less than 12 minutes 20 part 2/2

Publicado em FAST DIAG, TUTORIAL, VIDEOS | Marcado com | Deixe um comentário

Oracle Performance Monitoring in less than 12 minutes 20 part 1/2

Publicado em FAST DIAG, RECOMENDAÇÕES, VIDEOS | Marcado com | Deixe um comentário

O QUE É ORACLE E-BUSINESS SUITE?

Oracle E-Business Suite é um pacote de aplicativos que permite a uma organização gerenciar os processos chaves de seu negócio. Este aplicação é conhecida no mercado por vários nomes:

  • Oracle Enterprise Resource Planning (ERP)
  • Oracle Apps
  • Oracle Applications
  • Oracle Financials
  • e-Biz
  • EBS (e-Business Suite).

Neste post irei usar a referencia como E-Business Suite ou Oracle Applications.

No passado, não muito distante, era pratica comum para as organizações desenvolver software  “em casa”, para automatizar os processos do negócio. Por exemplo, grande partes das empresas necessitam de um sistema que faça compras de fornecedores e um sistema que faça pagamento dos fornecedores e eventos conhecidos como transações que devem ser contabilizadas no relatórios financeiros. Enterprise Resource Planning (ERP) é um pacote de softwares que disponibiliza diferentes tipos de funcionalidades dentro de si, então um cliente que compra este pacote de software não precisa desenvolver o mesmo software novamente.

Familia de Produtos

Oracle E-Business Suite é um produto que cobre grande parte de todo o fluxo de negócio amplamente usados na maioria das organizações. O negócio pode implementar quantos módulos forem necessários mas o sistema continua naturalmente integrado devido a arquitetura do E-Business Suite. Isto permite que as informações continuem integradas e disponível para a empresa; isto reduz muito os gastos com a TI (Tecnologia da informação) e torna o negocio mais eficiente.

Ao contrario de se tentar gerenciar uma solução de software geralmente heterogêneo, desenvolvida “em casa”, que frequentemente usa diferentes sistemas e tecnologias de desenvolvimento, sendo portanto extremamente dispendioso e complexo.

Os produtos oferecidos pelo E-Business Suite são organizados em famílias:

  • Financials
  • Procurement
  • Customer Relationship Management (CRM)
  • Project Management
  • Supply Chain Planning and Management
  • Discrete Manufacturing
  • Process Manufacturing
  • Order Management
  • Human Resources Management System (HRMS)
  • Aplications Technology

No E-Business Suite, cada família consiste de aplicativos.

Por exemplo, alguns dos aplicativos que compõem o produto Oracle Financials são:

  • General Ledger (GL)
  • Paybles (AP)
  • Receivables (AR)
  • Cahs Management
  • iReceivables
  • iExpenses

Links úteis para  prestar a prova.

IZO – 204

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1080544&lang=US&p_exam_id=1Z0_204

Material de Estudo

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D58324

Learn To:

Access and navigate within Oracle Applications using the user interface.
Explore a high-level understanding of major architectural components in R12.1 E-Business Suite
Identify and describe the benefits of R12.1 footprint
Explain the basic concepts of System Administration
Define key and descriptive Flexfields
Recognize the different entities that are shared between multiple applications
Identify features of Multiple Organizations and Multiple Organization Access Control (MOAC)
Explain features and benefits of Oracle Workflow
Use the Workflow Monitor to monitor a Workflow to completion
Describe Alerts and test a Periodic Alert
Describe the features of Oracle Business Intelligence (OBI) Applications
Audience
End Users
Functional Implementer
Course Objectives
Navigate within R12.1 Oracle E-Business Suite
Understand conceptually the major architectural components of R12.1 Oracle E-Business Suite
Describe basic concepts of R12.1 Oracle System Administration
Define key and descriptive Flexfields
Identify shared entities across R12.1 Oracle E-Business Suite
Describe features and benefits of Multiple Organization Access Control (MOAC)
Create Oracle Alerts and test a Periodic Alert
Learn the key features and benefits of R12.1 Oracle Workflow
Understand basic features of Oracle Business Intelligence (OBI) Applications
Course Topics
R12.1 Oracle E-Business Essentials for Implementers: Overview
Objectives
Course purpose
More help about documentation and other resources
OU courses
Summary
Copyright © 2011, Oracle. All rights reserved. Page 1Navigating in R12.1 Oracle Applications
Logging into and logging off from Oracle Applications
Accessing Responsibilities
Using forms & menus
Data Flow Across Oracle Applications
Entering data using forms
Retrieving, Editing and Deleting records
Accessing Online Help
Running reports and programs
Introduction to R12.1 Oracle Applications
Introduction to R12.1 Footprint
Benefits of R12.1 Footprint
R12.1 E-Business Suite Architecture
Basic Technical Architecture
Overview of Oracle file system and directories
Brief Overview/Introduction about Oracle E-Business Suite Integrated SOA Gateway
Fundamentals of System Administration
Application Security Overview
Function Security
Data Security
Profile Options
Standard request submission (SRS)
Fundamentals of Flexfields
Basics of Flexfields
Define Value sets
Define Key Flexfields
Define Descriptive Flexfields
Planning Decisions
Flexfield Enhancements
Shared Entities and Integration
Shared entities and non-key shared entities
Overview of E-Business Suite business flows and integration
Fundamentals of Multiple-Organization and Multiple-Organization Access Control (MOAC)
Introduction to Multiple-Organization and Multiple- Organization Access control (MOAC)
Types of organizations supported in the Multi-Org model
Multiple-Organization Access Control Setup and Process
Reporting across entities
Key Implementation considerations
Fundamentals of Oracle Workflow & Alerts
Overview of Workflow
Oracle Workflow Home Pages
Worklist WebPages
Workflow Monitor WebPages
Overview of Alerts
Difference between Workflow & Alerts
Copyright © 2011, Oracle. All rights reserved. Page 2Oracle Business Intelligence (OBI) Applications: Overview
Oracle BI Applications: Overview
Oracle BI Applications: Multisource Analytics
Application Integration: Security
Application Integration: Action Links
Guided Navigation
Deployment Options
Related Courses
R12.x Oracle E-Business Suite Essentials for Implementers – Self Study Course

Publicado em ORACLE EBS | Deixe um comentário

Documentação do Oracle E-Business Suite

Você poderá obter informações adicionais sobre os topicos abaixo nos links indicados:

http://download-uk.oracle.com/docs/cd/B34956_01/current/html/docset.html

http://www.oracle.com/technology/index.html > Documentation > Applications > Oracle E-Business Suite Online Documentation Library Release 12 Oracle Applications Multiple Organizations Implementation Guide

http://download.oracle.com/docs/cd/B40089_05/current/acrobat/120funmo.pdf

Abaixo, você pode encontrar informações sobre as principais famílias de produtos do E-Business Suite nos seguintes  links:
• Financials:

http://www.oracle.com/applications/financials/intro.html

• Human Resource Management:

http://www.oracle.com/applications/human_resources/intro.html

• Supply Chain Management:

http://www.oracle.com/applications/scm/index.html

• Customer Relationship Management:

http://www.oracle.com/applications/crm/index.html

• Projects:

http://www.oracle.com/applications/projects/intro.html

• Procurement:

http://www.oracle.com/applications/procurement/intro.html

Publicado em ORACLE EBS, TUTORIAL | Deixe um comentário

Install Oracle Applications E-Business Suite R12 on Any Windows using VMWare Virtual Machine 3/3

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Install Oracle Applications E-Business Suite R12 on Any Windows using VMWare Virtual Machine 2/3

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Install Oracle Applications E-Business Suite R12 on Any Windows using VMWare Virtual Machine 1/3

Publicado em ORACLE EBS, TUTORIAL | Deixe um comentário

OEL 5.6.Installation

Publicado em LINUX, VIDEOS | Deixe um comentário

Oracle E-Business Suite 12.1.1 Installation on OEL 5.6 – YouTube

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – iProcurement in Oracle E-Business Suite R12

Publicado em ORACLE 11gR2, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Navigating in Oracle E-Business Suite R12

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Create Purchase Requisition in Oracle E-Business Suite R12 – YouTube

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle EBS R12 Training – HRMS Create New Employee

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Create Purchase Order from Requisition in Oracle E-Business Suite R12

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Accounts Payable in Oracle E-Business Suite R12

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Order Management in Oracle E-Business Suite R12 – Part 1 (1080p – HD) – YouTube

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Order Management in Oracle E-Business Suite R12 – Part 2

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Order Management in Oracle E-Business Suite R12 – Part 2 (1080p – HD)

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Oracle Training – Defining Ledgers in General Ledger Part 1 – Oracle E-Business Suite R12

Publicado em ORACLE EBS, TUTORIAL, VIDEOS | Deixe um comentário

Oracle Training – Defining Ledgers in General Ledger Part 2 – Oracle E-Business Suite R12 – YouTube

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Oracle Training – Navigating in Oracle E-Business Suite R12

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Navigating in Oracle E-Business Suite R12

Publicado em ORACLE EBS, VIDEOS, VIRTUAL BOX | Deixe um comentário

Installing e-Business Suite R12 on Linux part 3

Publicado em ORACLE EBS, VIDEOS, VIRTUAL BOX | Deixe um comentário

Installing e-Business Suite R12 on Linux part 2

Publicado em ORACLE EBS, VIDEOS, VIRTUAL BOX | Deixe um comentário

Installing e-Business Suite R12 on Linux part 1

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Oracle EBS R12 Installation / Part-1

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Oracle EBS R12 Installation / Part-2

Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Oracle E-Business Suite R12 Architecture

Oracle E-Business Suite R12 Architecture


Publicado em ORACLE EBS, VIDEOS | Deixe um comentário

Downloads Microsoft

Curso completo para certificação MCTS Windows Server
Download – Guia exame 70-647 Windows Server Enterprise Administration


Curso completo para certificação MCTS Windows Server 
(Agradecimentos ao amigo Saulo Seity)

Download – Guia exame 70-643 Configuring Windows Sever 2008 Applications Infrastructure





Curso completo para certificação MCTS Windows Server 
(Agradecimentos ao amigo Saulo Seity)
Download – Guia exame 70-642 Configuring Windows Server 2008 Network Infrastructure  




Curso completo para certificação MCTS Windows Server
(Excelente material disponibilizado pelo nosso amigo Saulo Seyti – Perfil Linkedin)
Download – Guia exame 70-640 Configuring Windows Server 2008 Active Directory

Curso Completo – Profissional 5 Estrelas Windows Server 2008 – Módulo 1
Download – Guia dos Revisores Windows Server LongHorn
Download – Vídeo Visão Técnica Parte 1 (Duração aprox. 35min)
Download – Vídeo Visão Tecnica Parte 2 (Duração aprox. 49min)

Curso Completo – Profissional 5 Estrelas Windows Server 2008 – Módulo 2

Download – Gerenciamento de Papéis de Servidor Seção 1
Download – Server Core Seção 2
Download – Active Directory AD DS Seção 3
Download – Vídeo Gerenciamento de Papéis de Servidor

Download – Vídeo Windows Server Core
Download – Vídeo Active Diretory AD DS


Curso Completo – Profissional 5 Estrelas Windows Server 2008 – Módulo 3
Download – Tecnologia de Escritórios Remotos
Download – A Nova Geração de Protocolo TCP/IP
Download – Terminal Services
Download – Windows Deployment Services
Download – Network Access Protection NAP
Download – Vídeo Melhores Práticas para escritórios Remotos
Download – Vídeo Nova Geração de Protocolo TCP/IP
Download – Vídeo Terminal Services
Download – Vídeo Windows Deployment Services
Download – Vídeo Network Access Protection

Curso Completo – Profissional 5 Estrelas Windows Server 2008 – Módulo 4
Download – Políticas de Grupo
Download – Interoperabilidade Unix X Windows
Download – Virtualização

Download – Vídeo Políticas de Grupo
Download – Vídeo Interoperabilidade Unix X Windows
Download – Vídeo Virtualização

Curso Completo – Profissional 5 Estrelas Windows Server 2008 – Módulo 5
Download – Vídeo IIS 7.0 Visão Geral Técnica para Profissionais de TI
Download – Vídeo Utilizando os Novos Recursos de Delegação e Configuração do IIS7.0
Download – Vídeo Técnicas de Diagnótico e Tracing para Requisições WEB


Download – Resumão Exame 70-642 Windows Server 2008 Network Infrastructure Configuring

Download – Resumão Exame 70-640 Windows Server 2008

Download – Roadmap de Certificações Microsoft

Download – Plano de Capacitação e Treinamento para Profissionais de TI

Download – Curso Certificação Digital
Cursos on-line Intel
Acesse: http://www.nextgenerationcenter.com/home.aspx

Curso Avançado de Segurança da Informação – Academia Latino Americana de Segurança da Informação
Download Advanced Módulo 1 – Aspectos teóricos e práticos para implantação da Norma ABNT NBR ISO/IEC 17799:2005
Download Advanced Módulo 2 – Aspectos teóricos e práticos da Norma NBR ISO/IEC 17799:2005
Download Advanced Módulo 3 – Aspectos teóricos e práticos para implantação da Norma ABNT NBR ISO/IEC 17799:2005
Download Advanced Módulo 4 – Aspectos teóricos e práticos para implantação da Norma ABNT NBR ISO/IEC 17799:2005

Curso Básico de Segurança da Informação – Academia Latino Americana de Segurança de Informação
Download Essentials Módulo 1 – Introdução a Segurança da Informação
Download Essentials Módulo 2 – Conceitos de Análise de Risco
Download Essentials Módulo 3 – A Política de Segurança 
Download Essentials Módulo 4 – Implantação da Segurança e Plano de Segurança

Download – Governança Corporativa
Cursos on-line Intel
Acesse: http://www.nextgenerationcenter.com/home.aspx

Kit de estudos para certificação CCNA
Download – 1os Passos em Roteadores Cisco
Download – Guia de Estudos CCNA (Inglês)
Download – Guia de comandos Cisco
Download – Cisco Router Guide
Download – GNS3 – Simulador de configuração 1
Download – Simulador Cisco
Download – CCNA3 Simulador
Download – Cisco Networking Academy Data sheet

Donwload – DNS Windows Server 2003 – Manual completo para implementação de serviço Domain Name System



Download – Guia Windows Server 2008  – Manual descreve todas funções e recursos deste poderoso Sistema Operacional.

Download – Windows Server 2008 R2 – Introdução a novas funcionalidades e recursos do Windows Server 2008 R2.

Download – Windows Server 2008 Security – Guia para configurações de segurança no Windows Server 2008.

Download – Windows Intune – Guia do Produto – Apresentação do Windows Intune e seus recursos.

Publicado em DOWNLOADS | Marcado com , , , | Deixe um comentário

Usando DBMS_ADVANCED_REWRITE com HINT para alterar o plano de execução

Using DBMS_ADVANCED_REWRITE with an HINT to change the execution plan

In one of my earlier posts, I had illustrated a use case of DBMS_ADVANCED_REWRITE and its use in cases where we cannot change the code, but can still influence and change the way the optimizer executes the same SQL statement.

In case of many vendor provided and packaged applications, we do not have access to the SQL code and we cannot rewrite the SQL statements.

So there could be cases where even though indexes are present on the table, they are not being used by the optimizer and one way we can force the optimizer to use an index is via the INDEX hint.

So how we change the execution plan of the optimizer for a particular statement without changing the original SQL statement that the application is executing?

We do it using the powerful new feature introduced in 10g called DBMS_ADVANCED_REWRITE or “tuning without touching the code”.

To illustrate this, let us create a small table called MYOBJECTS which is based on DBA_OBJECTS and I have made the data skewed on purpose where in the 55000 row table, 1000 rows have the OWNER column with the value ‘PUBLIC’ and the remaining 54000 rows have the value ‘GAVIN’ for the OWNER column.

So the CBO will choose a FULL TABLE SCAN over the INDEX scan when the WHERE clause includes the predicate ‘GAVIN’ because majority of the rows are being accessed by the query and the CBO considers it more optimal in that case to just scan the table rather than both the table and the index.

SQL> create table myobjects as select * from dba_objects;

Table created.

SQL> update myobjects set owner='GAVIN';

56575 rows updated.

SQL> update myobjects set owner='PUBLIC' where rownum <1001;

1000 rows updated.

SQL> commit;

Commit complete.

SQL> create index myobjects_ind on myobjects(owner);

Index created.

SQL> explain plan for select object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2581838392

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 52826 |  4849K|   225   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| MYOBJECTS | 52826 |  4849K|   225   (3)| 00:00:03 |
-------------------------------------------------------------------------------

So if we feel that the CBO should be still using an index regardless, we can provide an INDEX hint and we see now the index is being used as opposed to a full table scan.

SQL> explain plan for select /*+ INDEX (myobjects myobjects_ind) */
  2  object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2745750972

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 52826 |  4849K|   916   (1)| 00:00:11 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYOBJECTS     | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  2 |   INDEX RANGE SCAN          | MYOBJECTS_IND | 52826 |       |   134   (3)| 00:00:02 |
---------------------------------------------------------------------------------------------

But now the question is if I cannot change the SQL statement or have access to the code, how do I still enforce the INDEX hint?

DBMS_ADVANCED_REWRITE to the rescue.

Remember SYS needs to grant execute privileges on the DBMS_ADVANCED_REWRITE package to the user who is going to use this.

Since we have created the table as SYSTEM, we connect as SYSTEM and execute this statement shown below.

Basically the two main parameters we are providing here is the SOURCE STATEMENT or the original SQL and the DESTINATION STATEMENT which is the modified piece of SQL we want the optimizer to execute every time it sees the “source or original” statement.

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'Use_Myobjects_Index',
source_stmt =>'select object_name,object_type from myobjects where owner=''PUBLIC''',
destination_stmt => 'select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobjects where owner=''PUBLIC''' ,
validate => false,
rewrite_mode => 'text_match');
end;
/

But since both the source and destination statements are the same in this case (Oracle considers the INDEX hint to be just a comment), we get an error like the one shown below:

ERROR at line 1:
ORA-30394: source statement identical to the destination statement
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

Okay. so now how do we make the second statement differ so that it is recognised as a new statement different from the original one without changing radically the SQL itself?

I looked up various user forums because this problem has been faced by others as well and one of the suggestions given was to add the clause SYSDATE=SYSDATE which I think is a good suggestion as it will not affect the outcome of the SQL statement and should not cause any real performance overhead.

So this is what the new DBMS_ADVANCED_REWRITE statement looked like:

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'Use_Myobjects_Index',
source_stmt =>'select object_name,object_type from myobjects where owner=''GAVIN''',
destination_stmt => 'select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobjects where owner=''GAVIN'' and sysdate=sysdate' ,
validate => false,
rewrite_mode => 'text_match');
end;
/

After executing the piece of PL/SQL, let us now see what has happens when we run our SQL statement which was originally going for a FULL TABLE SCAN.

SQL> explain plan for select object_name,object_type from myobjects where owner='GAVIN';

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 792246183

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  1 |  FILTER                      |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYOBJECTS     | 52826 |  4849K|   916   (1)| 00:00:11 |
|*  3 |    INDEX RANGE SCAN          | MYOBJECTS_IND | 52826 |       |   134   (3)| 00:00:02 |
----------------------------------------------------------------------------------------------

We see that now the CBO is using the index MYOBJECTS_IND and doing an INDEX RANGE SCAN instead of the full table scan and we have actually transformed the SQL statement which the application executes without altering the SQL itself.

We can use the view USER_REWRITE_EQUIVALENCES to check what all query rewrites we have set up in the database.

SQL> set long 500000

SQL> select NAME,SOURCE_STMT,DESTINATION_STMT,REWRITE_MODE from user_rewrite_equivalences;

NAME                           SOURCE_STMT

NAME                           SOURCE_STMT
------------------------------ --------------------------------------------------------------------------------
DESTINATION_STMT                                                                 REWRITE_MO
-------------------------------------------------------------------------------- ----------
USE_MYOBJECTS_INDEX            select object_name,object_type from myobjects where owner='GAVIN'
select /*+ INDEX (myobjects myobjects_ind) */ object_name,object_type from myobj TEXT_MATCH
ects where owner='GAVIN' and sysdate=sysdate
Publicado em ORACLE 11gR2, PLSQL TUNING, TUTORIAL | Marcado com , , , , , | Deixe um comentário

Unix para DBAs

.

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory
find . -print |grep -i test.sql
 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'
Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"
 Display top 10 CPU consumers using the ps command
/usr/ucb/ps auxgw | head -11
 Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
 Display the number of CPU’s in Solaris
psrinfo -v | grep "Status of processor"|wc -l
Display the number of CPU’s in AIX
lsdev -C | grep Process|wc -l
Display RAM Memory size on Solaris
prtconf |grep -i mem
Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0
Swap space allocation and usage
Solaris : swap -s or swap -l
Aix : lsps -a
 Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'
View allocated RAM memory segments
ipcs -pmb
Manually deallocate shared memeory segments
ipcrm -m '<ID>'
 Show mount points for a disk in AIX
lspv -l hdisk13
 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail
Display total file space in a directory
du -ks .
 Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;
 Locate Oracle files that contain certain strings
find . -print | xargs grep rollback
 Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print
 Finding large files on the server (more than 100MB in size)
find . -size +102400 -print
Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Publicado em LINUX, TUTORIAL, UNIX | Marcado com , , , , , | Deixe um comentário

Using a 10g R2 Oracle Physical Standby Database For Read/Write Testing and Reporting

Using a 10g R2 Oracle Physical Standby Database
For Read/Write Testing and Reporting

Introduction....................................................3
Description of the Test.........................................3
First Part: Prepare the environment and activate read/write the standby database...................................................3
Second part: Resynchronize the Physical Standby Database with the Primary Database................................................4
Implementation Step by Step....................................4
1. Check that primary and standby are synchronized.............4
2. Check that standby is applying redo logs....................6
3. Enable flashback database on physical standby...............7
4. Stop redo apply to the standby..............................9
5. Create a restore point on the standby.......................9
6. Archive log current on primary database.....................9
We need to archive the current redo log on the primary database; it will be shipped to the standby server but not applied. This way we will assure that when flashing back and reactivating the standby we will have the archived logs up to the SCN of the restore point.9
7. Defer redo log shipping to the standby.....................10
8. Activate the physical standby database.....................11
9. Startup mount force the standby............................11
10. Set the standby protection mode to maximum performance....12
11. Open the activated standby database.......................12
12. Use the activated standby for reporting/testing...........12
13. Revert activated database to physical standby role........14
14. Startup mount force.......................................15
15. Flashback activated database to restore point.............16
16. Convert activated database to physical standby............16
17. Startup mount force.......................................16
18. Put the standby database on recover mode..................17
19. Enable archive log shipping to the standby................18
20. Check that archive logs are applied to the standby........19
References....................................................21
End of the Procedure..........................................21
Introduction
On 11g we can open a Physical Standby database on read-only mode, while the Standby continues to be updated. This is a major break through on the High Availability field.
On 10g R2 we can take advantage of 10g Flashback Database together with a Physical Standby to be able to open the Standby Database on read-write mode, perform testing / reporting activities and, once these activities are completed we can reinstate the standby and synchronize it with the primary database.
This document is a step by step implementation test of this technology and is based on
The Oracle Data Guard Concepts and Administration, 10g Release 2 guide, Chapter 12, Data Guard Scenarios.
Description of the Test
This test is implemented using 2 Linux servers, and Oracle 10g R2 EE.
• Primary database ‘dgedb’ is located on server rac1.
• Physical standby database ‘dgfdb’ is located on server rac2
We will activate and open the standby read – write, and after simulating some activity, we will reinstate it as physical standby.
First Part: Prepare the environment and activate read/write the standby database.
1. Check that primary and standby are synchronized
2. Check that standby is applying redo logs
3. Enable flashback database on physical standby
4. Stop redo apply to the standby
5. Create a restore point on the standby.
6. Archive log current on primary database
7. Defer redo log shipping to the standby.
8. Activate the physical standby database.
9. Startup mount force the standby.
10. Set the standby protection mode to maximum performance.
11. Open the activated standby database
12. Use the activated standby for reporting/testing
Second part: Resynchronize the Physical Standby Database with the Primary Database
13. Revert activated database to physical standby role
14. Startup mount force
15. Flashback activated database to restore point
16. Convert activated database to physical standby
17. Startup mount force
18. Put the standby database on recover mode.
19. Enable archive log shipping to the standby
20. Check that archive logs are applied to the standby
End of the procedure
Implementation Step by Step
1. Check that primary and standby are synchronized
On this step we check that primary and standby are synchronized, by checking the current log sequence, using the archive log list command.
PRIMARY
ORACLE_SID=dgedb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 ORA_CRS_HOME=/u01/app/oracle/product/10.2.0/crs_1
SQL> archive log list
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/dgedb/archive/ Oldest online log sequence 27 Next log sequence to archive 29 Current log sequence 29
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE ---------------- ---------- PRIMARY READ WRITE
PHYSICAL STANDBY
ORACLE_SID=dgfdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_CRS_HOME=/u01/app/oracle/product/10.2.0/crs_1
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgfdb/archive/
Oldest online log sequence 29
Next log sequence to archive 0
Current log sequence 29
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PHYSICAL STANDBY MOUNTED
2. Check that standby is applying redo logs
On this step we will make switch logfile on the primary and will check how the sequence is advanced also on the physical standby
PRIMARY
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgedb/archive/
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
PHYSICAL STANDBY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgfdb/archive/
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 33
3. Enable flashback database on physical standby
On this step we will set up a flash recovery area, flashback log will be written to the db_recovery_file_dest, they will enable the possibility to flashback the database to the point in time we will set in order to re-activate the physical standby once the test is finished.
SQL> alter system set db_recovery_file_dest_size=2g scope=both; System altered. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flashback' scope=both; System altered. SQL> show parameters db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flashback db_recovery_file_dest_size big integer 2G
To enable flashback database the database needs to be shutdown cleanly, after that start up mount and enable flashback.
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
Database altered.
4. Stop redo apply to the standby
On the physical standby database we need to stop Redo Apply.
SQL> alter database recover managed standby database cancel;
Database altered.
5. Create a restore point on the standby
A guaranteed restore point will make possible for us to flashback the database to this point in time, once our read write activities on its activated version will finish.
SQL> create restore point Before_App_Test guarantee flashback database;
Restore point created.
6. Archive log current on primary database
We need to archive the current redo log on the primary database; it will be shipped to the standby server but not applied. This way we will assure that when flashing back and reactivating the standby we will have the archived logs up to the SCN of the restore point.
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgedb/archive/
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
7. Defer redo log shipping to the standby.
This step is performed on the Primary Database; we defer redo log shipping to the standby while it will be activated read write.
First we check the actual values for the standby archive log destination
SQL> show parameters log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
SQL> show parameters log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=dgfdb LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dgfdb
Once we are sure which is the destination to defer we can execute the defer command
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
System altered.
SQL> show parameters log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
8. Activate the physical standby database.
At this moment the physical standby has stopped redo apply, we have enable flashback database and we have created a restore point. We are ready to activate the database and open it for read/write operations.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
9. Startup mount force the standby
SQL> STARTUP MOUNT FORCE;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1260984 bytes
Variable Size 134218312 bytes
Database Buffers 79691776 bytes
Redo Buffers 2932736 bytes
Database mounted.
10. Set the standby protection mode to maximum performance
We need to downgrade the protection mode to maximum performance because this mode permits to work without a standby database protecting the activated standby once opened.
When later we will flash back to the restore point, the physical standby automatically will get the protection mode defined on the primary.
SQL> alter database set standby database to maximize performance;
Database altered.
11. Open the activated standby database
SQL> ALTER DATABASE OPEN;
Database altered.
12. Use the activated standby for reporting/testing
Now we have the standby database activated. It is a block by block 100% copy of our production environment we can use it to run reports, to do applications testing, i.e. implementing a new version of our applications and testing it, perform critical changes to the structure, if required on production and test the impact, etc.
NOTE: While the standby is open read write do not provide protection to the Primary. You may have a second standby for this purpose.
In this test I’m executing some read / write operations to mimic some reporting activity
SQL> connect avargas/oracle
Connected.
SQL> create table myusers as select * from dba_users;
Table created.
SQL> insert into myusers select * from myusers;
22 rows created.
SQL> /
44 rows created.
SQL> /
88 rows created.
SQL> /
176 rows created.
SQL> /
352 rows created.
SQL> commit;
Commit complete.
SQL> select username from myusers where username like 'AVAR%';
USERNAME
------------------------------
AVARGAS
…
…
…
AVARGAS
AVARGAS
32 rows selected.
13. Revert activated database to physical standby role
On this step we will revert the activated database to its standby role, note that all information stored during the time the database was open read/write will be lost. You may want to make a backup if you need this information for later use.
3 steps are required to revert to the standby role:
• Startup mount force
• Flashback database
• Convert to physical standby
14. Startup mount force
Note the log sequences we have on the standby, we are as a matter of fact on a new incarnation
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1260984 bytes
Variable Size 142606920 bytes
Database Buffers 71303168 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgfdb/archive/
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
15. Flashback activated database to restore point
On this step we are using the restore point Before_App_Test we created on step 5. We may have used also the SCN or a point in time.
SQL> flashback database to restore point before_app_test;
flashback complete.
16. Convert activated database to physical standby
SQL> alter database convert to physical standby;
Database altered.
17. Startup mount force
After having converted the database to the physical standby role we need to restart the database, notice that still at this point the sequences are after the restore point
SQL> STARTUP MOUNT FORCE;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1260984 bytes
Variable Size 150995528 bytes
Database Buffers 62914560 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgfdb/archive/
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
18. Put the standby database on recover mode.
When enabling recovery the standby database will start to apply archived logs from the primary that will take a minute until redo shipping is enabled on the primary.
SQL> alter database recover managed standby database disconnect;
Database altered.
On the alert log of the standby database we can see that the online redo logs are cleared from the sequences they were working on while opened read/write
Thu Dec 27 17:30:17 2007 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT Thu Dec 27 17:30:17 2007 Attempt to start background Managed Standby Recovery process (dgfdb) MRP0 started with pid=28, OS id=27288
Thu Dec 27 17:30:18 2007 MRP0: Background Managed Standby Recovery process started (dgfdb) Managed Standby Recovery not using Real Time Apply parallel recovery started with 2 processes Clearing online redo logfile 1 /u01/app/oracle/oradata/dgfdb/redo01.log Clearing online log 1 of thread 1 sequence number 2 Thu Dec 27 17:30:24 2007 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT Thu Dec 27 17:30:27 2007 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u01/app/oracle/oradata/dgfdb/redo02.log Clearing online log 2 of thread 1 sequence number 1 Clearing online redo logfile 2 complete Media Recovery Waiting for thread 1 sequence 35
19. Enable archive log shipping to the standby
On the primary database we enable log shipping to the standby, when the stream of changes get to the standby its online logs will be cleared from the sequences stored on them when it was activated and open on read write mode
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PRIMARY READ WRITE
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgedb/archive/
Oldest online log sequence 35
Next log sequence to archive 37
Current log sequence 37
20. Check that archive logs are applied to the standby
On step 19, after enabling redo shipping to the standby we did a switch logfile, now we can check if the standby is in sync with the primary, the current log sequence needs to be 37.
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PHYSICAL STANDBY MOUNTED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/dgfdb/archive/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 37
On the standby alert.log we can see the Remote File Server (RFS) process executing the apply process up to the last sequence
Thu Dec 27 17:34:28 2007 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/oradata/dgfdb/archive/ Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 1352 RFS[1]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Thu Dec 27 17:34:28 2007 RFS LogMiner: Client disabled from further notification Primary database is in MAXIMUM PERFORMANCE mode RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/oradata/dgfdb/SRL01.log' Thu Dec 27 17:34:32 2007 Fetching gap sequence in thread 1, gap sequence 35-35 Thu Dec 27 17:34:33 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[2]: Assigned to RFS process 1486 RFS[2]: Identified database type as 'physical standby' RFS[2]: Archived Log: '/u01/app/oracle/oradata/dgfdb/archive/1_35_633452428.dbf'
Thu Dec 27 17:34:39 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[3]: Assigned to RFS process 1686 RFS[3]: Identified database type as 'physical standby' Thu Dec 27 17:34:39 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[4]: Assigned to RFS process 1688 RFS[4]: Identified database type as 'physical standby' RFS[4]: Archived Log: '/u01/app/oracle/oradata/dgfdb/archive/1_36_633452428.dbf' Thu Dec 27 17:35:03 2007 Media Recovery Log /u01/app/oracle/oradata/dgfdb/archive/1_35_633452428.dbf Media Recovery Waiting for thread 1 sequence 37 (in transit)
References
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)
Part Number B14239-04
Chapter 12 Data Guard Scenarios
Chapter 12.6 Using a Physical Standby Database for Read/Write Testing and Reporting
End of the Procedure
Publicado em DATAGUARD | Deixe um comentário

Dataguard Metalink notes

734862.1 Step By Step Guide On How To Recreate Standby Control File When
Datafiles Are On ASM And Using Oracle Managed Files
749947.1 Enabling Encryption for Data Guard Redo Transport using the Advanced
Security Option
751600.1 Data Guard Physical Standby Switchover/Failover Detailed Best
Practices, 10.2.0.1 - 10.2.0.4 - Data Guard Broker, EM, or SQL*Plus
729551.1 Redo Transport Compression in a Data Guard Environment, 11.1.0.6 -
11.1.0.7
751528.1 Oracle Data Guard and SSH
754065.1 Installing Database Vault in a Data Guard Environment, 10.2.0.3 -
11.1.0.7
737460.1 Changing Storage Definition in a Logical Standby Database
565535.1 Flashback Database Best Practices & Performance
559353.1 SQL Apply and Extended Datatype Support
756732.1 Potentially Longer Instance Recovery Timings in a MAA Configuration
When Using Logical Standby
603361.1 Developer and DBA Tips for Pro-Actively Optimizing SQL Apply
454848.1 Installing and Using Standby Statspack in 11gR1
434164.1 Data Guard Redo Log Repository Example
416314.1 Reinstating a Logical Standby Using Backups Instead of Flashback
Database
416310.1 Reinstating a Physical Standby Using Backups Instead of Flashback
413696.1 Data Guard Broker does not support Cold Failover Clusters
413484.1 Data Guard Support for Heterogeneous Primary and Standby Systems in
Same Data Guard Configuration
414043.1 Role Transitions for Data Guard Configurations Using Mixed Oracle
Binaries
409013.1 Cascaded Standby Databases
395982.1 Data Guard Support for Mixed HP PA-RISC 64-bit and HP Itanium
Environments
370434.1 How to make CRS aware of the role change in Data Guard environment?
368276.1 Steps to workaround issue described in Alert 308698.1
364290.1 Global Customer Service Escalation Process
356163.1 Oracle Data Guard Readme for SQL Apply Release 9.2.0.7
341437.1 Business Continuity for Oracle Applications 11i Using RAC and Physical
Standby
340859.1 Upgrading Oracle Applications 11i 9i Database to Oracle Database 10g
with Physical Standby in Place
331924.1 RMAN backups in Max Performance/Max Availability Data Guard Environment
312434.1 Oracle10g Data Guard SQL Apply Troubleshooting
305360.1 Data Guard and Oracle Standard Edition
304488.1 Using standby_file_management with Raw Devices
304061.1 Oracle Data Guard Readme for SQL Apply Release 10.1.0.4
304059.1 Oracle Data Guard Readme for SQL Apply Release 10.1.0.3
303421.1 Creating a Logical Standby with a Different Block Size Than Primary
300479.1 Rolling Upgrades with Logical Standby
290817.1 Rolling a Standby Forward using an RMAN Incremental Backup in 9i
290814.1 Rolling a Standby Forward using an RMAN Incremental Backup in 10g
290161.1 The Gains and Pains of Nologging Operations
286787.1 Oracle Data Guard Readme for SQL Apply Release 9.2.0.6
285358.1 Creating a Logical Standby from a RAC Primary Using a Hot Backup
278643.1 Applying Patchset with a 10g Logical Standby in Place
278641.1 Applying Patchset with a 10g Physical Standby in Place
278521.1 Upgrading to 10g with a Physical Standby in Place
278371.1 Creating a Logical Standby with Minimal Production Downtime
278108.1 Upgrading to 10g with a Logical Standby in Place
276636.1 Data Guard GUI ASM restrictions
275416.1 Data Guard Monitoring Events for a 9i MAA environment
273177.1 Removing Archived Redo Log Files No Longer Needed By SQL Apply
273015.1 Migrating to RAC using Data Guard
271511.1 Readme for SQL Apply - Release 9.2.0.5
271463.1 Handling Problematic DDL with Data Guard SQL Apply
271455.1 Synchronizing tables in a Logical Standby Database
271448.1 Oracle Data Guard Switchover & Failover Best Practices
269954.1 Script to Collect Data Guard Logical Standby Table Information
266086.1 Unique Constraint Violations Stop SQL Apply
260040.1 Refining Remote Archival Over a Slow Network with the ARCH Process
259804.1 Release s for Data Guard in Oracle9i Release 2
257343.1 Data Guard Manager and Firewalls
257341.1 Example Usage of the DBMS_LOGSTDBY Package
255959.1 Data Guard and Network Disconnects
255958.1 Determining if SQL Apply Engine is Performing Full Table Scans
248460.1 Creating a Data Guard Logical Standby Configuration from a RAC Primary
in Oracle 9i
247129.1 ORA-16166 LGWR timed out on Network Server 0 due to buffer full
condition
243709.1 Monitoring Physical Standby Progress
241925.1 Troubleshooting 9i Data Guard Network Issues
241512.1 Script to Collect Data Guard Logical Standby Diagnostic Information
241438.1 Script to Collect Data Guard Physical Standby Diagnostic Information
241374.1 Script to Collect Data Guard Primary Site Diagnostic Information
240875.1 Oracle 9i Media Recovery Best Practices
240874.1 9i Data Guard Primary Site and Network Configuration Best Practices
239100.1 Data Guard Protection Modes Explained
234631.1 Creating a Logical Standby from a Hot Backup
233519.1 Known Issues with Logical Standby
233491.1 Data Guard Wait Events
233261.1 Tuning SQL Apply Operations for Logical Standby
232649.1 Data Guard Gap Detection and Resolution
232240.1 Performing Switchover in a Data Guard Configuration
227196.1 Failover in Oracle9i DataGuard Environments
223556.1 Upgrading the version of perl used by Data Guard Manager
219344.1 Data Guard 9i: Usage, Benefits and Limitations of Standby RedoLogs
215020.1 Troubleshooting Logical Standby
214071.1 Creating a configuration using Data Guard Manager
213164.1 Data Guard 9i "The following transaction makes no progress message" in
the Alert log
211555.1 Data Guard 8i SSH Verification Script
210989.1 Data Guard 9i Applying Patch Sets with Logical Standby Database in
place
205637.1 Data Guard 9i Configuring Transparent Application Failover in a Data
Guard Environment
204848.1 Data Guard 9i Data Guard Remote Process Startup Failed
204683.1 Oracle Enterprise Manager Readme - Release 9.2
203326.1 Data Guard 9i Log Transportation on RAC
188691.1 How to Avoid Generation of Redolog Entries
187242.1 Applying Patch Sets with Physical Standby Database in place
186150.1 Data Guard 9i Creating a Logical Standby Database
180031.1 Creating a Data Guard Configuration
Publicado em DATAGUARD | Deixe um comentário

11g Release 2 Rolling Upgrade using Transient Logical Standby database

11g Release 2 Rolling Upgrade using Transient Logical Standby database

This note illustrates how we can perform a rolling upgrade from Oracle 11g Release 1 to Oracle 11g Release 2 using a Transient Logical Standby database. This approach will miminise the downtime required for an upgrade which can potentially run into several hours down to just the time required to perform a switchover which could be a few minutes in most cases.

A rolling upgrade using a Transient Logical Standby database at a very high level will involve three main stages or steps:

1) Temporarily convert a physical standby database to a logical standby database using the new KEEP IDENTITY clause
2) Perform a database upgrade of the logical standby database
3) Return the logical standby database back to its identity or original status as a physical standby database once the upgrade is complete

The assumption here is that ….

1)We already have configured a Physical Standby Database using Data Guard best practices and both Primary and Standby databases are in sync and Redo Transport and Redo Apply are working properly.

2)Data Guard Broker if configured is disabled

3) Standby database is operating in Maximum Availability or Maximum Protection mode

4) Flashback Database is enabled

Steps

Create a Guaranteed Restore Point on original Primary

SQL> create restore point pre_upgrade_1 guarantee flashback database;

Restore point created.

Create a guaranteed restore point on original Standby database

We also create a restore point on the standby database in case we encounter any errors while upgrading the database and we would like to have a fallback in place.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>  create restore point pre_upgrade_2 guarantee flashback database;

Restore point created.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

Create the Log Miner dictionary on the original primary database

 

SQL> exec dbms_logstdby.build;

PL/SQL procedure successfully completed.

Convert the original physical standby database to logical standby database

We will use the new 11g KEEP IDENTITY clause which ensures that the logical standby database keeps its DBID which is identical to the DBID of the original primary database.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  526131200 bytes
Fixed Size                  2139784 bytes
Variable Size             209717624 bytes
Database Buffers          306184192 bytes
Redo Buffers                8089600 bytes
Database mounted.
SQL> alter database recover to logical standby keep identity;

Database altered.

SQL> alter database open;

Database altered.

Start SQL Apply on the new logical standby database and monitor the dictionary build

At this point now, the logical standby database will be receiving the necessary redo information from the original primary database to populate its own Log Miner dictionary. We can monitor the dictionary build by querying the view V$LOGSTDBY_STATE.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> /

STATE
----------------------------------------------------------------
APPLYING

SQL> /

STATE
----------------------------------------------------------------
IDLE

Prepare the Logical Standby database for upgrade

We need to stop the SQL Apply and also create another restore point on the logical standby as a fallback measure.

On Original Primary

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

System altered.

On Logical Standby

SQL> alter database stop logical standby apply;

Database altered.

SQL> create restore point pre_upgrade_3 guarantee flashback database;

Restore point created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Peform the upgrade of the Logical Standby database to 11g Release 2 using DBUA or by using the manual steps.

While the upgrade is in progress, users are still connected to the original 11g Release 1 primary database. Let us make some DDL as well as DML changes on this database.

SQL> conn sh/sh
Connected.
SQL> update customers set cust_city='Perth';

55500 rows updated.

SQL> commit;

Commit complete.

SQL> create table mycustomers as select * from customers;

Table created.

SQL> update mycustomers set cust_city='New York';

55500 rows updated.

SQL> commit;

Commit complete.

After the upgrade is complete, we will now start SQL Apply on the upgraded Logical Standby database

 

On Origial Primary

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

System altered.

On Upgraded Logical Standby database

SQL> alter database start logical standby apply immediate;

Perform a Switchover to the Upgraded 11g Release 2 standby database

On Original Primary

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to logical standby;

Database altered.

On upgraded Logical Standby database

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>  alter database commit to switchover to logical primary;

Database altered.

Perform some checks

While the logical standby database was being upgraded to 11g Release 2, users were connected to the original 11g Release 1 primary database. Let us see if those changes we made have been propagated to the standby site – which now after the switchover has become the new Primary database.

SQL> conn sh/sh
Connected.
SQL> select distinct cust_city from customers;

CUST_CITY
------------------------------
Perth

SQL> select distinct cust_city from mycustomers;

CUST_CITY
------------------------------
New York

Make some changes on the upgraded 11g Release 2 database

SQL> conn sh/sh
Connected.
SQL> update mycustomers set cust_city='Tokyo';

55500 rows updated.

SQL> update customers set cust_city='Hong Kong';

55500 rows updated.

SQL> commit;

Commit complete.

Retransformation into Physical Standby database

The former primary database is running at a lower Oracle version (11g Release 1) as a transient logical standby database. It cannot receive and apply any redo from the new primary database until it has been converted back into a physical standby database.

On New Primary (11g Release 2)

SQL> conn / as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=defer scope=memory;

On New Logical Standy database (11g Release 1)

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  526131200 bytes
Fixed Size                  2139784 bytes
Variable Size             260049272 bytes
Database Buffers          255852544 bytes
Redo Buffers                8089600 bytes
Database mounted.

SQL> flashback database to restore point pre_upgrade_1;

Flashback complete.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Start the new logical standby database in the 11g Release 2 Oracle Home and convert it to a physical standby database.

We need to do some prep work before we can start the instance in the 11g Release 2 Oracle home. Copy the password file and the init.ora file from the 11g R1 locations to the 11g R2 locations.

dba1:/u01/oracle/product/11.2/dbs> cp /u02/oradata/product/dbs/orapwdba1 .

dba1:/u01/oracle/product/11.2/dbs> cp /u02/oradata/product/dbs/spfiledba1.ora .

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.

At this stage we will get an error if we try and open the database as the database is of a lower version and has not been upgraded as yet.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 393392
Session ID: 191 Serial number: 3
  • Start Redo Apply on the converted physical database
On New Primary

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

System altered.

On New Physical Standby database

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

Monitor Redo Apply

When the redo apply starts, it will register a new incarnation received from the primary database. Until that happens, we will see from the alert log that the Redo Apply loops every ten seconds or so waiting for the incarnation to be registered.

Fri Mar 19 10:38:51 2010
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery waiting for new incarnation during transient logical upgrade procedure
Errors in file /u01/ofsad2/diag/rdbms/dba1_devu026/dba1/trace/dba1_pr00_368854.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-19906 exception
Errors in file /u01/ofsad2/diag/rdbms/dba1_devu026/dba1/trace/dba1_pr00_368854.trc:
ORA-19906: recovery target incarnation changed during recovery

Then we will see …..

RFS[1]: Identified database type as 'physical standby': Client is LGWR SYNC pid 1134740
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[1]: Selected log 4 for thread 1 sequence 111 dbid 2023850869 branch 713959355
Fri Mar 19 10:39:12 2010
RFS[2]: Assigned to RFS process 1736934
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 1990894
RFS[2]: Selected log 5 for thread 1 sequence 110 dbid 2023850869 branch 713959355
RFS[2]: New Archival REDO Branch(resetlogs_id): 713959355  Prior: 713439030
RFS[2]: Archival Activation ID: 0x78aa01e2 Current: 0x0
RFS[2]: Effect of primary database OPEN RESETLOGS
RFS[2]: Managed Standby Recovery process is active
RFS[2]: Incarnation entry added for Branch(resetlogs_id): 713959355 (dba1)
Fri Mar 19 10:39:13 2010
Setting recovery target incarnation to 3

Then we will see the the media recovery in progress. This will apply all the redo which contains the changes related to the upgrade of the database to 11g Release 2, so that once the media recovery is complete, we will see that the physical standby database has indeed been upgraded to 11g Release 2.

Fri Mar 19 11:01:06 2010
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves

RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 1990894
Fri Mar 19 11:01:22 2010
RFS[4]: Assigned to RFS process 1835206
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 794694
RFS[3]: Opened log for thread 1 sequence 7 dbid 2023850869 branch 713959355
RFS[4]: Opened log for thread 1 sequence 8 dbid 2023850869 branch 713959355
Archived Log entry 79 added for thread 1 sequence 7 rlc 713959355 ID 0x78aa01e2 dest 2:
Archived Log entry 80 added for thread 1 sequence 8 rlc 713959355 ID 0x78aa01e2 dest 2:
......
......
RFS[44]: Assigned to RFS process 561242
RFS[44]: Identified database type as 'physical standby': Client is ARCH pid 794694
RFS[44]: Opened log for thread 1 sequence 99 dbid 2023850869 branch 713959355
Archived Log entry 119 added for thread 1 sequence 99 rlc 713959355 ID 0x78aa01e2 dest 2:
Archived Log entry 120 added for thread 1 sequence 97 rlc 713959355 ID 0x78aa01e2 dest 2:
RFS[44]: Opened log for thread 1 sequence 100 dbid 2023850869 branch 713959355
Archived Log entry 121 added for thread 1 sequence 100 rlc 713959355 ID 0x78aa01e2 dest 2:
......
......
Media Recovery Log /u02/oradata/dba1/arch/arch1_10_713959355.log
Fri Mar 19 11:42:53 2010
Media Recovery Log /u02/oradata/dba1/arch/arch1_11_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_12_713959355.log
Fri Mar 19 11:43:09 2010
Media Recovery Log /u02/oradata/dba1/arch/arch1_13_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_14_713959355.log
......
......
Media Recovery Log /u02/oradata/dba1/arch/arch1_24_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_25_713959355.log
Fri Mar 19 11:45:02 2010
Media Recovery Log /u02/oradata/dba1/arch/arch1_26_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_27_713959355.log
Fri Mar 19 11:45:19 2010
Media Recovery Log /u02/oradata/dba1/arch/arch1_28_713959355.log
.....
.....
Media Recovery Log /u02/oradata/dba1/arch/arch1_104_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_105_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_106_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_107_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_108_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_109_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_110_713959355.log
Fri Mar 19 13:19:50 2010
Media Recovery Log /u02/oradata/dba1/arch/arch1_111_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_112_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_113_713959355.log
Media Recovery Log /u02/oradata/dba1/arch/arch1_114_713959355.log
Media Recovery Waiting for thread 1 sequence 115 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 115 Reading mem 0
  Mem# 0: /u02/oradata/dba1/flash_recovery_area/DBA1/onlinelog/o1_mf_6_5sm961z3_.log

SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           03-19-2010 13:28:43
.
Component                                Status         Version  HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.1.0  00:44:46
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.1.0  00:10:56
Oracle Workspace Manager
.                                         VALID      11.2.0.1.0  00:01:28
OLAP Analytic Workspace
.                                         VALID      11.2.0.1.0  00:01:18
OLAP Catalog
.                                         VALID      11.2.0.1.0  00:02:36
Oracle OLAP API
.                                         VALID      11.2.0.1.0  00:01:28
Oracle Enterprise Manager
.                                         VALID      11.2.0.1.0  00:38:10
Oracle XDK
.                                         VALID      11.2.0.1.0  00:01:31
Oracle Text
.                                         VALID      11.2.0.1.0  00:01:22
Oracle XML Database
.                                         VALID      11.2.0.1.0  00:06:22
Oracle Database Java Packages
.                                         VALID      11.2.0.1.0  00:01:13
Oracle Multimedia
.                                         VALID      11.2.0.1.0  00:15:21
Spatial
.                                         VALID      11.2.0.1.0  00:09:56
Oracle Expression Filter
.                                         VALID      11.2.0.1.0  00:00:31
Oracle Rules Manager
.                                         VALID      11.2.0.1.0  00:00:24
Oracle Application Express
.                                         VALID     3.2.1.00.10  00:26:39
Gathering Statistics
.                                                                00:17:09
Total Upgrade Time: 03:02:12

Switchback Steps

We will now perform a second switchover (note, this step is optional if we wish to continue running the database from the original standby site). This will return the new primary database back to its original role as the physical standby database and the new standby database back to its original role of Primary database.

On new Standby 

SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
Database opened.

On current Primary

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to standby with session shutdown;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
Database opened.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

	Test all changes made from 11g Release 2 database before switchback have been propagated
SQL> conn sh/sh
Connected.
SQL> select distinct cust_city from customers;

CUST_CITY
------------------------------
Hong Kong

SQL> select distinct cust_city from mycustomers;

CUST_CITY
------------------------------
Tokyo
Publicado em DATAGUARD, ORACLE 11gR2, RMAN, TUTORIAL | Marcado com , , , , | Deixe um comentário

11g Data Guard Broker DGMGRL

11g Data Guard Broker DGMGRL Configuration quick steps

This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control GUI as well and the assumption is that there is a Physical Standby database in place and redo transport and redo apply have already been configured and that both the Primary and Standby database are in sync.

Primary Database: genoa1_js

Standby Database: genoa1_fc

On both Primary as well as Standby database start the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Edit the listener.ora on both nodes to add a static entry for DGMGRL

This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER =
  (SID_LIST =
 (SID_DESC =
        (GLOBAL_DBNAME = genoa1_js_dgmgrl)
        (ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)
        (SID_NAME = genoa1)
        )
)

Create the configuration

genoa1:/u01/oracle> dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx
Connected.
DGMGRL> CREATE CONFIGURATION 'GenesisDR'
> AS
> PRIMARY DATABASE IS 'genoa1_js'
> CONNECT IDENTIFIER IS 'genoa1_js'
> ;
Configuration "GenesisDR" created with primary database "genoa1_js"

Add the Standby database to the configuration

DGMGRL>  ADD DATABASE
> 'genoa1_fc'
> AS
> CONNECT IDENTIFIER IS 'genoa1_fc'
> ;
Database "genoa1_fc" added

DGMGRL> SHOW CONFIGURATION

Configuration
  Name:                GenesisDR
  Enabled:             NO
  Protection Mode:     MaxAvailability
  Databases:
    genoa1_js - Primary database
    genoa1_fc - Physical standby database

Fast-Start Failover: DISABLED

Current status for "GenesisDR":
DISABLED

Enable the configuration

DGMGRL> ENABLE CONFIGURATION
Enabled.

DGMGRL> SHOW CONFIGURATION

Configuration
  Name:                GenesisDR
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    genoa1_js - Primary database
    genoa1_fc - Physical standby database

Fast-Start Failover: DISABLED

Current status for "GenesisDR":
SUCCESS

View the Standby and Primary database properties

DGMGRL> show database genoa1_js

Database
  Name:            genoa1_js
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  TRANSPORT-ON
  Instance(s):
    genoa1

Current status for "genoa1_js":
SUCCESS

DGMGRL>  show database genoa1_fc

Database
  Name:            genoa1_fc
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    genoa1

Current status for "genoa1_fc":
SUCCESS

DGMGRL> show database verbose genoa1_fc

Database
  Name:            genoa1_fc
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    genoa1

  Properties:
    DGConnectIdentifier             = 'genoa1_fc'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'drou037'
    SidName                         = 'genoa1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=drou037)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=genoa1_fc_DGMGRL)(INSTANCE_NAME=genoa1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "genoa1_fc":
SUCCESS

Change the properties of a configured databaase

DGMGRL> EDIT DATABASE 'genoa1_js' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

Performing a switchover

Note: In this case, currently the Primary Database is genoa1_fc and the Standby database is genoa1_js.

DGMGRL> switchover to 'genoa1_js'
Performing switchover NOW, please wait...
New primary database "genoa1_js" is opening...
Operation requires shutdown of instance "genoa1" on database "genoa1_fc"
Shutting down instance "genoa1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "genoa1" on database "genoa1_fc"
Starting instance "genoa1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "genoa1_js"

DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                GenesisDR
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    genoa1_js - Primary database
    genoa1_fc - Physical standby database

Fast-Start Failover: DISABLED

Current status for "GenesisDR":
SUCCESS

Monitoring the Data Guard Broker Configuration

If we receive any error or warnings we cab obtain more information about the same ny running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.

DGMGRL> show database genoa1_js statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL>  show database genoa1_js logxptstatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
              genoa1            genoa1_fc

DGMGRL> show database genoa1_js InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
Publicado em DATAGUARD, TUTORIAL | Marcado com , , , , , | Deixe um comentário

Standby Database using 11g DUPLICATE FROM ACTIVE DATABASE

Purpose

 

This note explains the procedure of creating a Physical Standby database using 11g RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

 

Environment

 

Primary Database DB_UNIQUE_NAME:    genoa1_js

Standby Database DB_UNIQUE_NAME: genoa1_fc

 

ORACLE_SID: genoa1

 

Primary hostname: oatu037

Standby hostname: drou037

 

Oracle software version:  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit

 

 

Enable Force Logging on the Primary database

 

SQL> alter database force logging;

 

Database altered.

 

 

Create the password file on the Standby host

 

Note  – ensure that the same password is used as the one used while creating the password file on the Primary host

 

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> orapwd file=orapwgenoa1 password=G3nesisoat

 

 

Update network configuration files

 

Add the following entries to the tnsnames.ora file on both Primary as well as Standby hosts.  The listener has been configured to run from the ASM home on the server, so we would need to update the tnsnames.ora file in both the database as well as ASM Oracle homes on both machines.

 

genoa1_js =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oatu037)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = genoa1_js)

)

)

 

genoa1_fc =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = drou037)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = genoa1_fc)

)

)

 

On the Standby host, add a static entry in the listener.ora file and reload or restart the listener.

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oatu037.bankwest.com)(PORT = 1521))

)

)

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = genoa1_fc)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = genoa1)

)

)

 

The database initially had a service_names value of ‘genoa1’.  We have defined the network configuration files using distinct service names to match the db_unique_name values on both Primary as well as Standby locations. We now need to change the service_names parameter and then we can test connectivity from the using the TNS aliases that we set up in the earlier step.

 

SQL> alter system set service_names=’genoa1_js’ scope=both;

 

System altered.

 

SQL> show parameter service

 

NAME                                 TYPE        VALUE

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

service_names                        string      genoa1_js

 

 

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> sqlplus system/G3nesisoat@genoa1_js

 

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Mar 15 15:43:43 2010

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

With the Partitioning and Real Application Testing options

 

SQL> select host_name from v$instance;

 

HOST_NAME

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

oatu037

 

 

Create a “scratch” init.ora file on the Standby host with just a single parameter

 

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> vi initgenoa1.ora

 

“initgenoa1.ora” [New file]

DB_NAME=genoa1

 

 

Create the required directories on the Standby host

 

Check the value of the parameter audit_file_dest  on the Primary database. Ensure that this directory structure also exists on the Standby host. If the directory structure differs in any way on the Primary and Standby server, we will have to ensure that the RMAN rcv file to create the Standby database is amended to reflect this as well.

 

On Primary database:

 

SQL> show parameter audit_file

 

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u01/oracle/admin/genoa1/adump

 

On Standby host:

 

genoa1:/u01/oracle/admin> mkdir genoa1

genoa1:/u01/oracle/admin> cd genoa1

genoa1:/u01/oracle/admin/genoa1> mkdir adump

 

 

Create the active_standby.rcv file

 

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET DB_UNIQUE_NAME=’genoa1_fc’

SET LOG_ARCHIVE_DEST_2=’service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)’

Set STANDBY_FILE_MANAGEMENT=’AUTO’

SET FAL_SERVER=’genoa1_js’

SET FAL_CLIENT=’genoa1_fc’

SET CONTROL_FILES=’+DATA/genoa1/controlfile/control01.ctl’,’+DATA/genoa1/controlfile/control02.ctl’

NOFILENAMECHECK

;

 

 

Start the Standby database instance in NOMOUNT state

 

genoa1:/u01/oracle/product/11.1.0/db_1/dbs> sqlas

 

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Mar 15 15:40:21 2010

 

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

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  217157632 bytes

Fixed Size                  2152328 bytes

Variable Size             159385720 bytes

Database Buffers           50331648 bytes

Redo Buffers                5287936 bytes

 

 

From the Primary host, run the following RMAN command to create the Standby Database

 

rman target / auxiliary sys/G3nesisoat@genoa1_fc cmdfile=active_standby.rcv log=cre_actv_stndby.log

 

 

Starting Duplicate Db at 16-03-2010 09:51:38

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=97 device type=DISK

 

contents of Memory Script:

{

backup as copy reuse

file  ‘/u01/oracle/product/11.1.0/db_1/dbs/orapwgenoa1’ auxiliary format

‘/u01/oracle/product/11.1.0/db_1/dbs/orapwgenoa1’   file

‘/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora’ auxiliary format

‘/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora’   ;

sql clone “alter system set spfile= ”/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora””;

}

executing Memory Script

 

Starting backup at 16-03-2010 09:51:40

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=529 device type=DISK

Finished backup at 16-03-2010 09:51:43

 

sql statement: alter system set spfile= ”/u01/oracle/product/11.1.0/db_1/dbs/spfilegenoa1.ora”

 

contents of Memory Script:

{

sql clone “alter system set  db_unique_name =

”genoa1_fc” comment=

”” scope=spfile”;

sql clone “alter system set  LOG_ARCHIVE_DEST_2 =

”service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)” comment=

”” scope=spfile”;

sql clone “alter system set  FAL_SERVER =

”genoa1_js” comment=

”” scope=spfile”;

sql clone “alter system set  FAL_CLIENT =

”genoa1_fc” comment=

”” scope=spfile”;

sql clone “alter system set  CONTROL_FILES =

”+DATA/genoa1/controlfile/control01.ctl”, ”+DATA/genoa1/controlfile/control02.ctl” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount ;

}

executing Memory Script

 

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

 

sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ”service=genoa1_js LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)” comment= ”” scope=sp

file

 

sql statement: alter system set  FAL_SERVER =  ”genoa1_js” comment= ”” scope=spfile

 

sql statement: alter system set  FAL_CLIENT =  ”genoa1_fc” comment= ”” scope=spfile

 

 

sql statement: alter system set  CONTROL_FILES =  ”+DATA/genoa1/controlfile/control01.ctl”, ”+DATA/genoa1/controlfile/control02.ctl” comment= ”” scope=s

pfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    5344731136 bytes

 

Fixed Size                     2153536 bytes

Variable Size               3154117568 bytes

Database Buffers            2147483648 bytes

Redo Buffers                  40976384 bytes

 

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format  ‘+DATA/genoa1/controlfile/control01.ctl’;

restore clone controlfile to  ‘+DATA/genoa1/controlfile/control02.ctl’ from

‘+DATA/genoa1/controlfile/control01.ctl’;

sql clone ‘alter database mount standby database’;

}

executing Memory Script

 

Starting backup at 16-03-2010 09:51:49

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/oracle/product/11.1.0/db_1/dbs/snapcf_genoa1.f tag=TAG20100316T095149 RECID=23 STAMP=713785910

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 16-03-2010 09:51:52

 

Starting restore at 16-03-2010 09:51:52

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=537 device type=DISK

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 16-03-2010 09:51:54

 

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”;

backup as copy reuse

datafile  1 auxiliary format

“+data”   datafile

2 auxiliary format

“+data”   datafile

3 auxiliary format

“+data”   datafile

4 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

 

Starting backup at 16-03-2010 09:51:59

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/genoa1/datafile/system.260.713696473

output file name=+DATA/genoa1_fc/datafile/system.256.713785921 tag=TAG20100316T095159 RECID=0 STAMP=0

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/genoa1/datafile/sysaux.258.713694281

output file name=+DATA/genoa1_fc/datafile/sysaux.259.713785935 tag=TAG20100316T095159 RECID=0 STAMP=0

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/genoa1/datafile/undotbs1.261.713696297

output file name=+DATA/genoa1_fc/datafile/undotbs1.260.713785951 tag=TAG20100316T095159 RECID=0 STAMP=0

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/genoa1/datafile/users.259.713694329

output file name=+DATA/genoa1_fc/datafile/users.261.713785957 tag=TAG20100316T095159 RECID=0 STAMP=0

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16-03-2010 09:52:38

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

backup as copy reuse

archivelog like  “+FRA/genoa1/archivelog/2010_03_16/thread_1_seq_43.317.713785959” auxiliary format

“+FRA”   ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

 

 

Starting backup at 16-03-2010 09:52:39

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=43 RECID=39 STAMP=713785959

output file name=+FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 16-03-2010 09:52:40

 

searching for all files in the recovery area

 

List of Files Unknown to the Database

=====================================

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_42.260.713783777

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_43.265.713785959

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.256.713749183

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.257.713749183

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.258.713749185

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.259.713749185

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.261.713783787

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.262.713783789

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.263.713783789

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.264.713783791

cataloging files…

cataloging done

 

List of Cataloged Files

=======================

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_42.260.713783777

File Name: +fra/GENOA1_FC/ARCHIVELOG/2010_03_16/thread_1_seq_43.265.713785959

 

List of Files Which Where Not Cataloged

=======================================

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.256.713749183

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.257.713749183

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.258.713749185

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.259.713749185

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_4.261.713783787

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_5.262.713783789

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_6.263.713783789

RMAN-07529: Reason: catalog is not supported for this file type

File Name: +fra/GENOA1_FC/ONLINELOG/group_7.264.713783791

RMAN-07529: Reason: catalog is not supported for this file type

 

datafile 1 switched to datafile copy

input datafile copy RECID=23 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/system.256.713785921

datafile 2 switched to datafile copy

input datafile copy RECID=24 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/sysaux.259.713785935

datafile 3 switched to datafile copy

input datafile copy RECID=25 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/undotbs1.260.713785951

datafile 4 switched to datafile copy

input datafile copy RECID=26 STAMP=713785961 file name=+DATA/genoa1_fc/datafile/users.261.713785957

 

contents of Memory Script:

{

set until scn  3817576;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 16-03-2010 09:52:42

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 43 is already on disk as file +FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959

archived log file name=+FRA/genoa1_fc/archivelog/2010_03_16/thread_1_seq_43.265.713785959 thread=1 sequence=43

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

Finished recover at 16-03-2010 09:52:43

Finished Duplicate Db at 16-03-2010 09:52:46

 

Recovery Manager complete.

 

 

Change the init.ora parameters related to redo transport and redo apply

 

On primary :

 

SQL> alter system set fal_server=’genoa1_fc’ scope=both;

 

System altered.

 

SQL> alter system set fal_client=’genoa1_js’ scope=both;

 

System altered.

 

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=genoa1_js’;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=genoa1_fc LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=genoa1_fc’;

 

 

Shutdown the Standby database, add the Standby log files and then start real time recovery

 

 

SQL> shutdown immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 5344731136 bytes

Fixed Size                  2153536 bytes

Variable Size            3154117568 bytes

Database Buffers         2147483648 bytes

Redo Buffers               40976384 bytes

Database mounted.

Database opened.

 

 

SQL> alter database add standby logfile group 4 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 5 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 6 size 500m;

 

Database altered.

 

SQL>  alter database add standby logfile group 7 size 500m;

 

Database altered.

 

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

 

SQL> !ps -ef |grep mrp

 

oracle 471268      1   0 10:51:16      –  0:02 ora_mrp0_genoa1

oracle 475150 270568   1 11:02:47  pts/0  0:00 grep mrp

 

 

Change the Protection Level of the Standby Database to MAXIMIZE AVAILABILITY

 

On the Primary database:

 

SQL> shutdown immediate;

 

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount

 

ORACLE instance started.

 

Total System Global Area 5344731136 bytes

Fixed Size                  2153536 bytes

Variable Size            3154117568 bytes

Database Buffers         2147483648 bytes

Redo Buffers               40976384 bytes

Database mounted.

 

SQL> alter database add standby logfile group 4 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 5 size 500m;

 

Database altered.

 

SQL> alter database add standby logfile group 6 size 500m;

 

Database altered.

 

SQL>  alter database add standby logfile group 7 size 500m;

 

Database altered.

 

SQL> alter database set standby database to maximize availability;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Publicado em DATAGUARD, TUTORIAL | Deixe um comentário

Oracle 11g External Tables to store results of OS commands

In Oracle 11g, we can now use external tables to not only display data stored in flat files outside the database, but also store the result of any command executed at the OS level – for example in this case we have a shell script which basically runs the ‘uptime’ command and we then run that command from the database by just querying the table – this can have a number of useful applications and something that developers need to be made aware of …..

[oracle@redhat64 oracle]$ cat get_uptime.sh

#!/bin/ksh
/usr/bin/uptime

SQL> CREATE TABLE uptime
2 (
3 data varchar2(255)
4 )
5 ORGANIZATION external
6 ( TYPE oracle_loader
7 DEFAULT DIRECTORY load_dir
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE
10 preprocessor exec_dir:’get_uptime.sh’
11 FIELDS TERMINATED BY “|” LDRTRIM
12 )
13 location ( ‘get_uptime.sh’)
14 )
15 /

Table created.

SQL> select * from uptime;

DATA
——————————————————————————–
14:49:19 up 15 days, 4:44, 1 user, load average: 0.09, 0.16, 0.13

Publicado em ORACLE 11gR2, SHELL SCRIPT | Deixe um comentário

Oracle GoldenGate – Using the Director Client

Oracle GoldenGate – Using the Director Client

To start the GoldenGate Director Server, we need to run the startWebLogic.sh script located in the “domain” sub-directory of the GoldenGate Director Server software installation directory.

[oracle@redhat346 bin]$ cd /u01/oracle/gg-director

[oracle@redhat346 gg-director]$ ls
cds_current_db_config.xml cds-tool-error.log cds-tool.log domain domain-new.jar ggs.url notices.txt uninstall

[oracle@redhat346 gg-director]$ cd domain

[oracle@redhat346 domain]$ cd bin

[oracle@redhat346 bin]$ ls
log4j.properties server_migration setDomainEnv.sh startPointBaseConsole.sh stopManagedWebLogic.sh
nodemanager service_migration startManagedWebLogic.sh startWebLogic.sh stopWebLogic.sh

[oracle@redhat346 domain]$ ./startWebLogic.sh.
……………………………
***************************************************
* To start WebLogic Server, use a username and *
* password assigned to an admin-level user. For *
* server administration, use the WebLogic Server *
* console at http://hostname:port/console *
***************************************************
starting weblogic with Java version:
java version “1.6.0_11″
Java(TM) SE Runtime Environment (build 1.6.0_11-b03)

…………………………………
……………………………………….

Note: we need to keep this window open!

We can now use the Director Administrator tool to create and modify administrator user accounts and manage data sources.

In the bin sub-directory under the top level directory where we installed the GoldenGate Director Client, we will find two shell scripts for launching the Director Administrator and the Director Client.

[oracle@redhat346 bin]$ cd /u01/oracle/gg-client/

[oracle@redhat346 gg-client]$ ls
bin etc GDSC Admin Tool GoldenGate-Director libs META-INF notices.txt Oracle GDSC Admin Tool.desktop Oracle GoldenGate-Director.desktop uninstall

[oracle@redhat346 gg-client]$ cd bin

[oracle@redhat346 bin]$ ls
run-admin.sh run-director.sh

Note – the default password for the admin user is ‘admin’ – we can change that via the Director Administrator tool.

[oracle@redhat346 bin]$ ./run-admin.sh

 

From the GoldenGate Director Client, we can manage all the GoldenGate instances in our environment as well as use a GUI to execute any of the command line GSSCI commands. We can also create and modify Extract and Replicat processes as well as get a diagramatical representation of the data extraction and replication processes currently running. We launch the Director Client by running the run-director.sh script as shown below.

[oracle@redhat346 bin]$ ./run-director.sh

GoldenGate Director Web

The browser based web client can be launched from the URL shown below ……

http://redhat346.localdomain:7001/acon

Publicado em GOLDENGATE | Deixe um comentário

Oracle GoldenGate – Installing GoldenGate Director Server and Clie

Oracle GoldenGate – Installing GoldenGate Director Server and Client

GoldenGate Director is a multi tier client server application that enables the configuration and management of the GoldenGate environment from a remote client which includes a web browser based client.

There are a number of different components which go to make up the GoldenGate Director product. Let us briefly describe each one of them.

GoldenGate Director Server – It is installed in a Weblogic server domain and enables the management of the different instances of GoldenGate which run in our environment.

GoldenGate Director Database – it is the central repository which is housed in a database (SQL Server/MySQL/Oracle) which contains information about the users, graphical diagrams which are created and other information related to user preferences.

GoldenGate Director Client – it is a GUI tool for managing the GoldenGate instances and runs on any platform which supports Java providing a menu driven interface with standard drag and drop functionality.

GoldenGate Director Web – web application that is hosted in the Director Server which provided browser based access to the GoldenGate environment.

GoldenGate Director Administrator – another client of the Director Server which enables us to carry out admin tasks like creating and modifying the admin user accounts, creating and modifying data sources which can be then used in the Director Client.

Before installing the Director Server we need to ensure that the JRE version 1.6 is already installed on the platform where we are going to install Director Server and also that Oracle 11g Weblogic Server (10.3.1) is available and running.

The following files were used for installation on Red Hat Linux RHEL 5 ….

oepe11_wls1031_linux32.bin – Oracle 11g Weblogic Server 10.3.1.
V19134-01.zip – GoldenGate Director Server
V19136-01.zip – GoldenGate Director Client

Let us look at the screen shots of an Oracle Weblogic Server installation.

 

 

Director Server Installation

In addition to the JRE 1.6.x requirement and the providing the location of the Weblogic Server software installation, we need to ensure that a database user has been created in the database which is going to serve as a repository for the Director Server. This database user needs standard privileges to create, alter and drop tables and indexes in it’s own schema. We will provide details of this user account in the course of the GoldenGate Director Server installation.

[oracle@redhat346 bin]$ export PATH=/u01/oracle/jre1.6.0_18/bin:$PATH
[oracle@redhat346 bin]$ java -version
java version “1.6.0_18
Java(TM) SE Runtime Environment (build 1.6.0_18-b07)
Java HotSpot(TM) 64-Bit Server VM (build 16.0-b13, mixed mode)

[oracle@redhat346 ~]$ ./gg-director-serversetup_unix_v2_0_0_3_007.sh
Starting Installer …

 

Director Client Installation

[oracle@redhat346 ~]$ ./gg-director-clientsetup_unix_v2_0_0_3_007.sh
Starting Installer …

 

Publicado em GOLDENGATE | Deixe um comentário

Oracle GoldenGate Veridata Installation and Configuration

Oracle GoldenGate Veridata Installation and Configuration

Oracle GoldenGate Veridata is a product that enables us to compare two sets of data and identify and report on data that is out of synchronization. This is helpful in a 24×7 replication enviornment where takimg downtime to compare sets of data is not an option and Veridata can run concurrently with the Extract and Replicat processes.

Let us look at the different components of GoldenGate Veridata.

Veridata Server – it coordinates the different Veridata tasks, performs data comparisons, sorting where required, reporting

Repository – it makes use of an Oracle or MySQL database for storing data related to user preferences, configuration information, job runtime statistics etc.

Veridata Agent – makes the required database calls on behalf of the Veridaata server to fetch and return rows required for comparions.

Veridata Web – browser based interface to create comparison configurations and run jobs and view reports. B

Note: Veridata also has a command line interface called vericom which performs most of the tasks which can be performed via the web interface from the command line.

Source: GoldenGate Veridata Administration Guide

Let us look at some of the screen shots taken from the Veridata Server Installation.

In the next post, we will discuss the installation and configuration of the Veridata Agent as well as using the Veridata Web to create and run some comparison jobs and view reports.

In this instance, we are installing the server on an AIX 5.3 Power series machine. So we have downloaded the file V19619-01.zip from the E-Delivery web site.

The Veridata Server requires a database user with the required privileges and also a database role called VERIDATA_ROLE. We are using our existing database user which we have used in earlier tutorials GGS_OWNER and have granted the role to this user.

SQL> create role veridata_role;

Role created.

SQL> grant create session, create table, create view, create procedure, create synonym to veridata_role;

Grant succeeded.

We then unzip this file and it creates another file called GoldenGate_Veridata_aix53_ppc_v3004_003.sh.

We then configure the DISPLAY variable to point to a valid X Windows and run the same ….

dba1:/u01/ofsad2/veridata> ./GoldenGate_Veridata_aix53_ppc_v3004_003.sh
Unpacking JRE …
Starting Installer …

Publicado em GOLDENGATE | Deixe um comentário

Installing and Configuring GoldenGate Veridata Agent

Installing and Configuring GoldenGate Veridata Agent

The GoldenGate Veridata Agents execute the database related requests to compare source and target database rows on behalf of the Veridata Server.

At a minimum we need to install two agents – one to retrieve source rows and one to retrieve target rows from the database.

There are two types of agents – one is the Java based agent and the other is the C-Agent.

The Java Agent connects to the database using JDBC and permits data comparisons in heterogeneous environments. A Java environment JRE/JSDK is required to support this agent and a minimum java version is 1.5

We have copied the V19618-01.zip file from the Metalink E-Deilvery site and unzipped it as shown below.

dba1:/u01/ofsad2> mkdir veriagent
dba1:/u01/ofsad2> mv V19618-01.zip ./veriagentdba1:/u01/ofsad2> cd veriagent
dba1:/u01/ofsad2/veriagent> ls
V19618-01.zip
dba1:/u01/ofsad2/veriagent> unzip *
Archive: V19618-01.zip
creating: agent/
creating: agent/classes/
creating: agent/drivers/
creating: agent/lib/
creating: agent/licenses/
inflating: agent/JavaAgent.jar
inflating: agent/agent.bat
inflating: agent/agent.properties.sample
inflating: agent/classes/log4j.properties
inflating: agent/drivers/gvdb2.jar
inflating: agent/drivers/ojdbc5.jar
inflating: agent/drivers/sqljdbc.jar
inflating: agent/lib/commons-dbcp-1.2.2.jar
inflating: agent/lib/commons-pool-1.3.jar
inflating: agent/lib/log4j-1.2.12.jar
inflating: agent/licenses/Apache-LICENSE-2.0.txt
inflating: agent/licenses/notices.txt
inflating: agent/agent.sh

We then need to copy the agents.properties.sample file and create a file called agents.properties as shown below. Here we are providing the port where the agent will run as well as database connection information like the listener port and SID of the database.

dba1:/u01/ofsad2/veriagent/agent> cp agent.properties.sample agent.properties

# The server.port property is the port where the Veridata agent listens
# for connection requests.
server.port=4000
# Oracle sample database connection URL^M
database.url=jdbc:oracle:thin:@redhat346:1522:gavin
server.driversLocation = drivers
server.jdbcDriver=ojdbc5.jar
database.transaction.isolation=READ_COMMITTED

[oracle@redhat346 agent]$ export JRE_HOME=/u01/oracle/jre1.6.0_18
[oracle@redhat346 agent]$ ./agent.sh start

Installing the C Agent

We first download the file V18202-01.zip from the E-Delivery web site and unpack as shown below. We then will launch the GoldenGate Command Interpretor (GGSCI) from the Veridata Agent directory location. We create the sub directories for the agent and configure a manager process for the Veridata Agent – not to be confused with the Manager process that we configured at the very beginning of the GoldenGate installation.

dba1:/u01/ofsad2/veriagent> unzip V18202-01.zip
Archive: V18202-01.zip
inflating: ggs_veridataAgent_aix53_ppc_ora102_64bit_v3.0.0.2_001.tar

dba1:/u01/ofsad2/veriagent> tar -xvf ggs_veridataAgent_aix53_ppc_ora102_64bit_v3.0.0.2_001.tar
x veridata
x veridata/agent
x veridata/agent/mgr, 1848763 bytes, 3611 tape blocks
x veridata/agent/ggsci, 2283442 bytes, 4460 tape blocks
x veridata/agent/ggMessage.dat, 393280 bytes, 769 tape blocks
x veridata/agent/help.txt, 117942 bytes, 231 tape blocks
x veridata/agent/bcrypt.txt, 1725 bytes, 4 tape blocks
x veridata/agent/libxml2.txt, 1668 bytes, 4 tape blocks
x veridata/agent/zlib.txt, 1476 bytes, 3 tape blocks
x veridata/agent/freeBSD.txt, 1968 bytes, 4 tape blocks
x veridata/agent/notices.txt, 176571 bytes, 345 tape blocks
x veridata/agent/libicui18n38.a, 9302329 bytes, 18169 tape blocks
x veridata/agent/libicuuc38.a, 6302602 bytes, 12310 tape blocks
x veridata/agent/libicudata38.a, 11360230 bytes, 22188 tape blocks
x veridata/agent/veriagt, 4438543 bytes, 8670 tape blocks

dba1:/u01/ofsad2/veriagent> cd veridata
dba1:/u01/ofsad2/veriagent/veridata> ls
agent
dba1:/u01/ofsad2/veriagent/veridata> cd agent
dba1:/u01/ofsad2/veriagent/veridata/agent> ls
bcrypt.txt ggMessage.dat help.txt libicui18n38.a libxml2.txt notices.txt zlib.txt
freeBSD.txt ggsci libicudata38.a libicuuc38.a mgr veriagt

dba1:/u01/ofsad2/veriagent/veridata/agent> ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 003
AIX 5L, ppc, 64bit (optimized), Oracle 10 on Sep 21 2009 00:19:47

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

GGSCI (devu026) 1>

GGSCI (devu026) 1> create subdirs

Creating subdirectories under current directory /u01/ofsad2/veriagent/veridata/agent

Parameter files /u01/ofsad2/veriagent/veridata/agent/dirprm: created
Report files /u01/ofsad2/veriagent/veridata/agent/dirrpt: created
Checkpoint files /u01/ofsad2/veriagent/veridata/agent/dirchk: created
Process status files /u01/ofsad2/veriagent/veridata/agent/dirpcs: created
SQL script files /u01/ofsad2/veriagent/veridata/agent/dirsql: created
Database definitions files /u01/ofsad2/veriagent/veridata/agent/dirdef: created
Extract data files /u01/ofsad2/veriagent/veridata/agent/dirdat: created
Temporary files /u01/ofsad2/veriagent/veridata/agent/dirtmp: created
Veridata files /u01/ofsad2/veriagent/veridata/agent/dirver: created
Veridata Lock files /u01/ofsad2/veriagent/veridata/agent/dirver/lock: created
Veridata Out-Of-Sync files /u01/ofsad2/veriagent/veridata/agent/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/ofsad2/veriagent/veridata/agent/dirver/oosxml: created
Veridata Parameter files /u01/ofsad2/veriagent/veridata/agent/dirver/params: created
Veridata Report files /u01/ofsad2/veriagent/veridata/agent/dirver/report: created
Veridata Status files /u01/ofsad2/veriagent/veridata/agent/dirver/status: created
Veridata Trace files /u01/ofsad2/veriagent/veridata/agent/dirver/trace: created
Stdout files /u01/ofsad2/veriagent/veridata/agent/dirout: created

GGSCI (devu026) 2> edit params mgr

“/u01/ofsad2/veriagent/veridata/agent/dirprm/mgr.prm” [New file]
PORT 7807
~

GGSCI (devu026) 3> start manager

Manager started.

GGSCI (devu026) 4> info manager

Manager is running (IP port devu026.7807).

Publicado em GOLDENGATE | Deixe um comentário