[ORACLE] Alter database open resetlogs failed after restore. And the problem was my Redo logs.

Hello all

 

How are you doing?

 

I was creating a new test environment today and I tried to open a database that I had restored. 

 

Simple. Of course not.

 

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+REDO/FOLHAHOM/ONLINELOG/group_4a.rdo'
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted

 

I checked the redo log files. They were wrong and I have no idea why.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

Maybe I forgot to set log_file_name_convert. 

 

I don't know and It doesn't matter. I didn't want to do the whole process again.

I can't drop the log file group because the status “CLEARING_CURRENT”, of course.

Kids, don't try it at home? 😜

I'm just kidding, but this solution is a workaround and should only be used in an emergency.

And remember: This is a test environment. Production can and probably will be different, so use at your own risk.

Let's go to the party:

 

-- Thread 1

SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_6a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_6a.rdo';
Database altered.

-- Thread 2
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_11a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_11a.rdo';
Database altered.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

 

Can I rename all the members of the log file?

Yes, but I preferred to recreate all the log file groups.

 

 

 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.
 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 7;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 8;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered.

SQL> alter system checkpoint;
System altered.

I checked that the old groups were inactive and dropped the last two groups.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
Database altered.

 

SQL> ALTER DATABASE ENABLE thread 2;

 

 

And that's it. I hope I've helped you.

Regards

Mario

 

 

[ORACLE] ORA-46362: Could not translate variable ORACLE_BASE.

Hello evceryone!

I hope you're doing well!

Today, when I tried to start a database using SRVCTL, I made the error below.

[GTP1.ora-rac01 dbs]$ srvctl start database -d GTP

When I started the database using SQLPLUS, it was normal.

I checked the ORACLE_BASE variable.

    [GTP1.ora-rac01 dbs]$ echo $ORACLE_BASE/

    /u01/app/oracle/

And I check the database configuration, as well.

    [GTP1.ora-rac01 dbs]$ srvctl config database -d GTP

Everything seemed normal.

When I check on "Database Error Messages", the error became clearer to me.

This database was restored and its environment variables were wrong. 

Why? I have no idea! 

The solution was to change the ORACLE_HOME variable using SRVCTL SETENV. 

[GTP1.ora-rac01 dbs]$ srvctl setenv database -db GTP -env ORACLE_BASE=$ORACLE_BASE

And there you go! As simple as that!

After that, the database can be started normally.

I hope that it helps you!

Regards

Mario

[ORACLE] Batch change EDITIONABLE property.

Hello everyone.

Hope you're doing well!

Today, I have a simple case.

 

A test database had many database objects with the EDITIONABLE property set to "N".

Why did this happen? I have no idea!

Of course, you don't have any possibility to making the change manually, object by object.

This isn't a normal thing to happen, so... let's get to the solution.

A simple script solves the problem. 😆

But always remember:  

• This step-by-step worked for me, but it may not work for you.
• It's a test environment. The real life will be different, for sure.
• This post is for study and testing as well and has no concern for performance and security best practices.

1) First, count the objects that will be changed.

SELECT

    substr(OWNER,1,20) AS OWNER,

    count(1) AS TOTAL,

    EDITIONABLE

FROM

    DBA_OBJECTS

WHERE

OWNER IN (

         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          

         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 

         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')

AND 

OBJECT_TYPE IN (

         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',

         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',

         'SYNONYM','VIEW')

AND 

EDITIONABLE = 'N'

GROUP BY OWNER, EDITIONABLE

ORDER BY OWNER, EDITIONABLE;

2) Generate the script for change.

SET SERVEROUTPUT ON;

SET PAGES 0;

SET VERIFY OFF;

SET FEEDBACK OFF;

SET HEADING  OFF;

     

SELECT 

   'ALTER '||object_type || ' ' || owner || '.' || object_name || ' EDITIONABLE;'

FROM

DBA_OBJECTS 

WHERE

OWNER IN (

         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          

         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 

         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')

AND 

OBJECT_TYPE IN (

         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',

         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',

         'SYNONYM','VIEW')

AND 

EDITIONABLE = 'N'

ORDER BY OWNER;

As simple as that.

But be careful. Some objects may be in heavy use at the moment, and because of this we can generate Library Cache Pin or Library Cache Lock waits - as in the image below. 

Therefore, it's important to run the script at a time when the database workload is low, monitoring the objects in use and the wait events.  

You can check the objects in the library cache using this SQL. This won't give you 100% certainty that the object is currently in use, but it's a good indicator.

SELECT 

     substr(OWNER,1,15) AS OWNER,

     substr(NAME,1,30)  AS NAME, 

     count(1) 

FROM

     GV$DB_OBJECT_CACHE 

WHERE

     NAME IN (

      'TRG_NOTA_FISCAL_CONV_PART','TRG_NOTA_FISCAL_FNFI',

      'TRG_NOTA_FISCAL_VALIDA_CNPJ','TRG_NOTA_FISCAL_PROIBE_CANC_NF',

      'TRG_NOTA_FISCAL_PARTICULAR','TRG_NOTA_FISCAL_AJUSTES',

      'TRG_NOTA_FISCAL_DADOS_NFE','TRG_NOTA_FISCAL_SN_CONTABILIZA',

      'TRG_EVOLUCAO_NOTA_FISCAL','TRG_FNFI_NF_INTEGRA_BENNER',

      'TRG_ATUALIZA_HIST_SIT_NF','TRG_STATUS_NFE_NOTA_FISCAL',

      'TRG_IMVW_OUT_NF_SUNAT')

GROUP BY OWNER, NAME;

3) Once again, count the objects with EDITIONABLE = N.

4) And count the objects with EDITIONABLE = Y.

I hope this simple tip helps you.

Regards

Mario

[ORACLE] How to move a datafile to a new location in ASM.

Hello everyone!

I hope you're doing well!

Today, I needed to move a datafile to another ASM Disk Group.

The process was simple, but remember that this step-by-step worked for me, but it may not work for you.

So do it in a test environment if you're not sure.

Important:

• For SYS and SYSAUX, the procedure will be slightly different, so don't use this step-by-step in these two cases. 
• There are also other ways to do this process, today I chose to do this one.

So, let's go to the party!

1) Check the datafile path

    SQL> SELECT file#, name FROM v$datafile where name like '%zab%';

2) Create a backup as copy with RMAN
    RMAN> BACKUP AS COPY DATAFILE 69 format '+DGFRA';

3) I put the datafile offline

    SQL> ALTER DATABASE DATAFILE 69 offline;

4) I switch & recover the datafile

    RMAN> switch datafile 69 to copy;
    RMAN> recover datafile 69;

5) I put the datafile online

    SQL> ALTER DATABASE DATAFILE 69 online;

6) Check the datafile in the new location 

    SQL> SELECT file#, name FROM v$datafile where name like '%zab%';

7) Delete the old datafile

    RMAN> delete datafilecopy '+DGDATA/PRD/DATAFILE/tbs_zabbix.334.1144323691';

As simple as that!

Regards

Mario

Erro ao executar um duplicate database em grid 12c (12.1.0.2.0)

Fala pessoal.

Hoje, montando um ambiente de testes aqui para um artigo de GoldenGate me deparei com um erro chatinho quando fui executar um duplicate from active database.

Estava com todas as configurações corretas, usando o script abaixo:

RUN 

{

set until scn 2462883;

set newname for datafile 1 to new;

set newname for datafile 2 to new;

set newname for datafile 3 to new;

set newname for datafile 4 to new;

set newname for datafile 5 to new;

set newname for datafile 6 to new;

set newname for tempfile 1 to new;

DUPLICATE TARGET DATABASE TO "ggcopy" FROM ACTIVE DATABASE;

}

Nada demais, clonando uma base de testes.

Estava tomando o erro:

sql statement: alter system set  db_unique_name =  ''GGCOPY'' comment= ''Modified by RMAN duplicate'' scope=spfile

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

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

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

RMAN-03002: failure of Duplicate Db command at 10/29/2016 09:12:29

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of sql command on clone_default channel at 10/29/2016 09:12:29

RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'GGCOPY' comment= 'Modified by RMAN duplicate' scope=spfile

ORA-32017: failure in updating SPFILE

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Pesquisando um pouco no MOS, como diz minha linda esposa, PAH!!! estava lá, um bug.

Note: ORA-65500 When Trying To Duplicate a Database (Doc ID 1963620.1)

Neste note, é referenciao um bug: Bug 20977794 - RMAN-3002 ORA-65500 from RMAN duplicate on RAC (Doc ID 20977794.8)

Existe um patch disponível, mas como não é o meu foco acertar essa base, eu nem apliquei.

Então, confirmando o bug, a minha base de testes estava registrada no meu grid, e por isso era dada a mensagem que o resource já existia.

Para resolver foi simples, aplicando o workaround do note, ou seja, excluir a base a ser clonada do grid e adicionar após o processo.

[ggcopy.rac12c1 dbs]$ srvctl remove database -d ggcopy

Remove the database ggcopy? (y/[n]) y

É isso, abraço.

Mario