Database – Page 6 – LUXOUG – LUXEMBOURG ORACLE USERS GROUP

Database

Tuning impdp, generate less redo with transform=disable_archive_logging:Y

Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Tuning impdp, generate less redo with transform=disable_archive_logging:Y

Every single DBA in this world know how boring is load huge amount of data into databases using dump files, it is so boring than see grass growing. Huge part of this time processing is because of redo/archivelog generation.

In Oracle 12cR1, oracle introduced this very nice feature to allow DBAs to import data bypassing redolog generation, nologging mode. So, you are running your database in force logging option it will not take effect and is strongly recommended generate a physical backup when the import of the database is finish.

Let’s setup our test environment, In this case I use 18c, but you can use 12cR1 as well to execute this procedure.

SQL> create directory home as '/home/oracle' ;

Directory created.

SQL> create user c##mufalani identified by welcome1;

User created.

SQL> grant dba to c##mufalani;

Grant succeeded.

Now, log in this user and create a table and load some test data on it.

— Create some data into one table

SQL> conn c##mufalani/welcome1
Connected.
SQL>
SQL>
SQL> create table test as select * from cdb_objects;

Table created.

SQL> insert into test (select * from test);

72897 rows created.

SQL> /

145794 rows created.

SQL> /

291588 rows created.

SQL> /

583176 rows created.

SQL> /

1166352 rows created.

SQL> commit;

Commit complete.

SQL>

– Let’s check the size of the table (segment)

SQL> col SEGMENT_NAME form a12
SQL> select bytes/1024/1024 mb, segment_name, segment_type from user_segments;

MB SEGMENT_NAME SEGMENT_TYPE
---------- ------------ ------------------
375 TEST TABLE

— Now, we are generating the dumpfile to load and measure the redo generation

[oracle@ora18c3 ~]$ expdp \"/as sysdba\" directory=home dumpfile=mufalani.dmp logfile=mufalani.log schemas="C##MUFALANI"

Export: Release 18.0.0.0.0 - Production on Sun Jul 7 11:52:38 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mufalani.dmp logfile=mufalani.log schemas=C##MUFALANI
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "C##MUFALANI"."TEST" 323.9 MB 2332704 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/mufalani.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jul 7 11:53:32 2019 elapsed 0 00:00:49

— Verifying the size of the dumpfile

[oracle@ora18c3 ~]$ ls -ltrh mufalani.*
-rw-r-----. 1 oracle dba 325M Jul 7 11:53 mufalani.dmp
-rw-r--r--. 1 oracle dba 1.3K Jul 7 11:53 mufalani.log

— So, now we will check the actual value for redo generation on v$sysstat, before the import to compare after the import

SQL> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME MB
---------------- ----------
redo size 837.06

— Then use the import in regular fashion, and see how much redo generation will be done

[oracle@ora18c3 ~]$ impdp \"/as sysdba\" directory=home dumpfile=mufalani.dmp logfile=imp_no_transform.log schemas="C##MUFALANI"

Import: Release 18.0.0.0.0 - Production on Sun Jul 7 12:01:12 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mufalani.dmp logfile=imp_no_transform.log schemas=C##MUFALANI
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##MUFALANI"."TEST" 323.9 MB 2332704 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Sun Jul 7 12:01:49 2019 elapsed 0 00:00:36

— Check the redo generated again

SQL> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME MB
---------------- ----------
redo size 1215.03

As we can see, to import this table, in regular mode, Oracle generates about 378MB of redo. Let’s drop the table and repeat using this feature to cut redo generation.

SQL> drop table c##mufalani.test purge;

Table dropped.

Then, I will raise the import again using parameter transform=disable_archive_logging:Y and check later the redo generation.

[oracle@ora18c3 ~]$ impdp \"/as sysdba\" directory=home dumpfile=mufalani.dmp logfile=imp_no_transform.log schemas="C##MUFALANI" transform=disable_archive_logging:Y

Import: Release 18.0.0.0.0 - Production on Sun Jul 7 12:14:56 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mufalani.dmp logfile=imp_no_transform.log schemas=C##MUFALANI transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##MUFALANI"."TEST" 323.9 MB 2332704 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Sun Jul 7 12:15:06 2019 elapsed 0 00:00:09

— Check the redo size generation again

SQL> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME MB
---------------- ----------
redo size 1218.01

As you can verify, the time to import was dropped from 36s to 9s and redo generated, just 3MB, very cool. Just to enforce the idea, my database is not running in force logging, note that the database is in archivelog mode, but no in force logging, this reduced of redolog generation is because oracle executes the import in nologging mode, in force logging Oracle will not bypass redolog generation, so, the time and efforts to execute import will be increased.

SQL> select log_mode, FORCE_LOGGING, name from v$database;

LOG_MODE FORCE_LOGGING NAME
------------ --------------------------------------- ---------
ARCHIVELOG NO DB01

— Let’s check the table to see what is the logging mode of the table, after the import is finished. As you can see, logging, which is the normal way (generates redo).

SQL> COL OWNER FORM A12
SQL> COL TABLE_NAME FORM A12
SQL> COL LOGGING FORM A10
SQL> select owner, table_name, logging from dba_tables where owner='C##MUFALANI' ;

OWNER TABLE_NAME LOGGING
------------ ------------ ----------
C##MUFALANI TEST YES

This is an very usefull feature to load huge amount of data into the database, but, import data with this parameter transform, leads to unrecoverable transactions and you must execute a full backup rigth after to finish the import to avoid corrupt blocks in case of recover. I will discuss this situation in other article, soon.

I hope this feature introduced on 12.1, can help you to speed up you imports.

All the best,

Rodrigo Mufalani

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”

RMAN, Allocate channel, CDB, and CLOSE, bug

Category: Database Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

RMAN, Allocate channel, CDB, and CLOSE, bug

Allocate channel for RMAN is used in various scenarios, most of the time is useful when you use tape as device type or you need to use some kind of format. The way to do the allocation not changed since a long time ago, but when you run the database in container mode you can hit a bug that turns your channel unusable. I will show you the bug and how to avoid it with a simple trick.

This bug hit every version since 12 and I discovered it last year when testing some scenarios, but I was able to test and post just recently. It just occurs for CDB databases and exists just one one-off solution published for 12.2. But there is one workaround more useful and works for every version.

The most interesting part is that everything that we made until now when allocate channel will not work. You can search in all doc available for allocate channel since 9i until 19c the first thing that you made after open the run{} is allocate channel. This is the default and recommended in the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/ALLOCATE-CHANNEL.html#GUID-9320BFF7-0728-4B3D-85B9-2184557ECDCE,

https://docs.oracle.com/en/database/oracle/oracle-database/18/rcmrf/ALLOCATE-CHANNEL.html#GUID-9320BFF7-0728-4B3D-85B9-2184557ECDCE

https://docs.oracle.com/cd/A97630_01/server.920/a96566/rcmconc1.htm

My environment for this post is 18c database (last update from April 2019) with just one PDB:

[oracle@orcloel7 ~]$ sqlplus / as sysdba




SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jun 30 23:24:23 2019

Version 18.6.0.0.0




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







Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.6.0.0.0




SQL> show pdbs




    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 ORCL18P                        READ WRITE NO

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.6.0.0.0

[oracle@orcloel7 ~]$

And for rman I show the schema and the backup for one datafile (#12) just to have smaller output.

RMAN> report schema;




Report of database schema for database with db_unique_name ORCL18C




List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    870      SYSTEM               YES     /u01/app/oracle/oradata/ORCL18C/system01.dbf

3    490      SYSAUX               NO      /u01/app/oracle/oradata/ORCL18C/sysaux01.dbf

4    330      UNDOTBS1             YES     /u01/app/oracle/oradata/ORCL18C/undotbs01.dbf

5    290      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCL18C/pdbseed/system01.dbf

6    340      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCL18C/pdbseed/sysaux01.dbf

7    5        USERS                NO      /u01/app/oracle/oradata/ORCL18C/users01.dbf

8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCL18C/pdbseed/undotbs01.dbf

9    300      ORCL18P:SYSTEM       YES     /u01/app/oracle/oradata/ORCL18C/ORCL18P/system01.dbf

10   360      ORCL18P:SYSAUX       NO      /u01/app/oracle/oradata/ORCL18C/ORCL18P/sysaux01.dbf

11   100      ORCL18P:UNDOTBS1     YES     /u01/app/oracle/oradata/ORCL18C/ORCL18P/undotbs01.dbf

12   5        ORCL18P:USERS        NO      /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf




List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    37       TEMP                 32767       /u01/app/oracle/oradata/ORCL18C/temp01.dbf

2    62       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCL18C/pdbseed/temp012019-05-25_23-51-33-086-PM.dbf

3    62       ORCL18P:TEMP         32767       /u01/app/oracle/oradata/ORCL18C/ORCL18P/temp01.dbf




RMAN> list backup of datafile 12 completed after "sysdate - 60/1440";




starting full resync of recovery catalog

full resync complete




List of Backup Sets

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







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4610    Incr 1  40.00K     SBT_TAPE    00:00:02     02-07-2019_00-03

        BP Key: 4611   Status: AVAILABLE  Compressed: YES  Tag: TAG20190701T235918

        Handle: VB$_4142545763_4609I   Media:

  List of Datafiles in backup set 4610

  File LV Type Ckp SCN    Ckp Time         Abs Fuz SCN Sparse Name

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

  12   1  Incr 1714757    02-07-2019_00-03              NO    /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4614    Incr 0  36.50K     SBT_TAPE    00:00:02     02-07-2019_00-03

        BP Key: 4615   Status: AVAILABLE  Compressed: YES  Tag: TAG20190701T235918

        Handle: VB$_4142545763_4609_12   Media:

  List of Datafiles in backup set 4614

  File LV Type Ckp SCN    Ckp Time         Abs Fuz SCN Sparse Name

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

  12   0  Incr 1714757    02-07-2019_00-03              NO    /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf




RMAN>

But look the example below where I try to restore the datafile (maybe because was corrupted – whatever the reason):

RMAN> run{

2> ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/18.6.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/18.6.0.0/dbhome_1/dbs/ra_zdlra credential_alias=zdlra-scan:1521/zdlra:orcl')";

ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;

RESTORE DATAFILE 12;

RECOVER DATAFILE 12;

}3> 4> 5> 6>




allocated channel: CH1

channel CH1: SID=72 device type=SBT_TAPE

channel CH1: RA Library (ZDLRA) SID=8CA6E4CA5A751068E053010310AC0A07




Statement processed

starting full resync of recovery catalog

full resync complete




Starting restore at 02-07-2019_00-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK




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

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

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

RMAN-03002: failure of restore command at 07/02/2019 00:18:08

RMAN-06026: some targets not found - aborting restore

RMAN-06100: no channel to restore a backup or copy of datafile 12




RMAN>

And now this:

RMAN> run{

ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;

ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/18.6.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/18.6.0.0/dbhome_1/dbs/ra_zdlra credential_alias=zdlra-scan:1521/zdlra:orcl')";

RESTORE DATAFILE 12;

RECOVER DATAFILE 12;

} 2> 3> 4> 5> 6>




Statement processed

starting full resync of recovery catalog

full resync complete




allocated channel: CH1

channel CH1: SID=63 device type=SBT_TAPE

channel CH1: RA Library (ZDLRA) SID=8CA703A063E7127DE053010310ACC85B




Starting restore at 02-07-2019_00-18




channel CH1: starting datafile backup set restore

channel CH1: specifying datafile(s) to restore from backup set

channel CH1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf

channel CH1: reading from backup piece VB$_4142545763_4609_12

channel CH1: piece handle=VB$_4142545763_4609_12 tag=TAG20190701T235918

channel CH1: restored backup piece 1

channel CH1: restore complete, elapsed time: 00:00:25

Finished restore at 02-07-2019_00-19




Starting recover at 02-07-2019_00-19




starting media recovery

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




Finished recover at 02-07-2019_00-19

starting full resync of recovery catalog

full resync complete

released channel: CH1




RMAN> ALTER PLUGGABLE DATABASE ORCL18P OPEN INSTANCES=ALL;




Statement processed

starting full resync of recovery catalog

full resync complete




RMAN>

Saw the bug? YES, the bug occurs when you allocate the channel and after execute a close command. When you do the “close”, rman internally cleans everything related for the session (including allocated channels) and you can hit the “RMAN-06100: no channel to restore a backup” or others similar errors like RMAN-06012: channel: CH1 not allocated.

The bug is Bug 28076886 – RMAN: RMAN-06012: “channel: %s not allocated” after ALTER PLUGGABLE DATABASE CLOSE command (Doc ID 28076886.8)

https://support.oracle.com/epmos/faces/DocContentDisplay?id=28076886.8.

The error occurs even for disk channels too (look full example):

RMAN> RUN {

ALLOCATE CHANNEL CH1 DEVICE TYPE DISK;

ALTER PLUGGABLE DATABASE ORCL18P CLOSE;

RECOVER DATABASE PREVIEW;

RELEASE CHANNEL CH1;

}2> 3> 4> 5> 6>




allocated channel: CH1

channel CH1: SID=110 device type=DISK




Statement processed

starting full resync of recovery catalog

full resync complete




Starting recover at 01-07-2019_00-34

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=110 device type=DISK

using channel ORA_DISK_1




no channel to restore a backup or copy of archived log for thread 1 with sequence 5 and starting SCN of 1552035

no channel to restore a backup or copy of archived log for thread 1 with sequence 6 and starting SCN of 1608615

no channel to restore a backup or copy of archived log for thread 1 with sequence 7 and starting SCN of 1669357

no channel to restore a backup or copy of archived log for thread 1 with sequence 8 and starting SCN of 1681533

no channel to restore a backup or copy of archived log for thread 1 with sequence 9 and starting SCN of 1681555

no channel to restore a backup or copy of archived log for thread 1 with sequence 10 and starting SCN of 1682610

List of Archived Log Copies for database with db_unique_name ORCL18C

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




Key     Thrd Seq     S Low Time

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

3516    1    11      A 26-05-2019_19-13

        Name: /u01/app/oracle/oradata/ORCL18C/archivelog/2019_06_30/o1_mf_1_11_gklbz4oh_.arc




recovery will be done up to SCN 1603932

Media recovery start SCN is 1603932

Recovery must be done beyond SCN 1692620 to clear datafile fuzziness

Finished recover at 01-07-2019_00-35




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

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

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

RMAN-03002: failure of release command at 07/01/2019 00:35:01

RMAN-06012: channel: CH1 not allocated




RMAN> RUN {

ALTER PLUGGABLE DATABASE ORCL18P CLOSE;

ALLOCATE CHANNEL CH1 DEVICE TYPE DISK;

RECOVER DATABASE PREVIEW;

RELEASE CHANNEL CH1;

}2> 3> 4> 5> 6>




Statement processed

starting full resync of recovery catalog

full resync complete




allocated channel: CH1

channel CH1: SID=110 device type=DISK




Starting recover at 01-07-2019_00-35




no channel to restore a backup or copy of archived log for thread 1 with sequence 5 and starting SCN of 1552035

no channel to restore a backup or copy of archived log for thread 1 with sequence 6 and starting SCN of 1608615

no channel to restore a backup or copy of archived log for thread 1 with sequence 7 and starting SCN of 1669357

no channel to restore a backup or copy of archived log for thread 1 with sequence 8 and starting SCN of 1681533

no channel to restore a backup or copy of archived log for thread 1 with sequence 9 and starting SCN of 1681555

no channel to restore a backup or copy of archived log for thread 1 with sequence 10 and starting SCN of 1682610

List of Archived Log Copies for database with db_unique_name ORCL18C

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




Key     Thrd Seq     S Low Time

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

3516    1    11      A 26-05-2019_19-13

        Name: /u01/app/oracle/oradata/ORCL18C/archivelog/2019_06_30/o1_mf_1_11_gklbz4oh_.arc




recovery will be done up to SCN 1603932

Media recovery start SCN is 1603932

Recovery must be done beyond SCN 1693086 to clear datafile fuzziness

Finished recover at 01-07-2019_00-36




released channel: CH1




RMAN>

But can be trickier to discover the failure. Look the example below that I try to backup datafile #12:

RMAN> run{

ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/18.6.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/18.6.0.0/dbhome_1/dbs/ra_zdlra credential_alias=zdlra-scan:1521/zdlra:orcl')";

ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;

BACKUP INCREMENTAL LEVEL 1 DATAFILE 12;

}2> 3> 4> 5>




allocated channel: CH1

channel CH1: SID=156 device type=SBT_TAPE

channel CH1: RA Library (ZDLRA) SID=8CBA2C4600D309E8E053010310AC3E17




Statement processed

starting full resync of recovery catalog

full resync complete




Starting backup at 02-07-2019_23-25

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=106 device type=DISK

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf

channel ORA_DISK_1: starting piece 1 at 02-07-2019_23-25

channel ORA_DISK_1: finished piece 1 at 02-07-2019_23-25

piece handle=/u01/app/oracle/oradata/ORCL18C/89BE7E168E3D28BDE053010310AC2497/backupset/2019_07_02/o1_mf_nnnd1_TAG20190702T232547_gkqlyx8j_.bkp tag=TAG20190702T232547 comment=NONE

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

Finished backup at 02-07-2019_23-25




Starting Control File and SPFILE Autobackup at 02-07-2019_23-25

piece handle=/u01/app/oracle/oradata/ORCL18C/autobackup/2019_07_02/o1_mf_s_1012605951_gkqlz1vm_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 02-07-2019_23-25




RMAN> list backup of datafile 12 summary;







List of Backups

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

Key     TY LV S Device Type Completion Time  #Pieces #Copies Compressed Tag

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

4270    B  0  A SBT_TAPE    01-07-2019_00-42 1       1       YES        TAG20190701T003820

4610    B  1  A SBT_TAPE    02-07-2019_00-03 1       1       YES        TAG20190701T235918

4614    B  0  A SBT_TAPE    02-07-2019_00-03 1       1       YES        TAG20190701T235918

5197    B  1  A DISK        02-07-2019_23-25 1       1       NO         TAG20190702T232547




RMAN>

And now:

RMAN> run{

ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;

ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/18.6.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/18.6.0.0/dbhome_1/dbs/ra_zdlra credential_alias=zdlra-scan:1521/zdlra:orcl')";

BACKUP INCREMENTAL LEVEL 1 DATAFILE 12;

}2> 3> 4> 5>




Statement processed

starting full resync of recovery catalog

full resync complete




allocated channel: CH1

channel CH1: SID=105 device type=SBT_TAPE

channel CH1: RA Library (ZDLRA) SID=8CBAA68CCD751232E053010310AC374B




Starting backup at 02-07-2019_23-44

channel CH1: starting incremental level 1 datafile backup set

channel CH1: specifying datafile(s) in backup set

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf

channel CH1: starting piece 1 at 02-07-2019_23-44

channel CH1: finished piece 1 at 02-07-2019_23-44

piece handle=ORCL18C_3pu5ma33_1_1 tag=TAG20190702T234435 comment=API Version 2.0,MMS Version 12.2.0.2

channel CH1: backup set complete, elapsed time: 00:00:03

Finished backup at 02-07-2019_23-44




Starting Control File and SPFILE Autobackup at 02-07-2019_23-44

piece handle=c-551670416-20190702-02 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 02-07-2019_23-44

released channel: CH1




RMAN>

Look that because of the bug, in the first example, the backup was made to disk and not in the tape as requested. In the last, you can see that backup was made correctly in tape. But as you saw, no error was reported.

Conclusion

As you saw a tricky bug can change everything that we already know (and need to do) for a daily basis activity. One simple “close” for a PDB can destroy your backup/restore policy that you use from decades. Besides that even without “RMAN-06100: no channel to restore a backup” or other similar errors like RMAN-06012: channel: CH1 not allocated you can have problems.

Luckily if you are running affected version (everything below 19.2) the workaround is very simple to deploy.

This post was published in my personal blog too: http://www.fernandosimon.com/blog/rman-allocate-channel-cdb-and-close-bug/

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”

Webniar – Patching Like a Hero

Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Webinar - Patching like a Hero

Hi guys !!

Yesterday we had our first Webinar and we are posting the webinar here for you.

Follow the link in the PDF presentation: Click here

See you next time.

André Ontalba – Board Member

Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Login failed due to ORA-28030: ORA-28030: Server encountered problems accessing LDAP directory service

Today I noticed in my environment a lot of messages ORA-28030 in the alert.log of the one database version 18c, previously configured to authenticate on OID (Oracle Internet Directory), event with removal of the configuration, one parameter still configured and that was the reason of the messages.

Also, I checked on listener.log and no failures occurred.

I try to find useful information regargind this error, but just found issues on the oposite direction, people configuring authentication on OID, our issue was cause because of removal, not completely, the configuration of OID.

So, I decided to write this small post about the issue and how to solve.

Before, one of the steps to configure OID is setting the parameter LDAP_DIRECTORY_ACCESS = PASSWORD, so one of my coleagues have done that:

alter system set LDAP_DIRECTORY_ACCESS = 'PASSWORD' scope=both sid='*';

— Note that this parameter is dynamic

To solve the issue, I issue the parameter as default:

alter system set LDAP_DIRECTORY_ACCESS = 'NONE' scope=both sid='*';

Immediately, after the command, no more messages “Login failed due to ORA-28030: ORA-28030: Server encountered problems accessing LDAP directory service”.

I hope this helps you to avoid this messages when you are removing OID authentication of one database.

You can also find more useful information on

Deinstall

https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/uninstalling-or-reinstalling-product.html#GUID-3FC6A4E1-4684-4BAC-9760-DA308A114FE7

Install

https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/installing-product-software.html#GUID-5D47117E-3983-4BED-BADB-CB7DDD63DB34

All the Best,

Rodrigo Mufalani

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited
to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

How to apply patch (PSU) in Oracle Enterprise Manager 13c

Category: Database Author: Leonardo Santos Lopes Date: 5 years ago Comments: 0

How to apply patch (PSU) in Oracle Enterprise Manager 13c

Today in this article I will show you how to apply patch (PSU) in Oracle Enterprise Manager 13c (OEM13c).

Before start:

export ORACLE_HOME=/u01/software/em/middleware

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/OMSPatcher

1 – Download the latest PSU in the MOS (My Oracle Support) and upload the .zip file to the OMS server:

– Enterprise Manager 13.2 Master Bundle Patch List (Includes Plugins: 13.2.1, 13.2.2, 13.2.3) (Doc ID 2219797.1)

2 – Download the required OPatch and OMSPatcher version as describe in the README of the PSU: (13.9.0.0.0)

OPatch:

– https://updates.oracle.com/download/6880880.html

OMSPatcher:

– Patch 19999993: OMSPatcher patch of version 13.8.0.0.2 for Enterprise Manager Cloud Control 13.2.0.0.0

3 – Unzip the patch for the OPatch and apply:

unzip p6880880_139000_Generic.zip -d /home/oracle/6880880

cd /home/oracle/6880880

$ORACLE_HOME/oracle_common/jdk/bin/java -jar /home/oracle/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME

4 – Unzip the new OMSPatcher version:

unzip p19999993_132000_Generic.zip -d /home/oracle/OMSPatcher/

mv /home/oracle/OMSPatcher/ /u01/software/em/middleware/

mv /u01/software/em/middleware/OMSPatcher/

/u01/software/em/middleware/OMSPatcher_old20180427

5 – Validate the OPatch and OMSPatcher version:

  $ORACLE_HOME/OPatch/opatch version




e.g.:

OPatch Version: 13.9.3.2.0

OPatch succeeded.

$ORACLE_HOME/OMSPatcher/omspatcher version




e.g.:

OMSPatcher Version: 13.8.0.0.2

 OPlan Version: 12.2.0.1.6

 OsysModel build: Wed Mar 21 18:20:48 PDT 2018

 OMSPatcher succeeded.

6 – Apply patch in the OPatch agent:

export ORACLE_HOME=/u01/software/em/agent/agent_13.2.0.0.0

$ORACLE_HOME/jdk/bin/java -jar /home/oracle/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME

$ORACLE_HOME/OPatch/opatch version




e.g.:

OPatch Version: 13.9.3.2.0

OPatch succeeded.

7 – Generating the property file:

export ORACLE_HOME=/u01/software/em/middleware

cd $ORACLE_HOME/OMSPatcher/wlskeys

$ORACLE_HOME/OMSPatcher/wlskeys/createkeys.sh -oh $ORACLE_HOME -location

$ORACLE_HOME/OMSPatcher/wlskeys

PS: You will be prompted to enter the credentials for the user: weblogic (Admin Server)

8 – Unzip the file and check prerequisites, as oracle:

unzip p27612395_132000_Generic.zip -d /home/oracle/27612395

cd /home/oracle/27612395

omspatcher apply -analyze -property_file /u01/software/em/middleware/OMSPatcher/wlskeys/properties OMSPatcher.OMS_DISABLE_HOST_CHECK=true

PS: DO NOT copy and paste the “omspatcher” command, it does not support. You must type each command manually.

9 – Stopping and Applying the patch in the OMS Server:

$ORACLE_HOME/bin/emctl stop oms

omspatcher apply -property_file /u01/software/em/middleware/OMSPatcher/wlskeys/properties OMSPatcher.OMS_DISABLE_HOST_CHECK=true

10 – Start OMS Server after apply patch:

$ORACLE_HOME/bin/emctl start oms

See you in the next post!

Leonardo Santos Lopes – https://leonardosantoslopes.wordpress.com/

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

Oracle Certification Matrix

Category: Database Author: Leonardo Santos Lopes Date: 5 years ago Comments: 0

Oracle Certification Matrix

Hi, everyone.

Today in this article I will show you, how to find the certification matrix for one specific Oracle Database Release.

PS: Also applied to any other Oracle Product.

Please find the steps below:

1 – Is mandatory to have an account in the My Oracle Support (MOS) website.

2 – After log in into the My Oracle Support, you will find a menu with a few options. Please click on “Certifications” tab:

3 – You will find in the right side menu, two mandatory fields (Product and Release) to be filled up.
e.g.:

Product:

Release:

After choose the “Product” and “Release” you can click on the button “Search“.

Ps: The “Platform” field is not mandatory to be filled up.

4 – The search results will bring you a list of a few products (Operating Systems, Application Servers, Middleware and etc.) that is certified to be used with the requested release:

See you in the next post!

Leonardo Santos Lopes – https://leonardosantoslopes.wordpress.com/

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

Backing up and restoring your ORACLE HOME

Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Backing up and restoring your ORACLE HOME

I did this small how to with purpose of copy a patched Oracle Home between servers. But you can use it as a ” best practice” and have a regular backup of your Oracle Home in case of you, or other guy on your team, accidentally remove part of or entire files and folders on it.

I will reproduce the steps on my virtual machine, I hope that is not your case, on a production database server, but, nobody knows when shit happens, and you may need this procedure to recover and put online your database after an accident.

As you can see, my database is up and runing in my Oracle 18c home.

[oracle@ora18c3 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 13 18:44:55 2019
Version 18.3.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 PDB2 READ WRITE NO
SQL>
SQL>

The location of my ORACLE HOME is listed for this database on my file /etc/oratab

[oracle@ora18c3 ~]$ cat /etc/oratab
#Backup file is /u01/app/18.0.0/grid/srvm/admin/oratab.bak.ora18c3 line added by Agent
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
db02:/u01/app/oracle/product/18.0.0/db_1:N # line added by Agent
+ASM:/u01/app/18.0.0/grid:N # line added by Agent
db01:/u01/app/oracle/product/18.0.0/db_1:N # line added by Agent

Now, let’s issue a backup of my oracle home, do this procedure with root user. You can backup OH online, no problem at all on the database side to issue this step.

[root@ora18c3 db_1]# tar -cf /media/bkp/dbhome18c_installed.tar .
[root@ora18c3 db_1]#

[root@ora18c3 db_1]# ls -ltrh /media/bkp/*home18c*
-rwxrwx---. 1 root vboxsf 9.3G Jun 13 18:58 /media/bkp/dbhome18c_installed.tar
[root@ora18c3 db_1]#

Now, I will cause some damage to my Oracle Home to return the backup of it.

[oracle@ora18c3 db_1]$ ps -ef | grep smon
oracle 11124 1 0 18:29 ? 00:00:00 asm_smon_+ASM
oracle 20849 1 0 18:41 ? 00:00:00 ora_smon_db01
oracle 31126 19386 0 18:59 pts/0 00:00:00 grep --color=auto smon

I will remove all files on my Oracle home…

[oracle@ora18c3 db_1]$ pwd
/u01/app/oracle/product/18.0.0/db_1
[oracle@ora18c3 db_1]$

[oracle@ora18c3 db_1]$ du -sh .
9.4G .
[oracle@ora18c3 db_1]$

[oracle@ora18c3 db_1]$ rm -rf *
[oracle@ora18c3 db_1]$
[oracle@ora18c3 db_1]$

[oracle@ora18c3 db_1]$ ls -ltrh
total 0
[oracle@ora18c3 db_1]$ pwd
/u01/app/oracle/product/18.0.0/db_1
[oracle@ora18c3 db_1]$

It can happen with anyone, just work in a wrong directory and issue the command rm -rf…

[oracle@ora18c3 db_1]$ sqlplus "/as sysdba"
-bash: /u01/app/oracle/product/18.0.0/db_1/bin/sqlplus: No such file or directory
[oracle@ora18c3 db_1]$

Now my sqlplus and all utilities were gone!!!

[oracle@ora18c3 db_1]$ ps -ef | grep smon
oracle 11124 1 0 18:29 ? 00:00:00 asm_smon_+ASM
oracle 32033 19386 0 19:01 pts/0 00:00:00 grep --color=auto smon
[oracle@ora18c3 db_1]$
[oracle@ora18c3 db_1]$

As you can see, database is down… On alert I can see these messages below

2019-06-13T19:01:18.456507+02:00
Errors in file /u01/app/oracle/diag/rdbms/db01/db01/trace/db01_psp0_20809.trc:
ORA-07274: spdcr: access error, access to oracle denied.
Linux-x86_64 Error: 2: No such file or directory
Errors in file /u01/app/oracle/diag/rdbms/db01/db01/trace/db01_psp0_20809.trc (incident=45633) (PDBNAME=CDB$ROOT):
ORA-7274 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/db01/db01/incident/incdir_45633/db01_psp0_20809_i45633.trc
2019-06-13T19:01:19.845668+02:00
USER (ospid: 20809): terminating the instance due to ORA error 7274
2019-06-13T19:01:19.889661+02:00
System state dump requested by (instance=1, osid=20809 (PSP0)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/db01/db01/trace/db01_diag_20825_20190613130119.trc
2019-06-13T19:01:26.111518+02:00
Instance terminated by USER, pid = 20809

As root user again, let’s restore our backuped Oracle Home to the correct path using these commands below.

[root@ora18c3 ~]# cd /u01/app/oracle/product/18.0.0/db_1/
[root@ora18c3 db_1]#
[root@ora18c3 db_1]#
[root@ora18c3 db_1]# tar -xf /media/bkp/dbhome18c_installed.tar .

Now as Oracle, let’s check the size of the folder..

[oracle@ora18c3 ~]$ cd /u01/app/oracle/product/18.0.0/db_1/
[oracle@ora18c3 db_1]$ du -sh .
9.4G .

As you could see, is the same size as a original one, 9.4Gb, good for us. To garantee that everything is good, I will do a relink on Oracle binaries before to
try open my database again.

[oracle@ora18c3 bin]$ pwd
/u01/app/oracle/product/18.0.0/db_1/bin
[oracle@ora18c3 bin]$ relink all
writing relink log to: /u01/app/oracle/product/18.0.0/db_1/install/relink_2019-06-13_19-17-53PM.log


[oracle@ora18c3 bin]$ cat /u01/app/oracle/product/18.0.0/db_1/install/relink_2019-06-13_19-17-53PM.log | grep -i "Code 1\|Fatal\|Error"
[oracle@ora18c3 bin]$
[oracle@ora18c3 bin]$

Really awesome!!! no errors on the relink file, so it means that we successfully restore our original Oracle home and now we can startup our database again.

[oracle@ora18c3 ~]$ . oraenv
ORACLE_SID = [db01] ? db01
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora18c3 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 13 19:25:41 2019
Version 18.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2432694552 bytes
Fixed Size 8898840 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 PDB2 READ WRITE NO
SQL>

I hope this article helps you to restore this situation that I described in the benning, and, please, include a backup of your Oracle Home in your schedules of backup,
otherwise you will need to reinstall Oracle again and maybe patch all patches that you already applied on your Oracle Home before the crash.

All the Best,

Rodrigo Mufalani

« 1 … 4 5 6 7 »