Skip to main content

Posts

Showing posts from June, 2012

Create Tablespace in ORACLE 10g

Create Tablespace in ORACLE 10g Permanent tablespace Adding Tablespace in database: CREATE SMALLFILE TABLESPACE  BU_SYSTEM_TBS  DATAFILE  '$DATAFILE_LOCATION/BU_SYSTEM_TBS'  SIZE  100M  AUTOEXTEND  ON  NEXT  1000M  MAXSIZE  UNLIMITED  LOGGING   EXTENT MANAGEMENT  LOCAL  SEGMENT SPACE MANAGEMENT  AUTO; Adding Datafile in a Tablespace: Use ALTER TABLESPACE to add datafile in tablespace: ALTER TABLESPACE BU_HIS_LOG_TBS ADD DATAFILE '$DATAFILE_LOCATION/BU_HISTLOG_TBS_01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED; Modify Datafile: You can modify datafile using ALTER DATABASE command: ALTER DATABASE DATAFILE   ’$DATAFILE_LOCATION/data01.dbf’  AUTOEXTEND   ON   NEXT   30M   MAXSIZE   1200M; which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required. Renaming Tablespaces: This is a feature that is available with Oracle 10g and can be useful in transportable tablespa

Starting and Stopping the Oracle Enterprise Manager Console

Starting and Stopping the Oracle Enterprise Manager Console To access the Oracle Enterprise  Manager Console from a client browser, the  dbconsole  process needs to be running on the server. The dbconsole process is automatically started after installation. However, in the event of a system restart,change in IP, or other changes, you can start it manually at the command line. To start the  dbconsole  process from the command line: Navigate into your  ORACLE_HOME/bin  directory. Run the following statement: ./emctl start dbconsole Additionally, you can stop the process and view its status. To stop the  dbconsole  process:          ./emctl stop dbconsole To view the status of the  dbconsole  process:         ./emctl status dbconsole -------------------------------------------------------------------------------------------------------

How to enable log shipping on Standby after failing archival.

The Primary Database was giving error in alert log that it was not able to send archivelogs to Standby. The error looked like the following: Tue Jun  5 10:31:36 2012 Errors in file /u01/oracle/admin/DB1/bdump/DB1_arcp_6846.trc: ORA-16055: FAL request rejected ARCH: FAL archive failed. Archiver continuing Diagnosis: To check the status of archiving the following sql was issued:   Check what is the destination status  select ds.dest_id                id,            ad.status,          ds.database_mode          db_mode,          ad.archiver               type,          ds.recovery_mode,          ds.protection_mode,          ds.standby_logfile_count  "SRLs",          ds.standby_logfile_active active,          ds.archived_seq#     from v$archive_dest_status ds, v$archive_dest ad    where ds.dest_id = ad.dest_id      and ad.status != 'INACTIVE'    order by ds.dest_id;       ID STATUS DB_MODE

Getting dump or list all parameters set at session level:

Getting dump or list all parameters set at session level: Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode= ALL_ROWS ; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; The Trace file contents: --------------------------------------------------------------------------------------------------------- PARSING IN CURSOR #1 len=43 dep=0 uid=0 oct=42 lid=0 tim=3561292942895 hv=3364811346 ad='90925928' alter session set optimizer_mode=first_rows END OF STMT PARSE #1:c=30000,e=30737,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3561292942888 EXEC #1:c=0,e=341,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3561292943385 *** 2012-06-04 15:28:05.809 DYNAMICALLY MODIFIED PARAMETE

How to Find Database is 32 bit or 64 Bit

How to Find Database is 32 bit or 64 Bit Login to command prompt using oracle user. Issue the following command. $  file    $ORACLE_HOME/bin/sqlplus you should see output like /u01/db/bin/sqlplus:  ELF 32-bit LSB executable 80386 Version 1, dynamically linked, not stripped If your oracle is 32 bit you should see output like oracle: ELF 32-bit MSB executable ----------------------------------------------------------------------------------------------

Script for monitoring RMAN progress

Script for monitoring RMAN progress: Using the following SQL you can monitor the progress of RMAN process, as channels completes creating the backup piece, new sessions are created so overall progress can be monitored by looking at where context = 3 as in the case below it shows the overall progress is 38.05% complete. SELECT SID,        SERIAL#,        USERNAME,        START_TIME,        CONTEXT,        SOFAR,        TOTALWORK,        ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE",        sysdate + TIME_REMAINING / 3600 / 24 Entimated_End_Time   FROM V$SESSION_LONGOPS   WHERE OPNAME LIKE ' RMAN% '     AND OPNAME NOT LIKE ' %aggregate% '     AND TOTALWORK != 0  AND SOFAR <> TOTALWORK ; Related Link: Related Link on This Topic RMAN Performance Tuning