Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for July, 2012

« Previous Entries

Alias (Unix)

Posted by FatDBA on July 23, 2012

Alias:

The alias command allows you to make new shortcuts and synonyms for commonly used commands.

Alias settings resets every time you reboots or switch users in Unix. There are number of ways/methods to permanently save Alias in your machine. Below discussed is one of the most common way to fix alias settings to make them global and always available.

1. Edit your bash profile (If using Bash Shell) and add all alias you want to set.

[root@localhost /]# gedit /etc/bashrc
(Below are excerpts from a live bashrc profile from one of my machine)

# /etc/bashrc

# System wide functions and aliases
# Environment stuff goes in /etc/profile

alias base=’cd /u01/app/oracle’
alias c=’clear’
alias home=’cd /u01/app/oracle/product/10.2.0/db_1′
alias sql=’sqlplus / as sysdba’
alias sid=”echo $ORACLE_SID”

# By default, we want this to get set.
# Even for non-interactive, non-login shells.

2. Add source /etc/bashrc to your ~/.bashrc profile.

[root@localhost /]# gedit ~/.bashrc

Will look like —

# .bashrc
# User specific aliases and functions

alias rm=’rm -i’
alias cp=’cp -i’
alias mv=’mv -i’
source /etc/bashrc

# Source global definitions

 

Enjoy The Shortcuts using ‘alias’ — Below is one of alias used to reach directory – ORACLE_BASE using alias named ‘base’.

[root@localhost ~]# base

[root@localhost oracle]# pwd
/u01/app/oracle
[root@localhost oracle]#

Advertisement

Posted in Advanced | Tagged: , | 1 Comment »

OS Kernel Parameters Explained (Oracle Installations)

Posted by FatDBA on July 22, 2012

During Manual Oracle Database Installation on Linux/Unix Platforms we need to perform some changes to Kernel Values. Below are the Kernel parameters needed to be change before installation begins:

If you have not used the “oracle-validated” package to perform all prerequisites, you will need to manually perform the following setup tasks.

To check existing Kernel parameters settings use below string to verify:
# cat /proc/sys/kernel/shmmni
4096

To check Semaphores Values:
[oracle@localhost orcl]$ ipcs -ls

—— Semaphore Limits ——–
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

kernel.shmmax = 2147483648
Meaning: This parameter defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space.Since the SGA is comprised of shared memory, SHMMAX can potentially limit the size of the SGA. SHMMAX should be slightly larger than the SGA size. If SHMMAX is too small, you can get error messages similar to this one:

ORA-27123: unable to attach to shared memory segment

kernel.shmall = 2097152
Meaning: This parameter sets the total amount of shared memory pages that can be used system wide.

kernel.shmmni = 4096
Meaning: This parameter sets the system wide maximum number of shared memory segments. Oracle recommends SHMMNI to be at least 4096 for Oracle 10g.

# semaphores: semmsl, semmns, semopm, semmni
kernel.sem=250 32000 100 128

semmsl: This parameter defines the maximum number of semaphores per semaphore set.
semmns: This parameter defines the total number of semaphores (not semaphore sets) for the entire Linux system.
semopm: This parameter defines the maximum number of semaphore operations that can be performed per semop(2) system call (semaphore call).
semmni: This parameter defines the maximum number of semaphore sets for the entire Linux system.

Posted in Advanced | Tagged: | 1 Comment »

RMAN Command – (Host, Script & Spool)

Posted by FatDBA on July 20, 2012

============================
‘SCRIPT’ Command
============================

Create a stored script and store it in the recovery catalog

# creates recovery catalog script to back up database and archived logs
RMAN> CREATE SCRIPT backup_whole
COMMENT “backup whole database and logs”
{
BACKUP INCREMENTAL LEVEL 0 TAG b_whole_l0
DATABASE PLUS ARCHIVELOG;
}

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

Scripts of Target Database QRCL

Script Name
Description
———————————————————————–
backup_whole
backup whole database and logs

RMAN>

The PRINT SCRIPT command is used to view the contents of a stored script.
The REPLACE SCRIPT command is used to update the contents of a stored script.
The EXECUTE SCRIPT command is used to execute the commands in the stored script.
The SCRIPT command line arguments for RMAN (described in “cmdLine”) runs a stored script automatically when starting RMAN.
The DELETE SCRIPT command is used to delete a stored script from the recovery catalog.

============================
‘HOST’ Command
============================
Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.

RMAN> host;

[oracle@prashant1 tmp]$ ls
bkp.txt                  keyring-0ApY2V  keyring-bnJBhL  keyring-EU8HZP  keyring-gyrtQl  keyring-NhkTlq  keyring-TRWPLH

keyring-VRSr6r  mapping-root
df2log.f                 keyring-2DWGLn  keyring-BnLWvG  keyring-Fd4x0Y  keyring-hvDT3R  keyring-NkuSug  keyring-ud148y

keyring-yfJADW  VMwareDnD
gedit.oracle.1518151704  keyring-9YXYpF  keyring-bWWK07  keyring-FulTZv  keyring-M5RlDV  keyring-OKgmpF  keyring-VJRsde

keyring-z0NZhH  vmware-root
gedit.root.905835812     keyring-b0iCrd  keyring-edvXjr  keyring-GUQwrx  keyring-N7mw5M  keyring-tmfXqO  keyring-vMdOpj

mapping-oracle  vmware-tools-distrib

[oracle@prashant1 tmp]$ exit
exit
host command complete

RMAN>

=============================
‘SPOOL’  Command
=============================

Write RMAN output to a log file.

[oracle@prashant1 tmp]$ touch bkp.txt
[oracle@prashant1 tmp]$ ls
bkp.txt                  keyring-2DWGLn

[oracle@prashant1 tmp]$ rman target / catalog rman/oracle90@orcl;

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Jul 20 15:43:23 2012

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

connected to target database: QRCL (DBID=859215136)
connected to recovery catalog database

RMAN> @/tmp/bkp/txt;

RMAN> BACKUP DATAFILE 1;
Starting backup at 20-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/qrcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JUL-12
channel ORA_DISK_1: finished piece 1 at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/backupset/2012_07_20/o1_mf_nnndf_TAG20120720T154419_80lcvvwv_.bkp

tag=TAG20120720T154419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 20-JUL-12

Starting Control File and SPFILE Autobackup at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/autobackup/2012_07_20/o1_mf_s_789147885_80lcwos9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUL-12

RMAN> SPOOL LOG TO ‘/tmp/df2log.f’;RMAN>
RMAN>
Spooling for log turned off

Recovery Manager10.2.0.1.0

RMAN> **end-of-file**

RMAN>
RMAN> exit

Recovery Manager complete.

[oracle@prashant1 tmp]$ cat df2log.f

Spooling started in log file: /tmp/df2log.f

Recovery Manager10.2.0.1.0

RMAN> BACKUP DATAFILE 2;
Starting backup at 20-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/app/oracle/oradata/qrcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JUL-12
channel ORA_DISK_1: finished piece 1 at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/backupset/2012_07_20/o1_mf_nnndf_TAG20120720T154449_80lcwslb_.bkp

tag=TAG20120720T154449 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-JUL-12

Starting Control File and SPFILE Autobackup at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/autobackup/2012_07_20/o1_mf_s_789147892_80lcwx78_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUL-12

RMAN> SPOOL LOG OFF;
[oracle@prashant1 tmp]$

Posted in Advanced, Basics | Tagged: , | Leave a Comment »

Flashback Drop (Recycle Bin)

Posted by FatDBA on July 16, 2012

When you’ve deleted a Table and soon after thyat deletion one of the user created a table with the same name then in that case we have to perform some steps to mitigate the conflict otherwise you’ll recieve error ‘Object trying to create already exists’ and will not allow you to restore the Old Table.

Deleted Table emp1 from system —

SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Created a table with same name after deletion – emp1 —

SQL> create table emp1 (name varchar2(10));
Table created.
SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KnyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:17:31
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Now if i’ll try to Flashback table emp1 then it will restore the one with latest Time Stamp.
SQL>  show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41
This can also be performed using Time Stamp —
FLASHBACK TABLE emp1 TO TIMESTAMP TO_TIMESTAMP(‘2012-07-15:12:16:41’, ‘YYYY-MM-DD HH:MI:SS’);
During the flashback operation the table can be renamed.
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Posted in Advanced | Tagged: , | Leave a Comment »

Database Version Details

Posted by FatDBA on July 14, 2012

Below provided image is descriptive enough to explain the details of a Database Version.

Notes: Texts on the top of the number defined the name of the process/activity and below are the standard names. Last number in the range is shands for “Platform Specific Release Number” eg. the last zero out of 10.2.0.1.0

Posted in Basics | Tagged: , | 2 Comments »

Master Table – Data Pump Jobs

Posted by FatDBA on July 14, 2012

What is a Master Table & what’s the significance or importance of  it during a Data Pump Job ?

At the heart of the Data Pump operation is the Master Table.  This table is created at the beginning of a Data Pump operation and is dropped at the end of the successful completion of a Data Pump operation. The Master Table can also be dropped if the job is killed using the kill_job interactive command. If a job is stopped using the stop_job interactive command or if the job is terminated unexpectedly, the Master Table will be retained.
The Master Table is used to track the detailed progress information of a Data Pump job:

* The current set of dump files.
* The current state of every object exported or imported and their locations in the dump file set.
* The job’s user-supplied parameters.
* The status of every worker process.
* The state of current job status and restart information.

The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks of lots of detailed information.  As a result, this table can take up a significant amount of storage space.

[oracle@localhost /]$ expdp larry/champion tables=etr,orders directory=dpump

Export: Release 10.2.0.1.0 – Production on Tuesday, 01 May, 2012 23:27:46

Copyright (c) 2003, 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
Starting “LARRY”.”SYS_EXPORT_TABLE_01″:  larry/******** tables=etr,orders directory=dpump dumpfile=test.dmp logfile=testlog.log content=all
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “LARRY”.”ETR”                            5.648 KB       7 rows
. . exported “LARRY”.”ORDERS”                            5.734 KB       5 rows
Master table “LARRY”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for LARRY.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/oradata/test.dmp
Job “LARRY”.”SYS_EXPORT_TABLE_01″ successfully completed at 23:27:51

Example: As highlighted in Bold the Master Table is successfully loaded and unloaded at the end of the Data Pump Job. Created in the same schema (Larry) which initiated the DP job as underlined created by the name of: SYS_EXPORT_TABLE_01

Let the Red Prevail …
Prashant D

Posted in Advanced | Tagged: , , | 2 Comments »

Oracle Background Processes Explained – (10-07-2012)

Posted by FatDBA on July 10, 2012

Background Process Explained-
==========================================

[oracle@prashant1 2012_09_04]$ ps -ef | grep qrcl
oracle    3219  3176  0 20:31 pts/2    00:00:00 tail -f /u01/app/oracle/admin/qrcl/bdump/alert_qrcl.log
oracle    3545     1  0 20:43 ?        00:00:00 ora_pmon_qrcl
oracle    3547     1  0 20:43 ?        00:00:00 ora_psp0_qrcl
oracle    3549     1  0 20:43 ?        00:00:00 ora_mman_qrcl
oracle    3551     1  0 20:43 ?        00:00:00 ora_dbw0_qrcl
oracle    3553     1  0 20:43 ?        00:00:00 ora_lgwr_qrcl
oracle    3555     1  0 20:43 ?        00:00:00 ora_ckpt_qrcl
oracle    3557     1  0 20:43 ?        00:00:00 ora_smon_qrcl
oracle    3559     1  0 20:43 ?        00:00:00 ora_reco_qrcl
oracle    3561     1  0 20:43 ?        00:00:00 ora_cjq0_qrcl
oracle    3563     1  0 20:43 ?        00:00:00 ora_mmon_qrcl
oracle    3565     1  0 20:43 ?        00:00:00 ora_mmnl_qrcl
oracle    3567     1  0 20:43 ?        00:00:00 ora_d000_qrcl
oracle    3569     1  0 20:43 ?        00:00:00 ora_s000_qrcl
oracle    3573     1  0 20:43 ?        00:00:00 ora_arc0_qrcl
oracle    3575     1  0 20:43 ?        00:00:00 ora_arc1_qrcl
oracle    3579     1  0 20:43 ?        00:00:00 ora_qmnc_qrcl
oracle    3585     1  0 20:44 ?        00:00:00 ora_j000_qrcl
oracle    3589     1  0 20:44 ?        00:00:00 ora_q000_qrcl
oracle    3591     1  0 20:44 ?        00:00:00 ora_q001_qrcl
oracle    4137     1  0 21:05 ?        00:00:00 ora_q002_qrcl
oracle    4162  2694  0 21:06 pts/1    00:00:00 grep qrcl
[oracle@prashant1 2012_09_04]$

SMON :– SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.  SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

$ ps -ef | grep smon
oracle   31144     1  0 11:10 ?        00:00:00 ora_smon_orcl

PMON (Process MONitor) is an Oracle background process created when you start a database instance. The PMON process will free up resources if a user process fails (eg. release database locks).
PMON is responsible to help listeners to determine if database service and it’s service handlers (Dispatchers) are available through registration.
During registration process PMON provides information like:
– Name of the database service provided by the database
– Service handlers (Dispatchers) available for the instance, including their type, protocol   address and current load details.

PMON normally wakes up every 3 seconds to perform its housekeeping activities. PMON must always be running for an instance. If not, the instance will terminate.
To speed-up housekeeping, one may also wake-up PMON (process 2 below) manually

SQL> oradebug setmypid
SQL> oradebug wakeup 2

MMON (Memory Monitor) is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.  This process was introduced in Oracle 10g.

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.

ARCH (Oracle’s ARCHiver Process) is an Oracle background process created when you start an instance in ARCHIVE LOG MODE. The ARCH process will archive on-line redo log files to an archive destination, typically a directory on the database sever.

DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. The DBWR writes data from the SGA to the Oracle database files. When the SGA buffer cache fills the DBWR process selects buffers using an LRU algorithm and writes them to disk. From Dirty buffers to Data Files.

LGWR (LoG WRiter) is an Oracle background process created when you start a database instance. The LGWR writes the redo log buffers to the on-line redo log files. If the on-line redo log files are mirrored, all the members of the group will be written out simultaneously.

CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred.
The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

d000 (Dispatchers): Dispatchers are optional background processes, present only when the shared server configuration is used.

QMNC: A queue monitor process which monitors the message queues. Used by Oracle Streams Advanced Queuing.

MMNL Performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
Server Process (Not part of standard Oracle Architecutre) – Writes the data blocks from data file to DB Buffer Cache.

Oracle 11g Architecture (View Online)

Posted in Basics | Tagged: | Leave a Comment »

What is a Control File ?

Posted by FatDBA on July 10, 2012

What is a Control File ?
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

  • The database name
  • Names and locations of associated datafiles and redo log files
  • The timestamp of the database creation
  • The current log sequence number
  • Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

Backing it up in a binary format:
alter database backup controlfile to ‘/some/arbitrary/path’;
alter database backup controlfile to ‘/some/arbitrary/path’ reuse;

Backing it up in a human readable format:
alter database backup controlfile to trace;
alter database backup controlfile to trace as ‘/some/arbitrary/path’;
alter database backup controlfile to trace as ‘/some/arbitrary/path’ reuse;

Inside of a Control File (Contents):


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M
— STANDBY LOGFILEDATAFILE
‘/u01/app/oracle/oradata/orcl/system01.dbf’,
‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’,
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’,
‘/u01/app/oracle/oradata/orcl/users01.dbf’,
‘/u01/app/oracle/oradata/orcl/example01.dbf’
CHARACTER SET WE8ISO8859P1
;– Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE– All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

— Database can now be opened normally.
ALTER DATABASE OPEN;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/orcl/temp01.dbf’
SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
— End of tempfile additions.
—
—     Set #2. RESETLOGS case
—
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
—  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘/u01/app/oracle/oradata/orcl/system01.dbf’,
‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’,
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’,
‘/u01/app/oracle/oradata/orcl/users01.dbf’,
‘/u01/app/oracle/oradata/orcl/example01.dbf’
CHARACTER SET WE8ISO8859P1
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/orcl/temp01.dbf’
SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
— End of tempfile additions.

Posted in Advanced, Basics | Tagged: | Leave a Comment »

What are SYS and SYSTEM users ? — What are SYSDBA & SYSOPER Privileges?

Posted by FatDBA on July 10, 2012

What are SYS and SYSTEM users ?

SYS:

•automatically created when Oracle database is installed
•automatically granted the DBA role
•has a default password: CHANGE_ON_INSTALL
•owns the base tables and views for the database data dictionary

SYSTEM:

•automatically created when Oracle database is installed
•automatically granted the DBA role
•has a default password: MANAGER (make sure you change it)

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.

What are SYSDBA & SYSOPER Privileges?

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set.
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

Posted in Basics | Tagged: | Leave a Comment »

‘ORA-12547: TNS:lost contact’ after OPATCH_PLATFORM_ID Reset in sqlplus.

Posted by FatDBA on July 5, 2012

Resolution:

1. De-install the deployed patch first  —

[oracle@prashant 8974084]$ opatch rollback -id 8974084   (Click Y/Yes when it asked you to select out of Yes/No).

Once the patch uninstalled unset the OPATCH_PLATFORM_ID

example:     [oracle@prashant 8974084]$ unset OPATCH_PLATFORM_ID

2. Stop all middle wares included e.g. ENTERPRISE MANAGER, LISTENER and shutdown the database.

3. Revert back the PLATFORM_ID back to it’s old value —

[oracle@prashant 8974084]$ export OPATCH_PLATFORM_ID=46   (46 was the old value of the machine)

4. Recheck all Network files located in $ORACLE_HOME/dbs     like listener.ora, tnsnames.ora and tnsnames.ora they might corrupted as well. Make sure you have the correct Host Name, Post Number, Service number defined in  tnsnames.ora. Check sqlnet,ora and verify if it has the EZCONNECT , TNSNAMES.

5. reboot your machine.

6.  Start listener and to give the database a try to start. Most probably  chances are there that you’ll receive LOCAL_LISTENER definition failure message during the start.

In that case you have to check your parameter file and perform requisite changes to the local listener definitions (*.LOCAL_LISTENER=’ ‘)

7. Re try to start the database and you’ll be able to start DB this time.

Feedbacks: If failed to start the DB box even after performing these steps and that will also motivate me to write about and research  🙂

Posted in Advanced | Tagged: , , | 2 Comments »

« Previous Entries
 
%d bloggers like this: