Replace corrupted undo tablespace SOA 10g

In certain cases, Oracle Products doesn't get corrupted, because bad stopping or something, this commands help us to change the undo and common tablespaces that fail commonly


In this case SOA doesn't start up because the undo tbs was currupted.

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

--Resize undo datafile
--ALTER DATABASE DATAFILE '/u01/oradata/soa/undotbs02.dbf' RESIZE 10000M;

--Create undo tablespace
--CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/soa/undotbs02.dbf' SIZE 1000M AUTOEXTEND ON;

--Drop undo tablespace
--drop tablespace UNDOTBS2;

select * from user_tablespaces;

--Drop undo tablespace including contents and datafiles
--DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

--Parameters
select * from  V$OPTION;


select
   file_name,
   bytes,
   autoextensible
from
   dba_data_files;

select * from
all_users;

select * from user_tablespaces;


SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS


ORABPEL


ALTER tablespace ORABPEL

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe2.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe3.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe4.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe5.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter TABLESPACE SYSAUX add DATAFILE  '/u01/oradata/soa/sysaux02.dbf' SIZE 550M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

alter TABLESPACE SYSAUX add DATAFILE  '/u01/oradata/soa/sysaux03.dbf' SIZE 550M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/zucprod/undotbs02.dbf' SIZE 30000M AUTOEXTEND ON;

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
  '/u01/oradata/zucprod/undotbs01.dbf' SIZE 30000M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;


ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

UNDOTBS2

ODI 12c Loop PLSQL Procedure

Here's the procedure I commonly use to implement a odi loop in package.!

create or replace function sys.set_initial_data (P_TOKEN varchar2, P_VALUE varchar2, P_ROW_ID number, p_session number) return varchar2
is
PRAGMA AUTONOMOUS_TRANSACTION;

res varchar2(50);

cursor get_datap is
select *  from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN  'R' else 'P' end from  dual)
and status = 'P';
--and id_host = nvl(null,id_host);

vget_datap get_datap%rowtype;

cursor get_datap_id is
select *  from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN  'R' else 'P' end from  dual)
and id_host = nvl(P_ROW_ID,id_host);
--and status = 'P'

vget_datap_id  get_datap_id%rowtype;


begin

if p_token = 'INIT' then
delete from process_data_odi;
insert into process_data_odi
select p_session, id, 'P', null,IP, puerto from table_info_host;

commit;

return 'OK';
 
elsif p_token = 'GET' then


    open get_datap_id;
    fetch get_datap_id  into vget_datap_id;
    close get_datap_id;


  IF P_VALUE = 'PORT' then
    res := vget_datap_id.PUERTO;
  elsif P_VALUE = 'HOST' then
    res := vget_datap_id.HOST;
  elsif P_VALUE = 'ROW' then


open get_datap;
fetch get_datap into vget_datap;
close get_datap;


    res := vget_datap.ID_host;
 
  update process_data_odi set status = 'R'
  where id_host = vget_datap.ID_host;

  commit;  

  end if;

elsif p_token = 'SET' then

  update process_data_odi set status = 'T'
  where id_host = P_ROW_ID;

  commit;

return 'OK';

end if;

return nvl(RES,'ERR');
end;


SELECT *  FROM process_data_odi;

select set_initial_data('GET','PORT',2,1111) from dual;

select set_initial_data('GET','HOST',2,1111) from dual;

select set_initial_data('GET','ROW',null,1111) from dual;

select set_initial_data('SET',null,1,1111) from dual;

select set_initial_data('INIT',NULL,null,1111) from dual;

commit;

Configure CDC ODI 12C with Reusable Mappings

1.- Identify the source and target locations
2.- Create your source model, and target model
3- In my case I created the CDC_Test model connected to a Database 11g XE Edition and my target is a Database 12c EE

Goldengate is a oracle product to syncronize legacy systems or databases using the log files, but is quite expensive, in this case we are going to see, how to do a sync between two systems using just ODI 12C,

Using CDC Simple  Mode:

1.- Create a Project and import JKM Oracle Simple

2.- Change de Model of CDC_Test to Simple

3.- Do the Reverse-Engineering for the tables to add to CDC, in this case the table I chose was SRC_CITY.

4.- Once you have the datastore in your data model, Now configure the CDC, right clic over the datastore SRC_CITY and Add to CDC.

5.- Add a subscriber, right clic over datastore and navigate to subscriber, Add subscriber

6.- Add the subscriber SUNOPSIS

7.- Start Journal, select the subscriber, then session is prompted to start, clic ok.
Note:
The steps to undo this are (Unsubscribe, Drop  Journal, Remove from  CDC)

8.- So far, we have configured the basic CDC Simple, let's remember this is intrusive, because it creates triggers over the source tables and may affect the performance, so check carefully.

9- Test

10.- Now let's do this automatically using a package, mapping (ODI Interface in ODI 11G) and reusable mapping

11.- Create a Reusable Mapping, just drag the source table (The one you just added to CDC, see above), and also drag an output signature and do the mapping.

12.- Create a mapping in ODI and drag the reusable mapping created in the last step.

13.- Drag the target table and do the mapping.

14.- Choose the Optimization context to Development
15.- Configure the Journalize Data (select CheckBox)
16.- In the Logical Section change the target to  Incremental Update
17.- In the Physical, select the target TRG_CITY, and in the section Integrated Knowledge Module change to IKM Oracle Incremental Update

Look like this:

18.- Create a package to automatize this.
19.- Drag a OdiWaitForLogData component

OdiWaitForLogData "-CONTEXT=DEVELOPMENT" "-GLOBAL_ROWCOUNT=1" "-LSCHEMA=GG_SOURCE" "-OPTIMIZED_WAIT=AUTO" "-POLLINT=1000" "-SUBSCRIBER_NAME=SUNOPSIS" "-TIMEOUT=0" "-TIMEOUT_WITH_ROWS_OK=YES" "-TABLE_NAME=SRC_CITY"

20.- Drag the interface into the package and make a connection to the OdiWaitForLogData and viceversa

21.- Test the package.

SELECT * FROM SALES_DATA.SRC_city;

SELECT count(*) FROM SALES_DATA.SRC_city;

SELECT * FROM SALES_DATA.SRC_city;

delete from SALES_DATA.SRC_city where city_id = 100;

commit;

insert into SALES_DATA.SRC_city values (100,'Acapulco',20,123456);

commit;

Install and configure Admin Tool with OBIEE 12C - IAAS Cloud

First of all you need to install the client required, the file:

Setup_BI_Client_12.2.1.0.0_Windows.X64

you can download it en the oracle web page, or e-delivery.

http://download.oracle.com/otn/nt/bi/1221/Setup_BI_Client_12.2.1.0.0_Windows.X64.zip

once you have installed, we proceed to configure it in order to allow connection,

we need first, configure tnsnames.ora.

1.- Navigate to C:\Oracle\Middleware\Oracle_Home\network\admin
(if the folder doesn't exist create it)

2.- Create file tnsnames.ora pointing out to the database you want to connect.

example:

# tnsnames.ora Network Configuration File mntopc_dataproduct12.1.0db_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.

CLOUDCDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db_private)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cloudcdb1)
    )
  )

CLOUDCDB1_PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db_private)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

3.- Configure DNS System Entries

The port 9514

4.- Select the tables as you desire and build your model

Linux IAAS, Usefull commands

One you have attached a volume here you have usefull commands:

Disk information:

lsblk
blkid

Mount: sudo mount /dev/xvdc /mnt/opc_data

Format : sudo mkfs -t ext3 /dev/xvdc

Install if needed:
sudo yum install e4fsprogs

Create folder if needed:
sudo mkdir /mnt/opc_data

mount as default:
vi /etc/fstab

add following line:
/dev/xvdc               /mnt/opc_data        ext3    defaults        0 0

Change user and group one command:
sudo chown opc:opc opc_data/

Check if process is on startup:
chkconfig --list | grep vncserver
"vncserver       0:off   1:off   2:on    3:on    4:on    5:on    6:off"

install vncserver if not installed:
yum install tigervnc-server

Put vncserver on startup:
sudo chkconfig vncserver on

Configure vncserver once installed (modify according your settings)
cat  /etc/sysconfig/vncservers

VNCSERVERS="2:opc 3:oracle"
VNCSERVERARGS[2]="-geometry 1280x1024 -nolisten tcp -localhost"
VNCSERVERARGS[3]="-geometry 1280x1024"

Restart Service

sudo service vncserver start
sudo service vncserver stop

sudo service vncserver status

Use vncpasswd to change password users

vncpasswd opc

If you don't have xterm install it
sudo yum install xterm

Enable X11Forwarding

sudo vi /etc/ssh/sshd_config

Restart sshd

sudo /etc/init.d/sshd restart

With operative systems with no graphic interface, but you want enable vncserver:

gconftool-2 -s -t bool /apps/gnome-screensaver/lock_enabled false

vncserver :3 -depth 16 -alwaysshared -geometry 1200x1024 -s off

sudo yum install xorg-x11-twm

sudo yum install xterm

sudo yum install xsetroot

sudo yum install xorg-x11-apps

Add LC_ALL=en_US; export LC_ALL=en_US in .bashrc or in .bash_profile 

sudo yum install liberation-sans-fonts

same procedure as vncserver

search text in files recursively in a folders and subfolders:
grep -rl 'windows' ./ | xargs sed -i 's/windows/linux/g'

grep -rnw . -e 172.28.88.111 --exclude-dir={*.out,*.log}
grep -rnw . -e 172.28.88.111 --exclude={*.out*,*.log*}

using escape character: 
grep -rl '/opt/oracle/obiee/user_projects' ./ | xargs sed -i 's/\/opt\/oracle\/obiee\/user_projects/\/opt\/oracle\/admin\/user_projects/g'

Create aliases .bash_aliases file, Example:
alias startobi='sh $ORACLE_BI_HOME/bitools/bin/start.sh'
alias stopobi='sh $ORACLE_BI_HOME/bitools/bin/stop.sh'

Disable firewall
systemctl disable firewalld.

.bash_profile example

Sometimes, operative systems users don't come with bash profile file, here you have an example of
this file and common environment variables in a Oracle installation.


# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=cbaf0b
export ORACLE_UNQNAME=cloudcdb1
export ORACLE_BASE=/mnt/opc_data
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=cloudcdb1

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export JAVA_HOME=/usr/java/jdk1.8.0_91
export JRE_HOME=/usr/java/jdk1.8.0_91/jre
export JDK_HOME=/usr/java/jdk1.8.0_91

export PATH=$JAVA_HOME/bin:$PATH

Cyaaa !!

Install OBIEE 12c IAAS Oracle Cloud

Step  1: Java

bash-4.1$ sudo rpm -Uvh jdk-8u91-linux-x64.rpm

Preparing...  ########################################### [100%]

jdk1.8.0_91 ########################################### [100%]

Step  2: Install Infra

To fix this error:

sudo yum install gcc-4.4*

sudo yum install glibc-devel-2*

Step 3: Create the repositories

cd /mnt/opc_data/Middleware/obiee12c/oracle_common/bin

./rcu

Step  4: Configure domain

Change location to and execute config.sh

/mnt/opc_data/Oracle/Middleware/bi/bin

Then you will be able to access Bi system:

http://xxx.xx.xx.xx:9502/analytics/saw.dll?bipublisherEntry&Action=new&itemType=.xma