Basic alter database commands

alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile

ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;

alter database: ARCHIVELOG Mode Commands

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/opt/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/logfile_backup/backup_logfile.ctl';

alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4 AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS NEW;

alter database: Datafile Offline/Online

See alter database: Alter a Data File

alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER '/opt/oracle/logfiles/redo02c.rdo' to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

alter database: Mount and Open the Database

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

alter database: Move or Rename a Database File or Online Redo Log

NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move database data files.

ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;

HowTo Remove parameters from the spfile

HowTo: Remove parameters from the spfile

How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?

Answer:

Use "ALTER SYSTEM RESET ..." (For database versions 9i and up)

Syntax:

ALTER SYSTEM RESET PARAMETER SID='SID|*'
ALTER SYSTEM RESET "_TRACE_FILES_PUBLIC" SCOPE=SPFILE SID='*';

NOTE: The "SID='SID|*'" argument is REQUIRED!

Changing SYSADMIN default password

Changing SYSADMIN default password
Hi All,This article is about "Reset of SYSADMIN password".Steps:1. Keep all the services running.Do not shutdown appsTier Or dbTier application2. Login to OS level via applmgr manager userLogin to application server via Operating System User.3. Run environment scriptsa. cd $APPL_TOPrun APPLORA.env$ . ./APPLORA.envb. cd admin (folder)run adovars.env$. ./adovars.envc. cd $FND_TOP/bin (folder)run FNDCPASS to change the SYSADMIN password(IMPORTANT: Change ONLY the SYSADMIN password)eg:FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN {new password}4. Restart the Apache,Forms,Reports, Listener & Concurrent Manager servicesIn short: Restart the dbTier Or appsTier services.5. To test, login into the application as the SYSADMIN user and the new password.6. Verify that a concurrent program such as Active Users runs.eg: Login to application user and run the any concurrent reports.

http://onlineappsdba.blogspot.com/2008/01/r12-collecting-configuration.html

http://onlineappsdba.blogspot.com/2008/01/r12-collecting-configuration.html

Oracle Applications 11i Hot Backup Cloning with Rapid Clone

Source System (PROD):

(a) P4 3.0 GHz System with 2GB RAM and 200 GB HDD (Redhat Linux AS 4)

/d01 ——- 40 GB (Application Tier Files)

/d02 ——- 10 GB (10g Oracle Home)

/d03 ——- 80 GB (Data Files)

/backup —- 100 GB (NFS mount point Shared on TEST Server)

Hostname: prodserver

Application Version: 11.5.10.2

Database Version: 10.2.0.2 Target System (TEST):

(b) P4 2.6 GHz system with 1.5 GB RAM with 300 GB HDD (Redhat Linux AS 4)

/d01 ——- 40 GB (Application Tier Files)

/d02 ——- 10 GB (10g Oracle Home)

/d03 ——- 80 GB (Data Files)

/backup —- 100GB (NFS Share Directory)

Hostname: testserver

Application Version: 11.5.10.2

Database Version: 10.2.0.2

Note: This target System was previously cloned with cold backup. This is second time cloning with Hot Backup from PRODSERVER.

Stage1: Prerequisites:

 ========> Apply OUI22 Patch, 5035661 to every IAS Oracle Home

          and RDBMS Oracle Home to be cloned. 

          If you are having 10g Oracle Home,

          there is no need of applying this patch. 

          You need to apply this patch on IAS Oracle Home 

         (if Database is not 10g)

A. Applying the patch on the iAS $ORACLE_HOME:

   ====================================================

   (a)  Unzip the patch into the  directory:

       $unzip -od /d01/prodora/iAS p5035661_11i_LINUX.zip

   (b)   Source the Apps environment file :

         $. $APPL_TOP/APPSORA.env

   (c)   Change directory to the /appsoui/setup

        $cd $IAS_ORACLE_HOME/appsoui/setup

   (d)  Execute the perl script OUIsetup.pl:

       $perl OUIsetup.pl

  NOTE:

  In the case of a Multi-Node instance, the above process

  should be repeated on the  of each Node.

(B) Applying the patch on the RDBMS $ORACLE_HOME: 

     (This step is not required for my current setup, 

    because my database version is 10g R2)

  (a)  Unzip the patch into the  directory:

       $unzip -od /u01/proddb/9.2.0 p5035661_11i_LINUX.zip

  (b)  Source the DB environment file :

       $. $ORACLE_HOME/PROD_prodserver.env

  (c) Change directory to the /appsoui/setup

      $cd $ORACLE_HOME/appsoui/setup

  (d) Execute the perl script OUIsetup.pl:

      $perl OUIsetup.pl

======> Check all other Requirements as Perl, JRE, JDK, ZIP utilities on Source and Target Nodes as per

        document “Cloning Oracle Applications Release 11i with Rapid Clone”

=======> Apply the Latest AD Minipack on Application Tier (Latest One is AD.I.5)

=======è Apply the Latest Autoconfig Template Patch and Latest Rapidclone Patches to Application Tier (Check Metalink for These Patches)

Stage2: Prepare the Source System (PRODSERVER)

      (a) Login into Database Tier as ORACLE user and run the preclone

           $cd $ORACLE_HOME/appsutil/scripts/PROD_prodserver

           $perl adpreclone.pl dbTier

      (b) Login into the Application Tier as APPLMGR User and run the  preclone

           $cd $COMMON_TOP/admin/scripts/PROD_prodserver

           $perl adpreclone.pl appsTier

Stage3: Put the Database in Begin Backup Mode and copy the Database Files 

       (a)   Login into database as sysdba user

             $sqlplus “/as sysdba”

             Sql> alter database begin backup;

       (b)   Copy Archive log files created during hot backup to /backup directory.

       (c)   Copy the All Data files to /backup directory.

       (d)   Backup the control file to trace.

             Sql> alter database backup control file to trace;

             Copy this trace file to /backup directory

       (e)   Copy the current init.ora file to /backup directory

       (f)   End the Begin Backup Mode.

             Sql> alter database end backup.

 Stage4: Copy the Application Tier File System Files

         (a)Login into the Application Tier as APPLMGR user and copy the APPL_TOP, COMMON_TOP, 

             IAS ORACLE HOME and 8.0.6 Oracle Home to /backup directory

 Stage5: Copy the Source Database files and Application Files to Target server

     Copy the parameter file, backup control file and archive log files from /backup directory  

          to /d01, /d02 and /d03 in target server.

 Stage 6: Configure the Target Database (TESTSERVER)

Log on to the target system as the ORACLE user

(1) Configure the

cd /appsutil/clone/bin

perl adcfgclone.pl dbTechStack

(2) Create the target database control file manually

Open the backed up control file

a. remove all lines before the startup nomount statement

b. Modify the REUSE to SET

c. Modify Source DB SID to Target SID (Here PROD to TEST)

d. Modify NORESETLOGS TO RESETLOGS

e. delete all lines after the CHARACTER SET statement

————————————

CREATE CONTROLFILE SET DATABASE “TEST” NORESETLOGS ARCHIVELOG…

LOGFILEGROUP 1

‘/d03/log01.log’ SIZE 50M,

…

DATAFILE

‘/d03/system01.dbf’,

‘/d03/undotbs01.dbf’,

…

CHARACTER SET UTF8;

—————————————————–

On the target system, modify the init.ora to have the target SID and location of the control file and also make sure that init.ora parameters are set for archive log mode On the target system,

starup the database in nomount stage

sql> startup nomount pfile=<>

sql> @clone.ctl ( here clone.ctl is the control file which we have modified above)

Once control file is created, database will be in mount stage

execute recover command using backup control file after the database is mounted

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

After the last archive log has been applied, issue the following command

SQL> alter database open resetlogs;

After opening the database, add temp files to target database

(3) Run the library update script against the database

cd /appsutil/install/

Where is “sl” for HP-UX, “so” for any other UNIX platform and not required for Windows.

(4)Configure the target database (the database must be open)

cd /appsutil/clone/bin

perl adcfgclone.pl dbconfig

where target context file is: /appsutil/.xml

Stage 7 : Configure the Target Application Tier

Logon to the target system as the APPLMGR user and type the following commands

$Cd $COMMON_TOP/clone/bin

$Perl adcfgclone.pl appsTier

Finishing tasks:

(1) Update Profile options

(2) Update Printer Settings

(3) Update the workflow configuration settings

(4) Verify the APPLCSF variable setting

(5) Update the session_cookie_domain value in icx_parameters

For the finishing tasks, check the finishing tasks section of the following document

Cloning Oracle Applications Release 11i with rapid clone Note: 230672.1

(The major defferenc is there between offline and "begin backup" mode.

Offline: If you put tablespace in offline you can't do any transaction on that perticular tablespace.
Begin backup: You can do transaction on the objects pertaining to this tablespace. only thing is the datafle header will be in freeze state. Once you take out of the backup mode your datafile header will be updated with new SCN no's. )

RMAN - Duplicate Database on the same host

RMAN - Duplicate Database on the same host

Primary DB : ORCL

Clone DB : AUX

Production Database should be archive enabled.

Startup mount;

alter database archivelog;

alter database open;

archive log list;

Recovery catalog for RMAN

Creating the Recovery Catalog Owner

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant

it the recovery_catalog_owner role. Look at this example:

% sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE tools
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman

Creating the Recovery Catalog

% rman catalog rman/rman@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

RMAN> REGISTER DATABASE;

Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )

Example Source Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /newpart//product/10.2.0/)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ORCL)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = ORCL)

)

(SID_DESC =

(GLOBAL_DBNAME = AUX)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = AUX)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

Example Source tnsnames.ora

AUX =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = AUX)(UR=A)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

Note : Make use of netca and netmgr to configure listener and tnsnames

# Find Production Database Files:

SQL> select name from v$datafile;

Create the Auxiliary Database directories needed

cd $ORACLE_HOME/dbs

create parameter file initAUX.ora

db_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

log_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

eg:-

db_name = aux

db_block_size = 8192

compatible = 10.2.0.1.0

remote_login_passwordfile = exclusive

control_files = ('/newpart/oradata/aux/control01.ctl',

'/newpart/oradata/aux/control02.ctl')

db_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

log_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

# create a passwordfile for remote connections as sysdba

% orapwd password= file=orapwAUX

% sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

Start the Duplication

ORACLE_SID=AUX; export ORACLE_SID # ksh

sqlplus /nolog

SQL> connect / as sysdba

Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

# Set your SID back to the TARGET for duplication.

> rman trace.log

Recovery Manager: Release 10.2.0.1.0 - Production

Copyright (c) Oracle. All rights reserved.

RMAN> connect target

connected to target database: V10GREL4 (DBID=2510891965)

RMAN>backup database;

RMAN>sql 'alter system switch logfile';

RMAN> connect auxiliary sys/pwd@AUX

connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to AUX device type disk;

Once this is done, login to duplicate database with alter database open resetlogs.

Configuring Load Balancer and Shared Application Tier for multiple Forms/Web Tier nodes

Configuring Load Balancer and Shared Application Tier for multiple Forms/Web Tier nodes




In this post I will describe the procedure to implement Http Layer load balancer for forms/web tier nodes. This also covers the method to setup your second forms/web tier node from the already running one forms/web node. The following picture describes what we will achieve:

Shared Application Tier

Assumptions:

1. There is already a Forms/web node running. This node will be called primary node.
2. The second node has already been setup and it is exactly similar in configuration to primary node i.e. OS version e.g. Red hat 4.0, OS kernel Parameters, OS patches, OS packages. This node will be called secondary node.
3. The secondary node is in the same network and has all the network related settings already done i.e. Assigned name, had an entry in DNS servers already. There should be ability to NFS mount application tier filesystems from primary node to secondary node.
4. You have setup your hardware level HTTP load balancer (e.g. Cisco, Big IP etc) and it is setup in your network (DNS entries, Ip adress setting have been done already etc..)

Steps:

1. Sharing your existing application file system with secondary node.

a. Verify Software versions: Before doing anything make sure you have the following softwares/patches
i. AD minipack H or higher (patch 2673262)
ii. ADX should be 11i.ADX.E.1 Feb 2005 Consolidated Update. (patch 4175764)
iii. iAS should be Oracle HTTP Server Rollup 4 (patch 3072811)
iv. Developer 6i patchset 16 or higher
v. Oracle Universal Installer should be 2.2.0.19: Apply patch 4017155 to each iAS ORACLE_HOME to have OUI version 2.2
vi. Apply patch 4104924 to each APPL_TOP in the application tier to have TXK AutoConfig Templates to RUP K.
vii. Apply patch 3219567 to each APPL_TOP in the application tier. This patch is included in 11.5.10.
viii. Zip version 2.3

b. Implement AutoConfig if you have not done yet : See metalink note:165195.1

c. Rename the Applications context file: If the Applications system was created with Rapid Install version 11.5.8 or earlier, you must regenerate the Applications context file:
i. cd /bin
ii. perl adclonectx.pl sharedappltop contextfile= Context file name
iii. Running adclonectx.pl creates a new Applications context file named SID_Hostname.xml

d. Run AutoConfig to enable shared file system
i. Run these commands on the primary node where you are enabling shared file system support. After performing this step, re-load your environment settings.
ii. cd /admin/scripts/CONTEXT_NAME
iii. adstpall.sh apps/apps pass
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script prompts for the following information:

Script Prompt

Important thing to note here is Configuration_top. There is an application environment variable called IAS_CONFIG_HOME which will be set to value of Configuration_top that you will provide. For non shared nodes this variable is set to IAS_ORACLE_HOME value. The perl script txkSOHM.pl that you ran above will create the following directories in IAS_CONFIG_HOME:
a. network (all tnsnames.ora etc)
b. Apache (apache config files)
c. oem_webstage (for oem conf files)
d. soap (Jserv soap conf files)
Please note that now your Apache configuration and log directories are under IAS_CONFIG_HOME and not IAS_ORACLE_HOME. This way oracle has placed IAS related configurations to separate directories for the nodes that will be sharing one IAS_ORACLE_HOME, this avoiding any conflict that may arise out of running same executable from two separate machines.

Adding the secondary node to a shared file system:

a. Execute Rapid clone on existing node.
i. cd COMMON_TOP/admin/scripts/CONTEXT_NAME
ii. perl adpreclone.pl appsTier

b. NFS mount the application tier files from primary node to secondary node.
Please note there is a specific way to NFS mount. Please see my blog post in wierdos sections:
NFS mounts with Shared Application Tier
c. Configure the node you want to add: On the secondary node login as applmgr and do the following.
i. cd AD_TOP/bin
ii. perl adclonectx.pl sharedappltop contextfile= Application Context file for existing node
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script will prompt you same question as mentioned above. The only difference is that this time you have to mention “Type of Instance” as “secondary”

At this point your second node is added and ready to run. You can test it by bringing up services on it. Please note that since load balancer is not yet configured, you can only use one node at a time.

Setup HTTP layer Load Balancer the for two nodes
1. Create a web entry point : When you setup your hardware level load balancer there is an option to specify for which URL this load balancer is configured. For example: http://navdeep.practicalappsdba.com. This URL is your “web entry point”. Also make sure that suitable option should be chosen to ensure that the load-balancer sends all the requests from one client session to the same Web Server Node, this is also called as session persistency or “stickyness”. At present oracle supports “Cookie based” and “IP address based” stickyness. Please consult your sysadmins/harware fols for Loadbalancer to know more about which stickyness is available for you.
2. On each Web server node, run the AutoConfig Context Editor : In the Context Detail screen, set the following configuration values:
a. “Web entry point Host” to the HTTP load-balancer machine name.
b. “Web entry point Domain” to the HTTP load-balancer domain name
c. “Web entry protocol” to the HTTP load-balancer protocol e.g. “http” or “https”
d. “Active Web Port” to the value of the HTTP load-balancer’s external port
e. “Login Page” to include “Web entry protocol”://“Web Host entry point”.“Web domain entry point”:“Active Web Port”

a. Here is an example values you will want to give:
i. Web entry point Host = navdeep
ii. Web entry point domain = practicalappsdba.com
iii. Web entry protocol = http
iv. Active Web Port = 8001
v. Login Page = http://navdeep.practicalappsdba.com:8001
3. Run Autoconfig on both the nodes.
4. Bring up the services on both the nodes and change your login URL to the one mentioned above e.g. http://navdeep.practicalappsdba.com:8001, try to login and see if everything is working.

At this point you have implemented shared application tier between your two nodes and placed Http based Hardware load balancer in front of them. If your implementation includes configurator then you will have to enable load balancing at the Apache JServ layer even if your hardware load balancer is configured to maintain session persistency. I will talk about Apache Jserv load balancer in my future post, till then keep reading my posts and good luck ……….

------------------------------------------------------------------------------------
Source :- http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
------------------------------------------------------------------------------------