Oracle Database Read Only User

This post explains how to create a user with read only access for any table in an Oracle Schema.

This Schema "SYSTEM" hold 50 tables and we create a user to access only this 50 tables in read only mode.

Let's take the database name as Test1

> SET ORACLE_SID=Test1
> SQLPLUS /NOLOG

SQL> CONN SYSTEM/SYSTEM;
SQL> CREATE USER User_Read_Only indentified by User_Read_Only
SQL> GRANT CREATE SESSION, SELECT ANY TABLE TO User_Read_Only
SQL> Commit; Now connect as newly created Read Only User and test.

SQL> CONN User_Read_Only/User_Read_Only;

Check for tables using this new User and SYSTEM.

Done

Oracle database cloning in 11g R2

In this post how to clone an Oracle Database is shown with 11g R2 Version on Windows platform. This post is just for learning purpose. Don't directly try on Production databases till you get the insight of it.

Old database -  test007
New cloned database - clonetest007

Step 1: Bring the old database test007 in Archivelog Mode.

SET ORACLE_SID=test007
SQLPLUS / as sysdba;
SQL> Archive log List

The output can be ARCHIVELOG or NOARCHIVELOG. We will go with ARCHIVELOG mode so following is the step for it.

SQL> Alter System Set LOG_ARCHIVE_DEST_1='Location=E:\oradata\test007\Archived_logs' scope=SPFILE;

Step 2: Now we will shutdown the database and start it in Archivelog Mode;

SQL> Shutdown Immediate;

SQL> startup mount;

SQL> Alter database Archivelog;

SQL> Alter database Open;

With this query let's check if our old database test007 is now in Archivelog mode or not.

SQL> select log_mode from v$database; OR SQL> Archive log list;

Step 3: Now we will create a new database with the clone of old one.

First we will create new folders for our new database.

We will change our directory on command prompt till the oradata where the data files exists.

e:> cd e:\app\product\11.2.0\dbhome_1\oradata\
e:\app\product\11.2.0\dbhome_1\oradata> mkdir clonetest007
e:\app\product\11.2.0\dbhome_1\oradata> cd clonetest007

e:\app\product\11.2.0\dbhome_1\oradata\cd clonetest007> mkdir Archived_Logs

Step 4: Now we will create the PFILE parameter file for the new database clonetest007

Lets create as "initCLONETEST007.ora" in ORACLE_HOME\database with parameter

db_name=clonetest007

Save the file.

Step 5: Now we will create the window service for this oracle database with the shipped "oradim" utility.

oradim -new -sid clonetest007

Instance Created.

We can also start this window service if it's not started with the below command.

net start OracleServiceclonetest007

Step 6: Let's now take the backup of old database test007

set ORACLE_SID=test007

SQLPLUS / AS SYSDBa;

SQL> select log_mode from v$database; ( Archivelog)

SQL> select * from v$backup;

If status is NOT ACTIVE then make it ACTIVE

SQL> Alter database begin backup;

SQL> quit;

Step 7: Now copy all the datafiles *.dbf to the oradata/clonetest007 folder which we created in above steps.

SQL> Alter database end backup;

SQL> Alter system archive log current;

SQL> Alter database backup control file to trace as 'e:\clonetest007.sql';

Open the trace file & make following changes:

REUSE - SET
NORESETLOGS - RESETLOGS
test007 - clonetest007

Remove comments and extra spaces. Keep the text till character set else delete and save it.

Step 8: Now lets start the new database clonetest007

set ORACLE_SID=clonetest007

SQLPLUS / AS SYSDBA;

Connected to an Idle Instance.

Run the trace file now created in above step.

SQL> e:\clonetest007.sql;

Control File Created;

Step 9: Now we will take the current archive log of old database for recovery of new database.

set ORACLE_SID=test007

SQLPLUS / AS SYSDBA;

SQL> Alter system archive log current;

SQL> /

SQL> /

SQL> quit;

Step 10: Recover and start the new database.

set ORACLE_SID=clonetest007

 SQLPLUS / AS SYSDBA;

SQL> Set logsource 'e:\oradata\test007\Archived_logs';

Press Enter four times then Cancel Type.

SQL> Alter database open resetlogs;


************************************** Done ************************************



 

Cannot open the Windows NT Service Control Manager.

This is the most common error that we face in using oracle "oradim" utility from command line:

DIM-00014: Cannot open the Windows NT Service Control Manager.

O/S-Error: (OS 5) Access is denied.

Solution:

This is just an administrator priviledge/right's issue.

Start the command line "cmd.exe" by right click and Run as Administrator.

CMDTUX CAT:1685: ERROR: Application initialization failure

CMDTUX CAT:1685: ERROR: Application initialization failure
CMDTUX_CAT:827: ERROR: Fatal error encountered; initiating user error handler
 
Solutions for this Error:
1) Check if your application server can reach the tuxedo installed directory. Proper permissions must be there on the Middleware directory where the tuxedo installation is done.

2) Since this issue arrives at application server level check the database server is up and running and is accessible to application server or not. TNSPING and LSNRCTL status can be checked to verify the connectivity between application server and database server.

3) Environment variable can be checked for Tuxedo TUXDIR path.

4) Disk space can be checked at the application server machine. Due to low disk space also this kind of initialization failure can come.

5) Check the Application Server and Tuxedo logs for the exact error where it happens.

Peoplesoft Application Designer Basic Tutorial

This video explain the Basic of Application Designer. What it is? What is the use of it?

 

Video showing Application Designer Basic Tutorial

Peoplesoft Installation 8.53 Video Tutorials

"This videos can be helpfull for Learning Peoplesoft Installation 8.53 with Human Capital Management System HCM9.2"

Platform : Windows 7
Database : SQL Server 2008 SP3
Peoplesoft Peopletools 8.53
Application HCM9.2

In this Videos Peoplesoft Installation 8.53 ,Creating Peoplesoft Database, Configuring Domains, Integration Broker and Peoplesoft Test Framework configuration is shown: Learn and Share with peoplesoft professionals.

 

       A short Introduction to Peoplesoft - Video #1

    How to obtain softwares for peoplesoft Installation - Video #2

 
Installation of SQL Server 2008, JDK update, Tuxedo and Weblogic Server - Video #3
 

Installation of Peoplesoft Peopletools 8.53 - Video #4

Installation of Peoplesoft Application HCM9.2 - Video #5

 

Creating Peoplesoft Database with SQL Server 2008 - Video #6

 

Creating Application Server Domain in Peoplesoft - Video #7

 

  
Creating Weblogic Server Domain in Peoplesoft - Video #8

 

Creating Process Scheduler Domain in Peoplesoft - Video #9

 

Configure Integration Broker Setup in Peoplesoft - Video #10

 

Install Peoplesoft Test Framework - Video #11

Configure Peoplesoft Test Framework - Video #12
 
Please share it accross to help peoplesoft professionals to get the max knowledge and Information out of it.

WebServer in Peoplesoft is Running Slow

This is very common issue found in peoplesoft. Weblogic Server is running slow. The user is complaining the pages are taking a long time to open.

Solution:

1) In this case we can check if the application server is running properly or not. Is any bottleneck is happening between the application and database server.
2) We can check the server status through PSADMIN utility for the application server process PSAPPSRV. If all server process PSAPPSRV is busy then also it can be a reason for longer processing of the request which cause the web server to run slow.
3) We can increase the number of Server process PSAPPSRV, PSQCKSRV, PSQRYSRV which can help to run the long queries if any in lesser time.
4) Thread count can be increased if required to increase the number of service request to be processed more on the web server.

This are few solution that can be check on the peoplesoft server instance. Also possibly take backup of configuration files before any changes to be on safer side.