Rogerio Antonio

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp). In general works fine, it allows an easy way to bring the database even different OS. It is always good to monitor the progress, to know where we are and estimate how long we are from the end. In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios. Starting with a simple one - the track the number of objects loaded during an import operation. When there is a massive amount of errors during the import. Drop and run the import again. The bellow query can be useful also to monitor the number of objects if we are dropping the users. Here, I’m making the assumption there were no other database activity in progress on the last 24 hours. select owner, count(0) from dba_objects where owner in ( select username from dba_users where created...

Let's  distillate  a bit the Fast Recovery Area (FRA)   Central location for backup data Logs Archive logs Backups Control file Location (either) ASM filesystem Allow RMAN Retention policies Parameters ( location and size) DB_RECOVERY_FILE_DEST DB_RECOVERY_DEST_SIZE FRA - Maintenance other notes The maintenance is done automatically, and if is properly sized are not expected issues Use the RMAN commands to remove backups and file copies that are not required. If gets full - database stops RMAN> REPORT OBSOLETE  RMAN> DELETE OBSOLETE  Get Info SQL> show parameter DB_RECOVERY_FILE_DEST NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 3852M SQL> col name form a30 SQL> col value form a30 SQL> select name, v...

Weblogic 11g $find $DOMAIN_HOME -name *.properties | xargs grep IS_CLUSTERED ./user_projects/domains/orwms_sit1/servers/WLS_FORMS/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=false ./user_projects/domains/orwms_sit2/servers/WLS_FORMS/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=false $find $DOMAIN_HOME -name *.properties | xargs grep IS_CLUSTERED ./user_projects/domains/orwms_0719/servers/WLS_FORMS1/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=true ./user_projects/domains/orwms_0718/servers/WLS_FORMS/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=true ./user_projects/domains/orwms_0720/servers/WLS_FORMS/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=true ./user_projects/domains/orwms_0739/servers/WLS_FORMS1/tmp/formsapp/11.1.1/install-plan.properties:IS_CLUSTERED=true Weblogic 12c # # Other way # grep -i cluster $DOMAIN_HOME/config/config.xml | sort -u </cluster> <cluster> <cluster-constraints-enabled>fal...

Weblogic 11g # # start weblogic # cd $WL_DOMAIN nohup ./startWebLogic.sh & # # start Node Manager # cd $WL_HOME/server/bin nohup ./startNodeManager.sh & Weblogic 12g # # auto login # mkdir -p $DOMAIN_HOME/servers/AdminServer/security echo "username=weblogic" > $DOMAIN_HOME/servers/AdminServer/security/boot.properties echo "password=Welcome0" >> $DOMAIN_HOME/servers/AdminServer/security/boot.properties # #start Node Manager # cd $DOMAIN_HOME/bin nohup ./startNodeManager.sh > ~/startNodeManager.nohup.out 2>&1 & # # start weblogic # cd $DOMAIN_HOME/bin nohup $DOMAIN_HOME/startWebLogic.sh > ~/startWebLogic.nohup.out 2>&1 & # # start MS # nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh soa_server1 http://wls-server:7001 > ~/soa_server1.nohup.out 2>&1 & nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh soa_server2 http://wls-server:7001 > ~/soa_server2.nohup.out 2>&1 & nohup $DOMAIN_HOME/bin/startManaged...

English Version Oracle Retail Allocation e um dos produtos de Oracle Retail ( MOM), embora a base de dados seja MOM, os accessos e a seguranca sao diferentes dos outros produtos MOM. Neste post mosto como criar e configurar um utilizador para Allocations. alter session set current_schema = &&rms_schema INSERT INTO ALC_USERS (USER_ID,USER_NAME,USER_PASSWORD,FIRST_NAME,LAST_NAME,MIDDLE_INITIAL,USER_TYPE,LANGUAGE,COUNTRY) VALUES  (ALC_USERS_SEQ.NEXTVAL,'JSMITH','JackButlandGK23','Joe','Smith',NULL,'A','EN','US'); commit; -- associate departments declare cursor c_deps is select dept from deps; alloc_user_id alc_users.user_id%type; begin select user_id into alloc_user_id from alc_users  where user_name  = 'ACCSERV2'; for  c1 in c_deps loop insert into alc_user_depts(user_depts_id,user_id,dept) values (alc_user_depts_seq.nextval,alloc_user_id,c1.dept); end loop; end; commit; Obrigado pela leitura, espero que este po...

EnglishVersion Apos adicionar um novo warehouse / WMS a solucao de Oracle Retail, ou refrescar um ambiente, e recomendavel testar a integracao RMS --> WMS antes de passar a bola ao negocio or project team. Nest post partilho uma forma facil de fazer esse teste - e como monitorizar a mensagem. Na base de dados do RMS, Alterar a descricao do Item : 101715654 select item, item_desc from item_master where item ='101715654' ITEM ITEM_DESC 101715654 ITEM XPTO update rms.item_master set item_desc = 'ITEM XPTO - DESC CHANGED' where item='101715654'; select item, item_desc from item_master where item ='101715654'; commit; Na base de dados do RIB Validar se a mensagem esta na queue/topic : 101715654 set lines 300 col enq_time form a30 col deq_time form a10 col msg form a100 set longchunksize 30000 set long 30000 select msgid,enq_time, deq_time, r.user_data.text_lob msg from ribaq.etitemsfromrmstable r where enq_time > sysdate - 1 and r.user_data.text_lob ...

E a tal query que nao termina And about that query that never ends ... -- --  General -- 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; -- --  RMAN Specific -- select sid, serial#,opname, context, sofar, totalwork,        round(sofar/totalwork*100,2) "%_complete" from v$session_longops where   lower(opname) like 'rman%'   and opname not like '%aggregate%'   and totalwork != 0   and sofar <> totalwork; select    to_char(start_time,'dd-mon-yy hh24:mi') "backup started",    opname,    sofar,    totalwork,    elapsed_seconds/60 "elapse (min)",    round(sofar/totalwork*100,2) "complete%" from v$session_longops where   lower(opname) like 'rman...

English Version Neste post partilho algumas queries que usei para monitorizar o processamento de mensagens no 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%' Testar Integracao - RMS > WMS Uma forma facil de testar a integracao (RIB) apos adicionar um novo warehouse (WMS), e actualizar um Item no RMS e validar se as alteracoes estao a ser transmitidas ao WMS, no exemplo seguinte mostro como fazer isso, e os tabelas/queues envolvidas. Item : 101715654 -- RMS DB SQL> select  item, item_desc  from item_master wher...

English Version Por vezes e necessario fazer trace a sessao desde inicio, nessas situacoes e util usar um  logon trigger ex: baches de Oracle Retail / RPM / RMS Em seguida partilho tres logon triggers , que podem ser uteis nessas circunstancias, o primeiro usando o hostname do cliente. 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_...

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...

Versao Portuguesa PT  :  Necessito mesmo  hard coded   na minha script ? EN :   Do I really need to  hard coded   my grant script ? set serveroutput on size 1000000; declare cursor c1 is ( select 'grant select on  wes.' || table_name  || ' to wes_ro'  cmd from dba_objects where owner like 'WES' and object_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','VIEW','MATERIALIZED VIEW','SEQUENCE') ); begin for i in c1 loop dbms_output.put_line(' ran : ' || i.cmd); execute immediate i.cmd ; end loop; end; / Thank you for reading, hope this post was helpful. Rogerio

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...

English Version Este post saiu da preparacao para o OCP - Oracle Certified Professional . Isto e simples , Oracle trabalha com logs, todos os DMS/DDL vao para a log. chamados redo logs, que sao escritos de forma circular. Para evitar perda de informacao podem ser archivados antes da re-escrita. Entre outras razoes, no caso de necessidade de recuperar a base de dados the forma tradicional sao necessarios 2 passos : 1) Restore - restaurar os data files e control files 2) Recover - Aplicar logs, neste caso convem ter os "archives" De seguida mostro como fazer isso, nota importante, isto deve ser feito com a base de dados em estado consitente. Exemplo, apos um "shutdown immediate". SQL> select startup_time from  v$instance; STARTUP_Time --------- 19-DEC-12 SQL> select dbid,name,log_mode,flashback_on from  v$database;       DBID        NAME       LOG_MODE          FLASHBACK_ON ----------          ---------      ------------               ------------------ 1272...

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 --...

Gestao de Processos Este foi o meu primeiro post, ha operacoes na base de dados demoram muito tempo a executar, e aconselhavel correr no lado do servidor. O prefixo nohup assegura que o commando continua a correr. O uso do "&" envia o comando para "background", libertando assim a linha de commando. Em seguida formas  formas de gerir esta abordagem.   Run - nohup + background + foreground $ nohup sqlplus $UP @update_data.sql "&" [2] 155548  nohup: ignoring input and appending output to `nohup.out' $ jobs [1]+  Running                 nohup sqlplus $UP @update_data.sql "&" $ fg 1 nohup sqlplus $UP @update_data.sql Ctrl+C Run - nohup + grep + kill $ nohup sqlplus $UP @update_data.sql & [1] 294427 >oracle@rogerioantonio.com> nohup: ignoring input and appending output to `nohup.out'   $ ps -ef | grep -i update_data.sql oracle 294427  80866 93 05:30 pts/1    00:00:16 sqlplus                    @update_data.sql oracle 29543...