Oracle PeopleSoft Data mover Tool

Hi DBAs,
Oracle PeopleSoft Data Mover(psdmt.exe) Tool is quite similar to FNDLOAD Utility  in Oracle EBS Application.PeopleSoft Data Mover is a PeopleTool that is used to export/import data between databases.
Apart from this,Data Mover can be used for a variety of functions like database security management,running SQL scripts etc.
On Windows machines,we can use the Data Mover using the GUI as well as from the command prompt.
While UNIX variants support only command line operation.

Modes of Operation:

A Data Mover operates in two different modes
1)Regular mode
2)Bootstrap mode.

1)Regular mode:To sign into the Data Mover in the regular mode,we use your PeopleSoft userID and password.

2)Bootstrap mode:To sign into  the Dava Mover in Bootstrap mode,we use the Database user ID and password.


Working with Data Mover PeopleTool:


While using a Data Mover,it is a standard practice to set a log file and an input/output file.Export scripts use the output file and Import scripts use input files.
Log files are used by both. We use the following commands to set input/output and log files.

SET INPUT C:\TEMP\DATA_FILE1.DAT;
SET OUTPUT C:\TEMP\DATA_FILE1.DAT;
SET LOG C:\TEMP\LOG_FILE1.LOG;

Commands of Data Mover:
The Data Mover commands are:

1)EXPORT:Used to export data out of the database.A file is specified and the data gets loaded into it.Later this data can be used for importing into the same/different databases.

Eg:
SET LOG C:\TEMP\PayCodeMove.log;
SET OUTPUT C:\TEMP\PayCodeMove.Dat;
EXPORT *;

2)IMPORT:Used to load data from a file into the database.This command also takes care of the table spacing.

Eg:
SET LOG C:\TEMP\PayCodeMove.log;
SET INPUT C:\TEMP\PayCodeMove.Dat;
IMPORT *;

Note:Make Sure you have the .Dat file in C:\TEMP location.

ENCRYPT_PASSWORD:This command can be used to get all or any user passwords encrypted.

Data Mover Script(DMS) Templates:

1)Export DMS Template and
2)Import DMS Template

We can download and use Export/Import DMS Template for our projects.These will act as starting points for our DMS scripting.


Using Data Mover PeopleTool:

If we have installed People Tool,Lets say we have 8.48.11 People Tool installed in Windows machine,we will find the Data Mover(psdmt.exe) application in below path:
C:\PeopleTools\8.48.11\bin\client\winx86.

Double Click on psdmt.exe application,It asks for Connection type,Database Name,User Id and Password.Provide the require inputs.We will see a window opening,copy and paste the Export/Import commands here.Above of this window we find traffic like symbol,click on it to execute the Export/import commands.Veriy the logs.If we see any error than we have to verify the Application designing and building steps,troubleshoot there and do it again.



Hope it helps...
Enjoy Oracle PeopleSoft Admin tasks...


Best regards,

Rafi.

psadmin Utility in Oracle PeopleSoft Application

Hi DBAs,
       psadmin is very vital utility for PeopleSoft DBAs. PeopleSoft administration like start/stop,purging of cache,PeopleSoft Application server Administration,Process Scheduler administration and lot of administration tasks related to PeopleSoft Applications can be done with utiliy  'psadmin'.
This utility is similar to adadmin utility in Oracle EBS Application.Since I'm from Oracle Apps DBA background,so I always compare and do PeopleSoft administration tasks,it also help me to sharp my apps dba skills.Let me share the below task of Starting/Stopping the PeopleSoft Application server.Here I'm using 'psoftsvc' OS user which is quite similar to 'applmgr' OS user in EBS applications.PeopleSoft Server  Administration can be done using 'psadmin' utility.


Stopping PeopleSoft Applications:
=======================================
Login with 'psoftsvc' user and set the PeopleSoft environment by executing 'psconfig.sh' file(as we do in Oracle EBS application by executing APPSContext.env file) and then run the psadmin utility.

login as: psoftsvc
Using keyboard-interactive authentication.
Password:
Last login: Sun Aug 19 12:33:42 2012 from 10.157.223.9
Oracle Corporation      SunOS 5.10      Generic Patch   January 2005
TERM = xterm
psoftsvc@[PeopleSoftApps_1]->su - psoftsvc

Password:

TERM = xterm
Please enter the menu command to get started.
PeopleSoftApps_1(/product/psoftsvc >menu
Select a Peoplesoft Environment:
1) PeopleSoftApps_1
2) PeopleSoftApps_2
3) Statistics
4) Quit
Selection:1
Welcome to the Peoplesoft PeopleSoftApps_1 Environment.
Tuxedo Installation Directory: /product/bea/tuxedo81
Cobol Installation Directory:  /product/microfocus/cobol

psoftsvc@PeopleSoftApps_1(PeopleSoftApps_1):/product/psoft/PeopleSoftApps_1>cd appserv
/product/psoft/PeopleSoftApps_1/appserv
psoftsvc@PeopleSoftApps_1(PeopleSoftApps_1):/product/psoft/PeopleSoftApps_1/appserv>./psadmin

PSADMIN -- Tools Release: 8.48.11
Copyright (c) 1988-2005 PeopleSoft, Inc.  All Rights Reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------


  1) Application Server
  2) Process Scheduler
  3) Search Server
  q) Quit


Command to execute (1-3, q): 1


--------------------------------------------
PeopleSoft Application Server Administration
--------------------------------------------

  1) Administer a domain
  2) Create a domain
  3) Delete a domain
  4) Import domain configuration
  q) Quit

Command to execute (1-4, q) : 1


Tuxedo domain list:

  1)  PeopleSoftApps_1

Select domain number to administer: 1


--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: PeopleSoftApps_1

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  8) Purge Cache
  9) Preload File Cache
 10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) : 1


-------------------------------
PeopleSoft Domain Boot Menu
-------------------------------
     Domain Name: PeopleSoftApps_1

  1) Boot (Serial Boot)
  2) Parallel Boot
  q) Quit

Command to execute (1-2, q) [q]: 1
psappsrv.cfg has changed archiving old one...
Copying PeopleSoftApps_1/Archive/psappsrv.cfg to PeopleSoftApps_1/Archive/psappsrv_080712_2252_05.cfg
Attempting to boot bulletin board...
tmadmin - Copyright (c) 1996-1999 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
Tuxedo is a registered trademark.
INFO: BEA Tuxedo, Version 8.1, 64-bit, Patch Level 192
INFO: Serial #: 650522264138-1184269388129, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft - ISV

Booting admin processes ...

exec BBL -A :
        CMDTUX_CAT:821: INFO: Duplicate server.
0 processes started.
tmadmin - Copyright (c) 1996-1999 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
Tuxedo is a registered trademark.
Attempting to boot ...
INFO: BEA Tuxedo, Version 8.1, 64-bit, Patch Level 192
INFO: Serial #: 650522264138-1184269388129, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft - ISV

Booting server processes ...

exec PSWATCHSRV -A -- -ID 44130 -C psappsrv.cfg -D PeopleSoftApps_1 -S PSWATCHSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSQCKSRV -s@../psqcksrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSQCKSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSQRYSRV -p 1,250:5,10 -sICQuery -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSQRYSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSSAMSRV -A -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSSAMSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSBRKHND -p 1,250:5,10 -s PSBRKHND_dflt:BrkProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSBRKHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSBRKHND -p 1,250:5,10 -s PSBRKHND_dflt:BrkProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSBRKHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSBRKHND -p 1,250:5,10 -s PSBRKHND_dflt:BrkProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSBRKHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSBRKHND -p 1,250:5,10 -s PSBRKHND_dflt:BrkProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSBRKHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSBRKDSP -s PSBRKDSP_dflt:Dispatch -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSBRKDSP_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSPUBHND -p 1,250:5,10 -s PSPUBHND_dflt:PubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSPUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSPUBHND -p 1,250:5,10 -s PSPUBHND_dflt:PubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSPUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSPUBHND -p 1,250:5,10 -s PSPUBHND_dflt:PubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSPUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSPUBHND -p 1,250:5,10 -s PSPUBHND_dflt:PubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSPUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSPUBDSP -s PSPUBDSP_dflt:Dispatch -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSPUBDSP_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSSUBHND -p 1,250:5,10 -s PSSUBHND_dflt:SubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSSUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSSUBHND -p 1,250:5,10 -s PSSUBHND_dflt:SubConProcess -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSSUBHND_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSSUBDSP -s PSSUBDSP_dflt:Dispatch -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSSUBDSP_dflt :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec PSMONITORSRV -A -- -ID 44130 -C psappsrv.cfg -D PeopleSoftApps_1 -S PSMONITORSRV :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec WSL -A -- -n //PeopleSoftApps_1:7000 -z 0 -Z 0 -d /dev/tcp -I 5 -T 30 -m 1 -M 2 -x 40 -c 100000 -p 7001 -P 7002 :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec JSL -A -- -d /dev/tcp -n //PeopleSoftApps_1:9000 -m 20 -M 20 -I 5 -j ANY -x 30 -S 5 -c 10000000 -w JSH :
        CMDTUX_CAT:821: INFO: Duplicate server.
exec JREPSVR -A -- -W -P /product/psoft/PeopleSoftApps_1/appserv/PeopleSoftApps_1/jrepository :
        CMDTUX_CAT:821: INFO: Duplicate server.
0 processes started.


Starting PeopleSoft Applications:
===================================
TERM = xterm
psoftsvc@[PeopleSoftApps_1]->su - psoftsvc

Password:

TERM = xterm
Please enter the menu command to get started.
PeopleSoftApps_1(/product/psoftsvc >cd appserv
ksh: appserv:  not found
PeopleSoftApps_1(/product/psoftsvc >menu
Select a Peoplesoft Environment:
1) PeopleSoftApps_1
2) PeopleSoftApps_2
3) Statistics
4) Quit
Selection:1
Welcome to the Peoplesoft PeopleSoftApps_1 Environment.
Tuxedo Installation Directory: /product/bea/tuxedo81
Cobol Installation Directory:  /product/microfocus/cobol

psoftsvc@PeopleSoftApps_1(PeopleSoftApps_1):/product/psoft/PeopleSoftApps_1>cd appserv
/product/psoft/PeopleSoftApps_1/appserv
psoftsvc@PeopleSoftApps_1(PeopleSoftApps_1):/product/psoft/PeopleSoftApps_1/appserv>./psadmin

PSADMIN -- Tools Release: 8.48.11
Copyright (c) 1988-2005 PeopleSoft, Inc.  All Rights Reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------


  1) Application Server
  2) Process Scheduler
  3) Search Server
  q) Quit


Command to execute (1-3, q): 1


--------------------------------------------
PeopleSoft Application Server Administration
--------------------------------------------

  1) Administer a domain
  2) Create a domain
  3) Delete a domain
  4) Import domain configuration
  q) Quit

Command to execute (1-4, q) : 1


Tuxedo domain list:

  1)  PeopleSoftApps_1

Select domain number to administer: 1



--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: PeopleSoftApps_1

  1) Boot this domain
  2) Domain shutdown menu
  3) Domain status menu
  4) Configure this domain
  5) TUXEDO command line (tmadmin)
  6) Edit configuration/log files menu
  7) Messaging Server Administration menu
  8) Purge Cache
  9) Preload File Cache
 10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) : 1


-------------------------------
PeopleSoft Domain Boot Menu
-------------------------------
     Domain Name: PeopleSoftApps_1

  1) Boot (Serial Boot)
  2) Parallel Boot
  q) Quit

Command to execute (1-2, q) [q]: 1
psappsrv.cfg has changed archiving old one...
Copying PeopleSoftApps_1/Archive/psappsrv.cfg to PeopleSoftApps_1/Archive/psappsrv_081912_1238_55.cfg
Attempting to boot bulletin board...
tmadmin - Copyright (c) 1996-1999 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
Tuxedo is a registered trademark.
No bulletin board exists. Entering boot mode.
INFO: BEA Tuxedo, Version 8.1, 64-bit, Patch Level 192
INFO: Serial #: 650522264138-1184269388129, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft - ISV

Booting admin processes ...

exec BBL -A :
        process id=2356 ... Started.
1 process started.
Attempting to boot ...
INFO: BEA Tuxedo, Version 8.1, 64-bit, Patch Level 192
INFO: Serial #: 650522264138-1184269388129, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft - ISV

Booting server processes ...

exec PSWATCHSRV -A -- -ID 44130 -C psappsrv.cfg -D PeopleSoftApps_1 -S PSWATCHSRV :
        process id=2391 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2393 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2413 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2417 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2421 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2443 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2447 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2451 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2463 ... Started.
exec PSAPPSRV -p 1,250:5,10 -s@../psappsrv.lst -- -C psappsrv.cfg -D PeopleSoftApps_1 -S PSAPPSRV :
        process id=2467



Enjoy PeopleSoft DBA tasks,


Best regards,

Rafi.

Creating Oracle 11g Database manually in 11 steps in Unix-based Operating system

Hi,
This is one of the most basic tasks for Oracle DBA,it also helps us to understand how Oracle Database works.Creating Database in 11g is very simple.In 10g you need to create additional directories bdump,cdump,udump instead of diagnostic dump directory.Below are the steps:


Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u03/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/testdb/

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u03/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u03/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u03/testdb/redo3.log' SIZE 10M
DATAFILE
'/u03/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u03/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u03/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[oracle@localhost ~]$ 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
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

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

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/testdb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;


Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

--------------------------------


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Enjoy learning Oracle DBA....


Best regards,

Rafi.

Copying Files and Directories commands for DBAs:

Copying Files  and Directories commands for DBAs:
 Hi DBAs,
         Many of our tasks deals with copying of Data within same and Different server from Windows to Linux(Unix Based) OS or from any Unix based Operating system
to Other Unix based Operating system.We can use different commands and tools for doing it.Below I'm sharing my experience.
       
1)tar:tar stands for translate archive.It is most favourite commands for Apps DBAs when performing clonning and refreshing of environment,we use this command for copying huge size directories from one environment to other environment,tar when combined with gzip is the best combination.tar is the most powerful command.

2)scp:scp stands for secure copy.It is the fastest command for copying,again handy tool for DBAs and Apps DBAs.It is use to copy files and directories from one server to another server.

3)gzip and gunzip:gzip is use for compressing files on Unix-based system and gunzip is use for uncompressing the files on Unix-based systems i.e Solaris,Linux,IBM-AIX,HP-UX.
Again very useful command for DBA.

Eg 1:Let's say I want to tar and zip a directory 'bin' and copy it to target server

On Source System:
=================

Tarring and zipping:
===================

tar -cvf - bin | gzip >bin.tar.gz


On Target System:
=================

Copying to Target system:
==========================

scp -rp oracle@testpapp01cdp.comp.com:/product/app/TESTAPPSDB/apps/apps_st/appl/xxtwc/12.0.0/bin.tar.gz .

passwd:

Extracting and Untarring:
=========================

gunzip -c bin.tar.gz|tar -xvf -
 
Eg 2:

For Compressing:
=================

Let's say you are currently in /product/obiee Directoy and want to compress and zip the OracleBIData Directory,than it can be done as follows:

tar -cvf  /product/obiee/OracleBIData.tar OracleBIData/


>ls -altr
total 987816
drwxr-x---   9 obiee    psapps      1024 Jul  6  2010 OracleBIData
drwxr-xr-x   4 obiee    psapps        96 Jul  9  2010 java
drwxr-x---  20 obiee    psapps      1024 Dec  9  2010 OracleBI
drwxr-xr-x  16 applmgr  oinstall    1024 May 12  2011 ..
drwxrwxrwx   5 obiee    oinstall    1024 Aug  8 21:05 .
-rw-r--r--   1 obidev2  psapps   505448448 Aug  8 21:17 OracleBIData.tar


For Zipping the Directory:
-----------------------------------------------------

$gzip OracleBIData.tar


>ls -altr
total 78256
drwxr-x---   9 obiee    psapps      1024 Jul  6  2010 OracleBIData
drwxr-xr-x   4 obiee    psapps        96 Jul  9  2010 java
drwxr-x---  20 obiee    psapps      1024 Dec  9  2010 OracleBI
drwxr-xr-x  16 applmgr  oinstall    1024 May 12  2011 ..
-rw-r--r--   1 obidev2  psapps   40054038 Aug  8 21:17 OracleBIData.tar.gz


Eg 3:


For Unzipping the Directory:
------------------------------------

$gunzip OracleBIData.tar.gz


For untarring/extracting the Directory:
-------------------------------------------

$tar -xvf OracleBIData.tar


Note:
gzip=>compression
c=create
v=verbose

tar with gzip are the best method to take the backup of directory,since we can do it very fast and it saves the Disk space efficiently.

Note:tar command options are operating system dependent,so please check with man command command for using various options.


4)rsync:rsync is a great tool for backing up and restoring files,from source system to target system.It is Useful tool for DBAs.It is very important to remember this command works on linux and Doesn't work on Solaris Operating system as rsync doesn't have manual entry on it.

Eg 4:Copy all directories and file structure with all the ownership,permissions and symbolic links.


On Target System:
=================

rsync -arv oracle@testpapp01cdp.comp.com:/tmp/rafi/bin /tmp/rafi/backup_bin

(/tmp/rafi=>current working directory on Target Server)


5)sftp:sftp stands for secure file transfer protocol.some times if scp ports are disabled due to security reason than you can use sftp.It is very much secure way
of copying files and directories from source to target Server.This is very useful for DBAs and Apps DBAs.

Eg 5:Copying files from one server to other server

Using sftp for copying from qb to prod:
------------------------------------------

On Target System:
=================

oracle@[testpapp01cdp]-> sftp oracle@testqapp02cdp.comp.com:/tmp/rafi
Connecting to testqapp02cdp.comp.com...
Password:
Changing to: /tmp/rafi
sftp> ls
sftp> get lookup_xxtwc_perf_level_planned_perc.ldt
Fetching /tmp/rafi/lookup_xxtwc_perf_level_planned_perc.ldt to lookup_xxtwc_perf_level_planned_perc.ldt

6)winscp:winscp is GUI(Graphical user interface) based tool for copying files from windows to Linux(Unix environment) and vice-versa.I like this tool very much,since it is very much user friendly.But,for copying large amount of data it takes using this tool.In this tool you can save the machine name and login with privilege user and copy the files.

7)ftp:ftp stands for  file transfer protocol and use for copying files from windows to linux(Unix environment) and vice-versa.This is fast method of copying files.
 2mb of data can be copied in less than 5 minutes.

Eg 7:

On Target System(Windows):
===========================

Go to the target folder in windows.

C:\Documents and Settings\rafiuddin_alvi>cd D:\TEST

C:\Documents and Settings\rafiuddin_alvi>d:

D:\TEST>ftp testdapp01cdc.comp.com
Connected to testdapp01cdc.comp.com.
220 testdapp01cdc.comp.com FTP server ready.
User (testdapp01cdc.comp.com:(none)): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> help
Commands may be abbreviated.  Commands are:

!               delete          literal         prompt          send
?               debug           ls              put             status
append          dir             mdelete         pwd             trace
ascii           disconnect      mdir            quit            type
bell            get             mget            quote           user
binary          glob            mkdir           recv            verbose
bye             hash            mls             remotehelp
cd              help            mput            rename
close           lcd             open            rmdir
ftp> cd /tmp/rafi
250 CWD command successful.
ftp> dir
200 PORT command successful.
150 Opening ASCII mode data connection for /bin/ls.
total 4640
-rwxrwxrwx   1 oracle  twcother 2272275 Aug  6 03:18 cn_reports.rpd
-rwxrwxrwx   1 oracle  twcother    8115 Jul  7 19:07 DisputeSrchPG.xml
-rwxrwxrwx   1 appldev2 oinstall     916 Jul 25 04:38 L6958530.log
-rwxrwxrwx   1 appldev2 oinstall     880 Aug  6 08:06 L6960409.log
-rwxr-xr-x   1 oracle  twcother      23 Aug  8 20:11 test1.sh
-rwxrwxrwx   1 appldev2 oinstall   22889 Jul 25 04:38 XX_QUINTILE_BATCH.ldt
-rwxrwxrwx   1 oracle  twcother    1802 Jul  5 06:48 XXTaskPerzSumCO.class
-rwxrwxrwx   1 oracle  twcother    1934 Jul  7 19:07 XXTaskSummaryCO.class
-rwxrwxrwx   1 appldev2 oinstall   20235 Aug  6 08:06 XXTWC_REPORT_METRIC_VAL
.ldt
226 Transfer complete.
ftp: 672 bytes received in 0.08Seconds 8.62Kbytes/sec.
ftp> get test1.sh
200 PORT command successful.
150 Opening ASCII mode data connection for test1.sh (23 bytes).
226 Transfer complete.
ftp: 24 bytes received in 0.00Seconds 24000.00Kbytes/sec.

ftp> get cn_reports.rpd
200 PORT command successful.
150 Opening ASCII mode data connection for cn_reports.rpd (2272275 bytes).
226 Transfer complete.
ftp: 2284457 bytes received in 113.08Seconds 20.20Kbytes/sec.

     To exit ftp:
  
ftp> bye
221-You have transferred 2284481 bytes in 2 files.
221-Total traffic for this session was 2285843 bytes in 3 transfers.
221-Thank you for using the FTP service on testdapp01cdc.comp.com.
221 Goodbye.

b)Putting files from source(Windows) to Target(Linux):

ftp> mput my_docs.txt
mput my_docs.txt? y
200 PORT command successful.
150 Opening ASCII mode data connection for my_docs.txt.
226 Transfer complete.
ftp> dir
200 PORT command successful.
150 Opening ASCII mode data connection for /bin/ls.
total 4640
-rwxrwxrwx   1 oracle  twcother 2272275 Aug  6 03:18 cn_reports.rpd
-rwxrwxrwx   1 oracle  twcother    8115 Jul  7 19:07 DisputeSrchPG.xml
-rwxrwxrwx   1 appldev2 oinstall     916 Jul 25 04:38 L6958530.log
-rwxrwxrwx   1 appldev2 oinstall     880 Aug  6 08:06 L6960409.log
-rw-r--r--   1 oracle  twcother       0 Aug  8 20:48 my_docs.txt
-rwxr-xr-x   1 oracle  twcother      23 Aug  8 20:11 test1.sh

8)cp:cp is the most basic command in linux(Unix environment) for copying files within same directory or directories on Same server.

Eg 8:To copy test1.sh to Target location /tmp/rafi,we can use below command.

testdapp01cdc(/export/home/oracle) >cp test1.sh /tmp/rafi

testdapp01cdc(/tmp/rafi) >ls -altr *test*
-rwxr-xr-x   1 oracle  twcother      23 Aug  8 20:11 test1.sh



Hope it helps....

Enjoy DBA learning...


Best regards,

Rafi.