Arquivos Database -

Clonando o Oracle_Home

17 de julho de 2019 vanberto zuim Database

Em muitas ocasiões ,é bem mais fácil para um dba clona um ORACLE_HOME existente do que tentar criar um novo, Abaixo segue o passo a passo para realizar o clone do ORACLE_HOME . 1. Como Root entre na pasta do oracle_base e zip o oracle Home

cd /u01/app/oracle/db1

tar -cvzf /u01/home_11.2.0.4.tgz 11.2.0.4

tar -cvzf home_11.2.0.4.tgz 11.2.0.4

cd /u01/app/oracle/db1

tar -cvzf /u01/home_11.2.0.4.tgz 11.2.0.4

tar -cvzf home_11.2.0.4.tgz 11.2.0.4

2. Copie o arquivo home_11.2.0.4.tgz para o servidor de banco 3. Descompacte o arquivo com o usuário root na estrutura desejada

tar -xvf home_11.2.0.4.tgz

tar -xzvf home_11.2.0.4.tgz

tar -xvf home_11.2.0.4.tgz

tar -xzvf home_11.2.0.4.tgz

4. Entre na pasta 11.2.0.4 depoiso comando abaixo com os valores[…]

Alterando um datafile no standby

5 de julho de 2019 vanberto zuim Database

Quando um datafile é criado no banco de produção e o diskgroup ou a partição não está mapeada no standby, pode ocorrer o erro abaixo:

ORA-01119: erro ao criar o arquivo '+dg1_zmsolution' de banco de dados
ORA-17502: ksfdcre:4 Falhou ao criar o arquivo +dg1_zmsolution
ORA-15001: grupo de discos "DG1_ZMSOLUTION" não existe ou não está montado
ORA-15001: diskgroup "DG1_ZMSOLUTION" does not exist or is not mounted
File #198 added to control file as 'UNNAMED00198'.
Originally created as:
'+DG1_ZMSOLUTION/zmdb/datafile/zuim_ts_tx_data.280.1008514969'
Recovery was unable to create the file as:
'+dg1_zmsolution'
Errors with log +DG_FRA/zmdb/zuim_arc_1_131660_890817996.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/zmdb/diag/rdbms/zmdbsty/zmdbsty1/trace/zmdbsty1_pr00_7388.trc:
ORA-01274: não é possível adicionar o arquivo de dados '+DG1_ZMSOLUTION/zmdb/datafile/zuim_ts_tx_data.280.1008514969' - o arquivo não pôde ser criado
Recovery interrupted!
Fri May 17 16:20:59 2019
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 6189833565041 but controlfile could be ahead of datafiles.
Fri May 17 16:20:59 2019
MRP0: Background Media Recovery process shutdown (zmdbsty1)

ORA-01119: erro ao criar o arquivo '+dg1_zmsolution' de banco de dados

ORA-17502: ksfdcre:4 Falhou ao criar o arquivo +dg1_zmsolution

ORA-15001: grupo de discos "DG1_ZMSOLUTION" não existe ou não está montado

ORA-15001: diskgroup "DG1_ZMSOLUTION" does not exist or is not mounted

File #198 added to control file as 'UNNAMED00198'.

Originally created as:

'+DG1_ZMSOLUTION/zmdb/datafile/zuim_ts_tx_data.280.1008514969'

Recovery was unable to create the file as:

'+dg1_zmsolution'

Errors with log +DG_FRA/zmdb/zuim_arc_1_131660_890817996.dbf

MRP0: Background Media Recovery terminated with error 1274

Errors in file /u01/app/zmdb/diag/rdbms/zmdbsty/zmdbsty1/trace/zmdbsty1_pr00_7388.trc:

ORA-01274: não é possível adicionar o arquivo de dados '+DG1_ZMSOLUTION/zmdb/datafile/zuim_ts_tx_data.280.1008514969' - o arquivo não pôde ser criado

Recovery interrupted!

Fri May 17 16:20:59 2019

Recovery stopped due to failure in applying recovery marker (opcode 17.30).

Datafiles are recovered to a consistent state at change 6189833565041 but controlfile could be ahead of datafiles.

Fri May 17 16:20:59 2019

MRP0: Background Media Recovery process shutdown (zmdbsty1)

O procedimento para altera a localização do datafile é bem simples . altere o standby_file_management para manual

alter system set standby_file_management='manual';

1	alter system set standby_file_management='manual';

Criei o novo datafile levando em questão o datafile criado fora de ordem.

alter database create datafile '/u01/app/zmdb/product/11.2.0.4/dbs/UNNAMED00023' as '+DG4/zmdbsty/datafile/zuim_ts_tx_data.262';

1	alter database create datafile '/u01/app/zmdb/product/11.2.0.4/dbs/UNNAMED00023' as '+DG4/zmdbsty/datafile/zuim_ts_tx_data.262';

Altere o standby_file_management para Automático

alter system set standby_file_management='auto';

1	alter system set standby_file_management='auto';

Habilite novamente a replicação.

recover managed standby database disconnect;

1	recover managed standby database disconnect;

OPatch requires a response file for Oracle Configuration Manager (OCM).

19 de junho de 2019 vanberto zuim Database

Erro no Opatch.. 🙁

Verifying environment and performing prerequisite checks...
UtilSession failed:
To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM).
Please run "/tmp/oracle-home-156017296682832/OPatch/ocm/bin/emocmrsp" to generate an OCM response file. The generated response file
can be reused on different platforms and in multiple OPatch silent installs.
To regenerate an OCM response file, Please rerun "/tmp/oracle-home-156017296682832/OPatch/ocm/bin/emocmrsp".
Log file location: /u01/app/sa/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2019-06-10_10-22-53AM_1.log
OPatch failed with error code 73

Para Resolução :
Execute
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp

Altere a permissão do arquivo 
chmod 775 ocm.rsp

Execute o opatch com a clausula <strong>- ocmrf</strong>
 /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch apply -silent -ocmrf /u01/ocm.rsp

Verifying environment and performing prerequisite checks...

UtilSession failed:

To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM).

Please run "/tmp/oracle-home-156017296682832/OPatch/ocm/bin/emocmrsp" to generate an OCM response file. The generated response file

can be reused on different platforms and in multiple OPatch silent installs.

To regenerate an OCM response file, Please rerun "/tmp/oracle-home-156017296682832/OPatch/ocm/bin/emocmrsp".

Log file location: /u01/app/sa/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2019-06-10_10-22-53AM_1.log

OPatch failed with error code 73

Para Resolução :

Execute

$ORACLE_HOME/OPatch/ocm/bin/emocmrsp

Altere a permissão do arquivo

chmod 775 ocm.rsp

Execute o opatch com a clausula <strong>- ocmrf</strong>

/u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch apply -silent -ocmrf /u01/ocm.rsp

Alterando o CHARACTER do Oracle

3 de junho de 2019 vanberto zuim Database, Não categorizado

Nesse post vamos altera o CHARACTER do banco de dados Oracle.

conn / as sysdba
sho parameter CLUSTER_DATABASE
sho parameter PARALLEL_SERVER
2. Execute the following commands in sqlplus connected as "/ AS SYSDBA":

conn / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;
SHUTDOWN IMMEDIATE;

conn / as sysdba

sho parameter CLUSTER_DATABASE

sho parameter PARALLEL_SERVER

2. Execute the following commands in sqlplus connected as "/ AS SYSDBA":

conn / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;

SHUTDOWN IMMEDIATE;

Cuidado..valide bem antes antes de fazer isso..

Erro ORA-25226 após import com impdp

17 de maio de 2019 vanberto zuim Database, EBS, Oracle BPM, Oracle SOA Suite, OSB

As vezes realizamos o import de um sistema que possui queue, normalmente após o import ocorre o seguinte erro: ORA-25226 dequeue failed queue not enabled for dequeue, para solucionar esse erro é bem simples, apos o import habilite novamente as queues. segue o comando abaixo:

select para verificar a QUEUE

SQL> select OWNER,NAME,QUEUE_TABLE,ENQUEUE_ENABLED,DEQUEUE_ENABLED from dba_queues where NAME='EVENT_QUEUE';

OWNER NAME
------------------------------ ------------------------------
QUEUE_TABLE ENQUEUE DEQUEUE
------------------------------ ------- -------
STRMADMIN EVENT_QUEUE
EVENT_QUEUE_TAB NO NO

execute o comando com sys para habilitar a queue

EXECUTE DBMS_AQADM.start_queue (queue_name => 'strmadmin.event_queue',enqueue=> TRUE,dequeue=> TRUE);

select para verificar a QUEUE

SQL> select OWNER,NAME,QUEUE_TABLE,ENQUEUE_ENABLED,DEQUEUE_ENABLED from dba_queues where NAME='EVENT_QUEUE';

OWNER NAME

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

QUEUE_TABLE ENQUEUE DEQUEUE

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

STRMADMIN EVENT_QUEUE

EVENT_QUEUE_TAB NO NO

execute o comando com sys para habilitar a queue

EXECUTE DBMS_AQADM.start_queue (queue_name => 'strmadmin.event_queue',enqueue=> TRUE,dequeue=> TRUE);

ORA-31623: a job is not attached to this session via the specified handle

13 de maio de 2019 vanberto zuim Database

O erro ORA-31623 ocorre algumas vezes na hora de executar um expdp, isso ocorre devido o parametro streams_pool_size está com o valor 0 Vamos corrigir o problema.

[oraclezm@zmdb1~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 12 17:11:42 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter stream

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
streams_pool_size big integer
0
SQL> alter system set streams_pool_size=64M scope=both;
alter system set streams_pool_size=64M scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use


SQL> alter system set streams_pool_size=64M scope=memory;

System altered.

SQL>

[oraclezm@zmdb1~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 12 17:11:42 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter stream

NAME TYPE

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

VALUE

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

streams_pool_size big integer

SQL> alter system set streams_pool_size=64M scope=both;

alter system set streams_pool_size=64M scope=both

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL> alter system set streams_pool_size=64M scope=memory;

System altered.

SQL>

RMAN-20020: database incarnation not set

12 de maio de 2019 vanberto zuim Database, Não categorizado

Quando tentamos fazer a ressincronização do catálogo com o banco de dados de destino, se recebermos a menssagem RMAN-20020: database incarnation not set, então temos que redefinir o banco de dados para definir a encarnação para o atual.

Step 1
connect to  rman and connect to the target database with recovery catalog
rman
Recovery Manager: Release 11.2.0.4.0 - Production 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connect target /
connected to target database: <SID> (DBID=<database_id>)
 
connect catalog rman/<password>@<catalog database service name>
connected to recovery catalog database

Step2

RMAN> resync catalog
2> ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 05/14/2013 13:36:02
RMAN-20020: database incarnation not set

RMAN> backup datafile <file_number>;
Starting backup at 14-MAY-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/14/2013 13:37:14
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set

Step 3
once you got the incarnation not set check the list of incarnation the database has

 list incarnation ;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
29      360     ZMDB    728861074        PARENT  1          05-MAR-18
29      361    ZMDB    728861074       PARENT  14640435   08-JUN-18
29      98      ZMDB   728861074        PARENT  14882874888 22-JUN-18
29      30      ZMDB   728861074        ORPHAN  15574861795 11-JUL-18
29      321    ZMDB    728861074       CURRENT 15996977185 22-AUG-18


SQL> select * from v$controlfile_record_section where TYPE ='DATABASE INCARNATION';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE INCARNATION                  56           292            4           1          4          4

we can check the no of database incarnations in the database control file ...

Step 4
To sync the incarnation number with the catalog issue the Reset the database  command in the rman 
and test the backup

RMAN> reset database;

database incarnation already registered

RMAN> backup datafile 3;
Starting backup at 14-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4256 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/fahdb/datafile/<datafile_number>
channel ORA_DISK_1: starting piece 1 at 14-MAY-13
channel ORA_DISK_1: finished piece 1 at 14-MAY-13
piece handle=+FLASH/fahdb/backupset/2013_05_14/nnndf0_tag20130514t135843_0.282.815407125 tag=TAG20130514T135843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-MAY-13
Starting Control File and SPFILE Autobackup at 14-MAY-13
piece handle=+FLASH/fahdb/autobackup/2013_05_14/s_815407132.326.815407133 comment=NONE
Finished Control File and SPFILE Autobackup at 14-MAY-13

RMAN> resync catalog ;
starting full resync of recovery catalog
full resync complete

Step 1

connect to rman and connect to the target database with recovery catalog

rman

Recovery Manager: Release 11.2.0.4.0 - Production

connect target /

connected to target database: <SID> (DBID=<database_id>)

connect catalog rman/<password>@<catalog database service name>

connected to recovery catalog database

Step2

RMAN> resync catalog

2> ;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of resync command on default channel at 05/14/2013 13:36:02

RMAN-20020: database incarnation not set

RMAN> backup datafile <file_number>;

Starting backup at 14-MAY-13

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 05/14/2013 13:37:14

RMAN-03014: implicit resync of recovery catalog failed

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set

Step 3

once you got the incarnation not set check the list of incarnation the database has

list incarnation ;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

29 360 ZMDB 728861074 PARENT 1 05-MAR-18

29 361 ZMDB 728861074 PARENT 14640435 08-JUN-18

29 98 ZMDB 728861074 PARENT 14882874888 22-JUN-18

29 30 ZMDB 728861074 ORPHAN 15574861795 11-JUL-18

29 321 ZMDB 728861074 CURRENT 15996977185 22-AUG-18

SQL> select * from v$controlfile_record_section where TYPE ='DATABASE INCARNATION';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

DATABASE INCARNATION 56 292 4 1 4 4

we can check the no of database incarnations in the database control file ...

Step 4

To sync the incarnation number with the catalog issue the Reset the database command in the rman

and test the backup

RMAN> reset database;

database incarnation already registered

RMAN> backup datafile 3;

Starting backup at 14-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4256 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=+DATA/fahdb/datafile/<datafile_number>

channel ORA_DISK_1: starting piece 1 at 14-MAY-13

channel ORA_DISK_1: finished piece 1 at 14-MAY-13

piece handle=+FLASH/fahdb/backupset/2013_05_14/nnndf0_tag20130514t135843_0.282.815407125 tag=TAG20130514T135843 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 14-MAY-13

Starting Control File and SPFILE Autobackup at 14-MAY-13

piece handle=+FLASH/fahdb/autobackup/2013_05_14/s_815407132.326.815407133 comment=NONE

Finished Control File and SPFILE Autobackup at 14-MAY-13

RMAN> resync catalog ;

starting full resync of recovery catalog

full resync complete

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

26 de abril de 2019 vanberto zuim Database

Quandos possuímos bancos na versão 12c e tentamos catalogar o banco em uma versão 11g , ocorrera o seguinte erro: PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old Esse erro ocorre devido ao catalogo do rman esta desatualizado. a resolução desse problema é bem simples

[oracle@zmsolution1~]$ rman target / catalog rman/zuim@rman

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:50:34 2019

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

connected to target database: ZUIMDB(DBID=1268148162)
 connected to recovery catalog database
 PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> register database;

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
 PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of register command at 04/25/2019 16:55:06
 RMAN-06429: RCVCAT database is not compatible with this version of RMAN

RMAN> upgrade catalog;

recovery catalog upgraded to version 12.02.00.01
 DBMS_RCVMAN package upgraded to version 12.02.00.01
 DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> register database;

database registered in recovery catalog
 starting full resync of recovery catalog
 full resync complete

RMAN>

[oracle@zmsolution1~]$ rman target / catalog rman/zuim@rman

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:50:34 2019

connected to target database: ZUIMDB(DBID=1268148162)

connected to recovery catalog database

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> register database;

PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of register command at 04/25/2019 16:55:06

RMAN-06429: RCVCAT database is not compatible with this version of RMAN

RMAN> upgrade catalog;

recovery catalog upgraded to version 12.02.00.01

DBMS_RCVMAN package upgraded to version 12.02.00.01

DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

Instalando o Oracle RAC 12c no OL6 Usando VirtualBox 5

5 de junho de 2018 vanberto zuim Database

Neste artigo vamos instalar o Oracle RAC 12c (12.1) no Oracle Linux 6 Usando VirtualBox 5, preferi iniciar pelo Oracle 12.1, para podermos realizar a migração para o Oracle 12.2 em um novo artigo. Para começar vamos baixar os instaladores do Oracle. Oracle Database 12c Release 1 (12.1.0.2.0) for Linux x86-64 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.2.0) for Linux x86-64 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html Criando a Máquina Virtual Antes de iniciar, crie uma pasta com o[…]

ORA-28040: No matching authentication protocol ocilogon():

8 de maio de 2018 vanberto zuim Database

Uma Solução de contorno para o erro é , acessar o diretório $ORACLE_HOME/network/admin/ e adicionar os parâmetros abaixo no sqlnet.ora. SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

cd $ORACLE_HOME/network/admin/

vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

cd $ORACLE_HOME/network/admin/

vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8