Monday, March 14, 2016

Install or upgrade Oracle APEX 5.0.3 for ORACLE DATABASE 12C on Linux

Step 1: Create tablespace

create tablespace apex  datafile '/u01/app/oracle/oradata/PTUD/datafile/apex.dbf' size 100M autoextend on next 10M;

Step 2: Download Oracle Apex 5.0.3.00.03 from Oracle or My Google Drive

or

Step 3: extract file apex_5.0.3_en.zip to directory: /u01/setup

[oracle@VTGDB.COM ~]$cd /u01/setup
[oracle@VTGDB.COM setup]$unzip apex_5.0.3_en.zip
[oracle@VTGDB.COM setup]$ ls
apex  apex_5.0.3_en.zip

Step 4:  Go to the apex directory ( /u01/setup/apex), connect sys as sysdba then run sql scripts:

[oracle@VTGDB.COM ~]$cd /u01/setup/apex

[oracle@VTGDB.COM ~]$sqlplus / as sysdba
SQL > @apexins.sql apex apex temp /i/

Step 5: Run script apex_epg_config.sql /u01/setup

[oracle@VTGDB.COM ~]$cd /u01/setup/apex
[oracle@VTGDB.COM apex]$sqlplus / as sysdba
SQL > @apex_epg_config.sql /u01/setup

Step 6: Unlock user account oracle


[oracle@VTGDB.COM apex]$sqlplus / as sysdba
SQL > alter user APEX_PUBLIC_USER account unlock;
SQL > alter user APEX_050000 account unlock;
SQL > alter user ANONYMOUS account unlock;

Step 7: Verifying Oracle XML DB HTTP Port and set it to 8080

[oracle@VTGDB.COM apex]$sqlplus / as sysdba
SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

GETHTTPPORT
-----------
              0
SQL> EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.


Step 8: Configure Apex application


[oracle@VTGDB.COM apex]$ sqlplus  / as sysdba
SQL> @apxconf.sql

      PORT
----------
      8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.


================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" exists.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Changed password of instance administrator ADMIN.

Enter a port for the XDB HTTP listener [      8080]
...changing HTTP Port
SQL>

Step 9: Access your Apex application web:


http://YOUR_HOST:8080/apex
workspace: internal
username: ADMIN ( uppercase)

pass: YOUR_PASS


After you login successfully, you have your interface following:

Monday, March 7, 2016

How to create schemas for BI Publisher 11G without RCU

Contents



Prerequisite

Grant exempt access policy to user system in order to fix ORA 39181. ( VERY IMPORTANT!).
[oracle@PTUD-SingleSignOn dpdump]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 7 15:07:35 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> GRANT EXEMPT ACCESS POLICY to System;

Grant succeeded.

SQL> commit;

Commit complete.

Download file dump from my google drive:


List of tablespaces

Create all of tablespaces following:
Name
SQL Create
RMS11G_BIPLATFORM
CREATE  TABLESPACE "RMS11G_BIPLATFORM" EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  SEGMENT SPACE MANAGEMENT  AUTO  DATAFILE '/u01/app/oracle/oradata/RMS11G_biplatform.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE 1024M;
RMS11G_IAS_TEMP
CREATE  TEMPORARY  TABLESPACE "RMS11G_IAS_TEMP" EXTENT MANAGEMENT LOCAL  TEMPFILE '/u01/app/oracle/oradata/RMS11G_iastemp.dbf' SIZE 100M;
RMS11G_IAS_IAU
CREATE  TABLESPACE "RMS11G_IAS_IAU" EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  SEGMENT SPACE MANAGEMENT  AUTO  DATAFILE '/u01/app/oracle/oradata/RMS11G_ias_iau.dbf' SIZE 60M REUSE  AUTOEXTEND ON NEXT 10240K MAXSIZE  UNLIMITED;
RMS11G_MDS
CREATE  TABLESPACE "RMS11G_MDS" EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  SEGMENT SPACE MANAGEMENT  AUTO  DATAFILE '/u01/app/oracle/oradata/RMS11G_mds.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 1000M;

List of schemas

Create all of schemas following:

Name
SQL Create
RMS11G_BIPLATFORM
create user "RMS11G_BIPLATFORM" identified by ******* profile "DEFAULT" account unlock default tablespace  "RMS11G_BIPLATFORM" temporary tablespace "RMS11G_IAS_TEMP";
       
grant CREATE SEQUENCE to "RMS11G_BIPLATFORM";
grant UNLIMITED TABLESPACE to "RMS11G_BIPLATFORM";
grant "CONNECT" to "RMS11G_BIPLATFORM";
grant "RESOURCE" to "RMS11G_BIPLATFORM";
grant CREATE VIEW to "RMS11G_BIPLATFORM";
RMS11G_IAU
create user "RMS11G_IAU" identified by ******* profile "DEFAULT" account unlock default tablespace  "RMS11G_IAS_IAU" temporary tablespace "RMS11G_IAS_TEMP";
       
grant UNLIMITED TABLESPACE to "RMS11G_IAU";
grant "CONNECT" to "RMS11G_IAU";
grant "RESOURCE" to "RMS11G_IAU";
RMS11G_IAU_APPEND
create user "RMS11G_IAU_APPEND" identified by ******* profile "DEFAULT" account unlock default tablespace  "RMS11G_IAS_IAU" temporary tablespace "RMS11G_IAS_TEMP";
       
grant "CONNECT" to "RMS11G_IAU_APPEND";
RMS11G_IAU_VIEWER
create user "RMS11G_IAU_VIEWER" identified by ******* profile "DEFAULT" account unlock default tablespace  "RMS11G_IAS_IAU" temporary tablespace "RMS11G_IAS_TEMP";
       
grant "CONNECT" to "RMS11G_IAU_VIEWER";
RMS11G_MDS
create user "RMS11G_MDS" identified by ******* profile "DEFAULT" account unlock default tablespace  "RMS11G_MDS" temporary tablespace "RMS11G_IAS_TEMP";
       
grant CREATE TABLE to "RMS11G_MDS";
grant CREATE SEQUENCE to "RMS11G_MDS";
grant CREATE PROCEDURE to "RMS11G_MDS";
grant "CONNECT" to "RMS11G_MDS";
grant CREATE TYPE to "RMS11G_MDS";

List of dump Files

Import data use SQL dump.
Name
SQL Dump
RMS11G_BIPLATFORM.dmp
expdp system/*********@ptud schemas=RMS11G_BIPLATFORM dumpfile=RMS11G_BIPLATFORM.dmp logfile=exRMS11G_BIPLATFORM.log version=11.2.0.4.0

impdp system/******@service_name directory=your_directory
dumpfile= RMS11G_BIPLATFORM.dmp
logfile= RMS11G_BIPLATFORM.log
RMS11G_IAU.dmp
expdp system/**********@ptud schemas=RMS11G_IAU dumpfile=RMS11G_IAU.dmp logfile=exRMS11G_IAU.log version=11.2.0.4.0
impdp system/******@service_name directory=your_directory
dumpfile= RMS11G_IAU.dmp
logfile= RMS11G_IAU.log
RMS11G_IAU_APPEND.dmp
expdp system/********@ptud schemas=RMS11G_IAU_APPEND dumpfile=RMS11G_IAU_APPEND.dmp logfile=exRMS11G_IAU_APPEND.log version=11.2.0.4.0
impdp system/******@service_name directory=your_directory
dumpfile= RMS11G_IAU_APPEND.dmp
logfile= im RMS11G_IAU_APPEND.log
RMS11G_IAU_VIEWER.dmp
expdp system/********@ptud schemas=RMS11G_IAU_VIEWER dumpfile=RMS11G_IAU_VIEWER.dmp logfile=exRMS11G_IAU_VIEWER.log version=11.2.0.4.0
impdp system/******@service_name directory=your_directory
dumpfile= RMS11G_IAU_VIEWER.dmp
logfile= im RMS11G_IAU_VIEWER.log
RMS11G_MDS.dmp
expdp system/**********@ptud schemas=RMS11G_MDS dumpfile=RMS11G_MDS.dmp logfile=exRMS11G_MDS.log version=11.2.0.4.0
impdp system/******@service_name directory=your_directory
dumpfile= RMS11G_MDS.dmp
logfile= imRMS11G_MDS.log

Unlock and set password for schemas:


Name
SQL Dump
RMS11G_BIPLATFORM
Alter user RMS11G_BIPLATFORM account unlock identified by ********;
RMS11G_IAU
Alter user RMS11G_IAU account unlock identified by ********;
RMS11G_IAU_APPEND
Alter user RMS11G_IAU_APPEND  account unlock identified by ********;
RMS11G_IAU_VIEWER
Alter user RMS11G_IAU_VIEWER account unlock identified by ********;
RMS11G_MDS
Alter user RMS11G_MDS account unlock identified by ********;

Contact:


Any question feedback thonh15@gmail.com

Friday, March 4, 2016

How to backup & restore timesten


 

My environment



OLD Timesten Server
New Timesten Server
Timesten Home
/home/orattadmin/app/TimesTen/tt1121
/u01/app/TimesTen/tt1121
Host
VTGDB1.COM
VTGDB2.COM
User OS
orattadmin
orattadmin
Data source
ttvaaa
ttvaaa
Backup Dir
/home/orattadmin/backup
/u01/app/TimesTen/backup


ON OLD TIMESTEN SERVER


VTGDB1.COM
[orattadmin@VTGDB1.COM backup]$ttBackup -type fileFullEnable -dir /home/orattadmin/backup ttvaaa
[orattadmin@VTGDB1.COM backup]$cd /home/orattadmin/backup
[orattadmin@VTGDB1.COM backup]$ ls
ttvaaa.0.bac  ttvaaa.0.bac64  ttvaaa.sta

Copy all contents of /home/orattadmin/backup to NEW TIMESTEN SERVER (//u01/app/TimesTen/backup)

ON NEW TIMESTEN SERVER


VTGDB2.COM
[orattadmin@VTGDB2.COM backup]$ pwd
/u01/app/TimesTen/backup
[orattadmin@VTGDB2 backup]$ ls
ttvaaa.0.bac  ttvaaa.0.bac64  ttvaaa.sta

Insert or edit in file sys.odbc.ini on folder (/u01/app/TimesTen/tt1121/info)
VTGDB2.COM
[ODBC Data Sources]
ttvaaa=TimesTen 11.2.1 Driver
...
[ttvaaa]
Driver=/u01/app/TimesTen/tt1121/lib/libtten.so
DataStore=/u01/app/ttdata/ttvaaa
LogDir=/u01/app/ttdata
PLSQL_MEMORY_SIZE=100
PermSize=1500
TempSize=100
LogBufMB=100
PLSQL=1
#OracleNetServiceName=vas
Connections=100
DatabaseCharacterSet=WE8MSWIN1252

Restart ttDaemonAdmin
VTGDB2.COM
[orattadmin@VTGDB2.COM info]$ ttDaemonAdmin -restart
TimesTen Daemon stopped.
TimesTen Daemon startup OK.

Restore TimesTen
VTGDB2.COM

[orattadmin@VTGDB2 TimesTen]$ ttRestore -dir /u01/app/TimesTen/backup -fname ttvaaa "dsn=ttvaaa"
Restore started ...
Restore complete


Install Oracle Timesten step by step


Step 1: Create TimesTen user and groups

[root@VTGDB.COM ~]# groupadd timesten
[root@VTGDB.COM ~]# groupadd orattadmin
[root@VTGDB.COM ~]#  useradd -G orattadmin -g timesten orattadmin
[root@VTGDB.COM ~]# id orattadmin
uid=1104(orattadmin) gid=1104(timesten) groups=1104(timesten),1105(orattadmin)
[root@VTGDB.COM ~]# passwd orattadmin
Changing password for user orattadmin.
New password:
BAD PASSWORD: is too simple
Retype new password:
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.

Step 2: Create directories

[root@VTGDB.COM ~]# mkdir -p /etc/TimesTen
[root@VTGDB.COM ~]# chown -R orattadmin:timesten /etc/TimesTen
[root@VTGDB.COM ~]# mkdir -p /u01/app/TimesTen/
[root@VTGDB.COM ~]# chown -R orattadmin:timesten /u01/app/TimesTen/
[root@VTGDB.COM ~]# mkdir -p /u01/app/ttdata/
[root@VTGDB.COM ~]# chown -R orattadmin:timesten /u01/app/ttdata/
[root@VTGDB.COM ~]# chown -R orattadmin:timesten /home/orattadmin

Step 3: Starting setup Timesten Program

Then, Copy the downloaded file to /etc/Timesten
[root@VTGDB.COM TimesTen]# su - orattadmin
[orattadmin@VTGDB.COM ~]$ cd /etc/TimesTen/
[orattadmin@VTGDB.COM TimesTen]$ ls
timesten112180.linux8664.tar.gz
[orattadmin@VTGDB.COM TimesTen]$ tar -xzvf timesten112180.linux8664.tar.gz
linux8664/
linux8664/doc/
linux8664/doc/doc.zip
linux8664/3rdparty/
linux8664/3rdparty/ant-1.6.2-bin.tar.bz2
linux8664/3rdparty/jms-1_1-fr-apidocs.tar.bz2
linux8664/install.pl
linux8664/setup.sh
linux8664/uninst.sh
linux8664/README.html
linux8664/LINUX8664/
linux8664/LINUX8664/manifest
linux8664/LINUX8664/perl
linux8664/LINUX8664/bzip2
linux8664/LINUX8664/unzip
linux8664/LINUX8664/ttpatchinst
linux8664/LINUX8664/common.tar.bz2
linux8664/LINUX8664/ttserver.tar.bz2
linux8664/LINUX8664/timesten.tar.bz2
linux8664/LINUX8664/ttclient.tar.bz2
[orattadmin@VTGDB.COM TimesTen]$ cd linux8664/
[orattadmin@VTGDB.COM linux8664]$ ls
3rdparty  doc  install.pl  LINUX8664  README.html  setup.sh  uninst.sh
[orattadmin@VTGDB.COM linux8664]$ ./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
      The instance name must be a non-null alphanumeric string, not longer
      than 255 characters.

Please choose an instance name for this installation? [ tt1121 ]
Instance name will be 'tt1121'.
Is this correct? [ yes ]

Of the three components:

  [1] Client/Server and Data Manager
  [2] Data Manager Only
  [3] Client Only

Which would you like to install? [ 1 ]

Of the following options :

  [1] /home/orattadmin
  [2] /etc/TimesTen
  [3] Specify a location
  [q] Quit the installation

Where would you like to install the tt1121 instance of TimesTen? [ 1 ] 3
Please specify a directory to install TimesTen? [ /home/orattadmin ] /u01/app/TimesTen/
Where would you like to create the daemon home directory? [ /u01/app/TimesTen/tt1121/info ]

The daemon logs will be located in /u01/app/TimesTen/tt1121/info
Would you like to specify a different location for the daemon logs? [ no ]
Installing into /u01/app/TimesTen/tt1121 ...
Uncompressing ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
      daemon port number must be the same across all TimesTen installations
      managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
      port number that is set at installation time. The daemon port number can
      be verified by running 'ttVersion'.

The default port number is 53388.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (53388).

NOTE: For security, we recommend that you restrict access to the
      TimesTen installation to members of a single OS group. Only members of
      that OS group will be allowed to perform direct mode connections to
      TimesTen, and only members of that OS group will be allowed to perform
      operations that access TimesTen data stores, TimesTen files and shared
      memory. The OS group defaults to the primary group of the instance
      administrator. You can default to this group, choose another OS group
      or you can make this instance world-accessible. If you choose to make
      this instance world-accessible, all database files and shared memory
      are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'timesten'? [ yes ]

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ]

In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using /bin/ttModInstall.

Please enter a value for TNS_ADMIN (s=skip)? [  ] /u01/app/TimesTen/tt1121/network/admin

TNS_ADMIN will be set to /u01/app/TimesTen/tt1121/network/admin
You can change TNS_ADMIN later by running /bin/ttmodinstall.



NOTE: It appears that you are running version 4.1 of the g++
      compiler. TimesTen ships with multiple sets of client libraries and server
      binaries : one built for compatibility with g++ 3.4.6 and one with
      g++ 4.1.0. The installer has created links to the 4.1.0 library in the
      /lib directory and to the 4.1.0 server binary in the
      /bin directory. If you want to use a different compiler,
      please modify the links to point to the desired library and server binary.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53389 ]
Do you want to install QuickStart and the TimesTen Documentation? [ no ]
Would you like to install the documentation (without QuickStart)? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /u01/app/TimesTen/tt1121/doc ]
The TimesTen documentation has been installed in /u01/app/TimesTen/tt1121/doc.
Installing client components ...

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the 'setuproot' script :
            cd /u01/app/TimesTen/tt1121/bin
            ./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
  '/u01/app/TimesTen/tt1121/startup/tt_tt1121'.

The 11.2.1.8 Release Notes are located here :
  '/u01/app/TimesTen/tt1121/README.html'

Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.

Step 4: Run the script by root user

Run scripts:
[root@VTGDB.COM  app]# cd /u01/app/TimesTen/tt1121/bin
[root@VTGDB.COM  bin]# ./setuproot -install
Would you like to install the TimesTen daemon startup scripts into /etc/init.d? [ yes ]
Copying /u01/app/TimesTen/tt1121/startup/tt_tt1121 to /etc/init.d

Successfully installed the following scripts :
/etc/init.d/tt_tt1121
/etc/rc.d/rc0.d/K45tt_tt1121
/etc/rc.d/rc1.d/K45tt_tt1121
/etc/rc.d/rc2.d/S90tt_tt1121
/etc/rc.d/rc3.d/S90tt_tt1121
/etc/rc.d/rc5.d/S90tt_tt1121
/etc/rc.d/rc6.d/K45tt_tt1121

Setting environment for TimesTen

Step 5: Setting environment for TimesTen
[orattadmin@VTGDB.COM ~]$ vi .bash_profile
# .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
# Set environment for Timesten
export TIMESTEN_HOME=/u01/app/TimesTen/tt1121
export TNS_ADMIN=$TIMESTEN_HOME/network/admin
export PATH=$PATH:$TIMESTEN_HOME/bin
source $TIMESTEN_HOME/bin/ttenv.sh

[orattadmin@VTGDB.COM ~]$ .  .bash_profile
NOTE: TNS_ADMIN is already set in environment - /u01/app/TimesTen/tt1121/network/admin

LD_LIBRARY_PATH set to /u01/app/TimesTen/tt1121/lib:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1

PATH set to /u01/app/TimesTen/tt1121/bin:/u01/app/TimesTen/tt1121/quickstart/sample_code/oci:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc/xla:/u01/app/TimesTen/tt1121/quickstart/sample_code/jdbc:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc_drivermgr:/u01/app/TimesTen/tt1121/quickstart/sample_code/proc:/u01/app/TimesTen/tt1121/quickstart/sample_code/ttclasses:/u01/app/TimesTen/tt1121/quickstart/sample_code/ttclasses/xla:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk:/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/orattadmin/bin

CLASSPATH set to /u01/app/TimesTen/tt1121/lib/ttjdbc5.jar:/u01/app/TimesTen/tt1121/lib/orai18n.jar:/u01/app/TimesTen/tt1121/lib/timestenjmsxla.jar:/u01/app/TimesTen/tt1121/3rdparty/jms1.1/lib/jms.jar:.