Rogerio Antonio

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are : Is the database doing something? How long the database takes to complete the task? There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done. Starting with a first example, a query for general propose . set lines 240 col message form a100 col opname form a50 select * from ( select round(l.sofar/l.totalwork*100,2) as complete, --l.* l.sid, --l.opname, l.message, l.start_time, l.time_remaining/60 "minuts remaining", l.elapsed_seconds from gv$session_longops l where totalwork !=0 ) where complete < 100; The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery. select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complet...

In this post, I will show how to copy files from / to Oracle Automatic Storage Management (Oracle ASM). A typical example example is when we do TTS / a.k.a transportable tablespaces. I'm showing below how to do that using asmcmd cp command. [oracle@databaseserver01]$ $ asmcmd ls +DATA MOM/ MOMSPIT/ MOMUAT3A/   [oracle@databaseserver02] $ asmcmd cp +DATA/standby_control.ctl sys@databaseserver01.1522.+ASM:+DATA/standby_super_mario.ctl Enter password: *********   [oracle@databaseserver01][$ asmcmd ls +DATA MOM/ MOMSPIT/ MOMUAT3A/ standby_super_mario.ctl.305.859841709 More about this: asmcmd Thank you for reading, hope this post was helpful. Rogerio

In this post I'm sharing how to address of the most repetitive tasks after fresh build or clone a VM. Eg. for database   pre install tasks . First of all , I will how to change the hostname. Next, I will setup the local naming resolution. At end, how to play with the firewall. Change hostname - the hostname is key including for server provisioning and orchestration, the server name can be changed using the hostnamectl command. Lets see bellow some examples. root@Unknown-08-00-27-26-48-7d # hostnamectl status Static hostname: localhost.localdomain Transient hostname: Unknown-08-00-27-26-48-7d Icon name: computer-vm Chassis: vm Machine ID: d5a9e3e4d118804089e345228a189582 Boot ID: f76e3fc1c76f49028a1583b90eb01511 Virtualization: kvm Operating System: Oracle Linux Server 7.7 CPE OS Name: cpe:/o:oracle:linux:7:7:server Kernel: Linux 4.14.35-1902.3.2.el7uek.x86_64 Architecture: x86-64 root@Unknown-08-00-27-26-48-7d # hostnamectl set-hostname wls.example.com root@Unknown-08-00-27-26-48...

Versao Portuguesa In Oracle Retail the warehouses ( and other facilities as stores ) have a unique identify. Specific to the WMS, RIB sends/redirects specific information using that ID . That information needs to be in : RMS, TAFRs file and WMS. The bellow query can be used to identify the WMS ID . set lines 20 col description a30 col description form a30 set lines 200 select l.facility_id,description,dc_dest_id from transshipment_setup t, facility l where l.facility_id = t.facility_id; Thank you for reading, hope this post was helpful. Rogerio Futher Reading : https://docs.oracle.com/cd/E82085_01/1303/operations_guide/Chapter%207%20-%20Message%20and%20Routing%20(TAFR).htm https://docs.oracle.com/cd/E12456_01/rwms/pdf/141/html/impg/wms-integration.htm

Versao Portuguesa In this post i'm sharing some queries I used to monitoring the messages processing in RIB. -- find all items queues SQL> select * from user_queues where queue_type = 'NORMAL_QUEUE' and name like '%ITEM%' --- find all items to process SQL> select * from ETITEMSTLFROMRIBTABLE -- queue subscribers SQL> select * from AQ$_ETITEMSTLFROMRIBTABLE_S -- queue historic SQL> select * from AQ$_ETITEMSTLFROMRIBTABLE_H where msgid = 'FE4C339218D830B1E0440024813309CD' --- find all tables/queue tables SQL> select * from dba_tables where table_name like '%ETITEMSTLFROMRIB%' Tesing the Integration - RMS > WMS An easy way to test the integration (RIB) , after add a new warehouse (WMS), is to choose an Item in RMS , update the description, commit the changes and validate if they are being cascaded to the WMS. After the validation put back the original description . Eg : Item : 101715654 -- RMS DB SQL> select  item, item_desc...

Versao Portuguesa Sometimes is useful to trace the session from the beginning, for that is useful to use a logon trigger eg RPM batches I'm sharing three logon trigger examples to help in that situations, the first one using the client hostname, if you know it.  SQL> drop trigger sys.set_trace_rpm; SQL> create or replace trigger sys.set_trace_rpm after logon on database when (user in ('RPM_OAS')) declare retail_module varchar(200); begin select module into  retail_module from gv$session where sid =  upper(sys_context('userenv','sid')) and (osuser is null and machine like 'rpm_app_server%'); execute immediate 'alter session set statistics_level=all';  execute immediate 'alter session set max_dump_file_size=unlimited';  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';  execute immediate 'alter session set tracefile_identifier=''RA_RPM'''; e...

Control file In this post I approach RMAN Configuration, Metadata, Catalogues RMAN always stores its metadata in the target database  control file Whether you use or not the Recovery Catalog Oracle stores metadata into the targer database control file RMAN info on control file is is aged based on CONTROL_FILE_RECORD_KEEP_TIME Single point control ( control file) Does not required another DB Recovery Catalog keep info for long time single point to store many databases RESYNC CATALOG, syncronize the control file metadata with the recover catalog Can store "RMAN backup scripts" in the catalog ... is not possible do it on the control file Recovery Catalog control views RC_* Allow history commands BACKUP .. KEEP UNTIL TIME - keep the backup until point that referrers the configured retention police  BACKUP ... KEEP FOREVER - keep the backup forever, until manually removed REPORT SCHEMA ... AT - show the structure at specific time period Create a RMAN Catalog --1 create tablespace...

Versao Portuguesa Quick Summary This post was initially written for 11g - how to setup the database environment to start the database instance. Post revised for 12c, in this case added some validations related with new functionalities related with conatiner databases . Some assumptions, the database was created with dbca , or if manually the installation was completed with the /etc/oratab registration. The easiest way to setup the environmnt variables is to run " . oraenv " in command line , let see the output: 11g - Setup environment variables [oracle@oracle ~]$ . oraenv ORACLE_SID = [orcl] ? The Oracle base for ORACLE_HOME=/u01/app/oracle/Database/11.2.0/orcl is /u01/app/oracle [oracle@oracle ~]$ echo $ORACLE_SID orcl 11g - Start the server Start the database instance, mount and open the database [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 19 20:02:16 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connect...

Versao Portuguesa This post comes out from the practice to learn and preparation the subjects for OCP - Oracle Certified Professional . In simple words, Oracle works with logs, all DMS/DDL are written in log file, know as redo logs, the written happens in circular way. So the files are redo file contents will be replaced, with the transactions. In order to keep that info, Oracle allows to archive the contents. It has a few reasons to archive the redo log, for now let focus on one, teh need of recover the database. I mean some thing happened and is need to put the database back. The traditional method is done in two steps. 1) Restore - restore data files and control files 2) Recover - Apply logs, in this case the archive logs .. and eventually redo log Let's see how to do that, but for now a note, the database should be in mount state and in a consistent manner, eg after a "shutdown immediate". SQL> select startup_time from  v$instance; STARTUP_Time --...