|
| How to Drop Database in Oracle 10g !!!!!!! |
| Monday, June 30, 2008 |
It’s quite amazing that a database, healthy or otherwise, can be dropped in under a minute.” Gone in 60 seconds ..."
I knew about the feature, I just thought that I would never use it. Obviously, this is not a production system. It is useful for testing.
Oracle 10g dropping a database is a single command.
Make sure before to drop the DB.
Database must be closed.
Exclusively mounted
Restricted
The DROP DATABASE, which will remove your datafiles, control files, redo logs, and spfile.ora. Your INIT.ora and any password file are retained.
The database should be mounted exclusive, if necessary to prevent others' logins.
I knew about the feature, I just thought that I would never use it. Obviously, this is not a production system. It is useful for testing backups, though.
In order to drop the database start the database in restrict mode and bring it in mount state as shown:
Steps to drop database
Sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 30 16:25:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
oracle database closed
oracle database dismounted
oracle instance shutdown
SQL> startup mount exclusive restrict;
SQL> drop database;
Database dropped
SQL> exit
Note: Thus u will find that all the files associated with the database will be deleted.
OR
Run DBCA GUI tool (in ORACLE_HOME/bin subdirectory) and delete the database you do not want. |
posted by Jaswinder Singh @ 5:15 AM   |
|
|
|
| How to clear temporary tablespace !!!!!!! |
| Wednesday, June 25, 2008 |
If the tablespace is a default temporary tablespace when you are greeted with the following exception:
SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
Steps to clear the temporary tablespace:
The first step you need to perform is creating another temporary tablespace (let’s call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace.
1) SQL>CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oracle/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
2) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
3) SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
4) SQL>CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oracle/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
5) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
6) SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped. |
| posted by Jaswinder Singh @ 1:36 AM |
|
|
|
| Oracle Applications Patching !!!!!!! |
| Monday, June 23, 2008 |
How to apply patch in 11i instance and which order we need to apply?
We can apply patch with adpatch utility. i.e – adpatch driver=u203793.drv
If patch consists of copy driver, database driver, and generate driver then order is
c.drv – Copying of files
d.drv – Database updating
g.drv – Generation of files
What all are the patch components?
Read me files:
Before apply to patch read associated read me file carefully
Contains patch application instructions
Also may contain manual steps
Patch driver files :
Traditional patches: Copy driver, Database driver, Generate driver.
Newer patches: unified driver which combines the action of the copy, database and generate drivers in a single consolidated driver.
Replacement files: Copied over existing files
These files are listed in the copy driver and replace the forms, reports, SQL scripts, HTML files, object modules and etc.
Adpatch make a backup before replacement of any file
Patch metadata files: Contains key patch information
Metadata files are used by the AD utilities to determine prerequisite patches, analyze the impact of the patch on the system.
Compare versions of the files in the patch with those of files in the system.
SQL Scripts: Scripts to modify the database
Run to perform updates
Scripts are called by the database driver
Note: You read the read me file before applying any patch.
What is Auto Patch?
It is utility that automates many patching tasks for oracle applications
It is used to apply oracle applications patches
Compares version numbers before replacing a file to ensure the most recent file is used
It doesn’t change files if those on the file system are more recent then the ones in a patch
Makes a backup copy before replacing any file
It doesn’t run database tasks that have already been performed
It unloads patch metadata and verifies prerequisite patches have been applied
It reads and validates the patch driver files
It relinks executables
It generates java archive (JAR) files
It compiles JSP files
It updates database objects
It compiles invalid objects
It runs AutoConfig to update configuration files, If any template files are introduced or updated by the patch.
It saves patch history information to the database
It is platform aware, If u try to apply a sun Solaris specific patch on windows system, AutoPatch fails
What is needed to be considering before applying a patch?
Take a cold backup of oracle applications as well as database if it possible
Bounce the applications tier
Enable maintenance mode with adadmin utility
Create patch top directory
Download patch and unzip the patch under patch top directory
Make sure there is space in file system to apply patch
Merge all product specific patches |
| posted by Jaswinder Singh @ 5:00 AM |
|
|
|
| Do you know about DB RAC !!!!!!! |
| Thursday, June 19, 2008 |
• Minimum 2 Servers are required for DB RAC configuration.
• Group of servers acting as single system.
• Database files are on shared storage.
• Oracle instances communicating with a single shared copy of physical database.
• All instances having common data and control files.
• Each instance having individual log files and undo segments.
• In Oracle Database 10g Release 2, Oracle supports 100 nodes in a cluster.
• In Oracle Database 10g, Cluster management is provided using Oracle’s Clusterware.
• Oracle Clusteware Oracle’s Cluster Manager (CM) is a new feature in Oracle Database 10g RAC.
Oracle Clusterware Coming Soon..... |
| posted by Jaswinder Singh @ 2:51 AM |
|
|
|
| Components of OAS10gR3 !!!!!!! |
| Wednesday, June 18, 2008 |
Oracle Application Server 10g Release 3 (10.1.3.1.0) provides a new version of Oracle Application Server. Even it is part of an Oracle Applications R12. we can integrate Oracle Application Server 10g Release 3 (10.1.3.1.0) with an existing Oracle Application Server environment 10g (9.0.4) or 10g Release 2 (10.1.2) OracleAS Infrastructure, 10g Release 2 (10.1.2) Oracle HTTP Server, or 10g Release 2 (10.1.2) OracleAS Web Cache components.
Components
- Oracle HTTP Server (OHS).
- Oracle Containers for J2EE (OC4J).
- Oracle Process Manager and Notification Server (OPMN).
- Application Server Control Console. |
| posted by Jaswinder Singh @ 3:19 AM |
|
|
|
| Enabling Archive in Oracle Database 10g !!!!! |
| Thursday, June 12, 2008 |
1) Check these parameters and use alter system command to set these parameters if you do not want to use the default valuesThese following parameters are dynamic.
SQL> show parameter db_recovery_file_dest – If require set the location.
SQL> show parameter db_recovery_file_dest_size – Default size of dir is 2GB.
i.e SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*' ;
Note : Why we don’t need to set LOG_ARCHIVE_DEST parameter b’coz LOG_ARCHIVE_DEST_10 init param automatically set to DB_RECOVERY_FILE_DEST.
Note : log_archive_start parameter in pfile as it is been deprecated in 10g.
Note : log_archive_dest this parameter you can’t use with db_recovery_file_dest. If you want to multiple archive log destination you should set "log_archive_dest_n" parameter for mutliple location.
2) Shutdown Database and take cold backup.
SQL>shutdown immediate;
3) Startup database in Mount State.
SQL>Startup Mount
4) Enable Archive Log.
SQL>alter database archivelog;
5) Open Database
SQL>alter database open;
6) Check Archive Log Mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 128
Next log sequence to archive 130
Current log sequence 130
Or
SQL> select log_mode from v$database;
LOG_MODE
-----------------
ARCHIVELOG
6) Switch log file
SQL>alter system switch logfile;
System altered.
7) Now Check flash_recovery_area
flash_recovery_area\\ARCHIVELOG\/
8) Query these view.
V$FLASH_RECOVERY_AREA_USAGE - Using this view you can immediately see what kind of files are available in the Flash Recovery Area.
V$RECOVERY_FILE_DEST – It shows the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.
v$archived_log - Information about archived redo logs.
v$parameter - Shows the location of the flash recovery area where archived redo logs are created.
v$log_history - Contains information on previous redo logs
Note : You cannot enable these parameters if you have set values for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up the flash recovery area. You can instead set values for the LOG_ARCHIVE_DEST_n parameters. If you do not set values for local LOG_ARCHIVE_DEST_n, then setting up the flash recovery area will implicitly set LOG_ARCHIVE_DEST_10 to the flash recovery area.
==============================================
Pre-Checking
============
Logon to SQLPLUS and check:
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string PPAAPI_%t_%s_%r.arc
SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string /prod/arch
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /prod/arch
Oldest online log sequence 597
Current log sequence 600
SQL> !df -h /prod/arch
Filesystem Size Used Avail Use% Mounted on
approd06-nas:/vol/aoh_16a_arch/prod_arch
100G 1G 99G 1% /prod/arch
Action Plan
===========
1. Bring down the database services.
2. Bring up the database in mount state
Logon to SQLPLUS as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
3. Verify the Archive Log Mode as following:
SQL> archive log list
SQL>select log_mode from v$database;
4. After above action plan is done, once its changed to archive mode, Perform
the backup (Recommended). |
| posted by Jaswinder Singh @ 5:00 AM |
|
|
|
| Sniped Sessions Shadow Processes !!!!!!! |
| Tuesday, June 10, 2008 |
When idle_time exceeds, the session status becomes 'sniped' in v$session, but sniped sessions never get cleaned up.
Using 'alter system kill session' to kill the session, session status becomes 'killed' in v$session, but still is never cleaned up.
What happens is this:
1) The user leaves their session idle
2) Oracle sets the status to "sniped" (i.e. session is inactive, waiting on client)
3) Once the message is displayed, the session is terminated and the user must reconnect
It does not disconnect the session. It changes the status to from INACTIVE to SNIPED.
Set Parameter
Show parameter resource_limit
SQL> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
If this parameter is false.I changed RESOURCE_LIMIT=TRUE make it true as following
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH
Create New Profile
CREATE PROFILE "INACT_SESS" LIMIT IDLE_TIME 10;
Newly created profile assign to user
ALTER USER apps PROFILE inact_sess;
Query against Sniped status in v$session
Select * from v$session where status=’SNIPED’;
Shell Script to kill sniped processes from OS level :
Suppose Script Name : snip_kill_proc.sh
#!/bin/sh
tmpfile=/tmp/tmp.$$
su - testora -c /d01/oracle/product/10.2.0/bin/sqlplus /nolog < connect / as sysdba
spool kill_sniped_sessions.lst
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
nd s.status='SNIPED';
spool off
EOF
for x in `cat /d01/testora/kill_sniped_sessions.lst | grep "^[0123456789]"`
do
kill -9 $x
done
rm /d01/testora/kill_sniped_sessions.lst
Execute it by issue this command: sh snip_kill_proc.sh
We can Schedule this shell script in crontab as follow :
*/30 * * * * /d01/backup/scripts/snip_kill_proc.sh > /d01/backup/scripts/snip_kill_proc.log 2>&1
For Your Reference : Metalink Note id: 96170.1 Script for killing sniped sessions shadow processes
|
| posted by Jaswinder Singh @ 4:53 AM |
|
|
|
|
|
