RAM&KRISHNA BLOG

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Note: here we are trying to insert  values in a table from another user Which has access. SQL Error: ORA-02289: sequence does not exist 02289. 00000 - "sequence does not exist" *Cause: The specified sequence does not exist, or the user does            not have the required privilege to perform this operation. *Action: Make sure the sequence name is correct, and that you have            the right to perform the desired operation on this sequence. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ generating query is not having SCHEMA NAME suffixed before the SEQUENCE NAME. Please see below example’s for easy understanding. Please see painted values in the below queries. SQL> show user; USER is "RKPYTE" FAILING SQL: SQL> insert into RKPYTE.TRANSACTION_IMPORT(TRANSACTION_IMPORT_ID,IMPORT_RUN_CODE,SOURCE_TRANSACTION_ID,DELIVERY_METHOD_CODE,TRANSA

C:\Users\computer>set ORACLE_SID=TEST C:\Users\computer>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 10 23:05:45 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------- TEST TAKEN BACKUP OF THE CONTROL FILE; SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\TEST1\CONTROL.BKP'; Database altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. NOTE: EDITED PFILE AND REMOVED THE EXISTS CONTROLFILE PARAMETER FROM IT & TRY STARTED THE DATABASE IN MOUNT. SQL> startup mount; ORACLE instance started. Total System Global Area  535662592 bytes Fixed Size                  1348508 bytes Variable Size             167775332 bytes Database Buffers  

C:\Users\computer>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jan 10 22:13:29 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> grant dba to scott; Grant succeeded. SQL> conn scott Enter password: Connected. SQL> SQL> show user; USER is "SCOTT" SQL> SQL> create table bemp as select * from emp; create table bemp as select * from emp              * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table bemp; Table dropped. SQL> flashback table bemp to before drop; Flashback complete. SQL> desc bemp;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  EMPNO                                

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select dbid from v$database;       DBID ---------- 2188161033 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\computer>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET      Username/Password              (NONE) DBNAME      New database name              

C:\Users\>sqlplus SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 11 20:55:55 2013 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> create user exp identified by exp; User created. SQL> create user imp identified by imp; User created. SQL> grant dba to exp; Grant succeeded. SQL> grant dba to imp; Grant succeeded. SQL> conn exp Enter password: Connected. SQL> SQL> create table Test (col1 char(10)); Table created. SQL> grant select on Test to rishi; Grant succeeded. SQL> insert into Test values('hello'); 1 row created. SQL> commit; Commit complete. NOTE:Directory already exists. So used the existng one SQL> select * from dba_directories; OWNER                          DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ---------------------------------

Due to maximum number of sessions exceeded in XXXX database. Hence we have received the following error. ORA-12519: TNS:no appropriate service handler found Solution: Seems the number of processes parameter value less. Its unable to create new session. Because its reached maximum sessions. Please check like below. SQL> select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions'); RESOURCE_NAME                  CURRENT_UTILIZATION      MAX_UTILIZATION         INITIAL_ALLOCATION             LIMIT_VALUE ----------------------         -------------------   ---------------------    -----------------------   ------------------------------ processes                             177                    180                        180                         180 sessions                              198                    203                        203                         203 Close the unwanted sessions

DATA  GUARD Data Guard is basically a ship redo and then apply redo, as you know redo is the information needed to recover a database transaction. A production database referred to as a primary database transmits redo to one or more independent replicas referred to as standby databases. Redo information needed to recover a database transaction. Remember that the LGWR can write to the log file using "group" commits, basically entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, thus reducing I/O. Even through the data buffer cache has not been written to disk, Oracle guarantees that no transaction will be lost due to the redo log having successfully saved any changes. A production database referred to as a primary database transmits redo to one or more independent replicas referred to as standby databases. A standby database will also automatically re synchronize if it becomes temporary disconnected

STARTUP UPGRADE ------------------------------- Starts the database in OPEN UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. UPGRADE should only be used when a database is first started with a new version of the Oracle Database Server. STARTUP RESTRICT ----------------------------- Only enables Oracle Database users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature. STARTUP FORCE ------------------------ Shuts down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used. SQL> STARTUP FORCE; ORACLE instance started. Total System Global Area  805306368 bytes

NOTE: Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks. Logging of changed blocks is performed by the CTWR process which is also responsible for writing data to the block change tracking file. When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file. After enabling change tracking, the first level

Please see below ... We have encountered the below situation many times. The space is not getting released after dropping the data files in LINUX machine.   SQL> drop user TMP561 cascade; SQL> drop tablespace TMP561 including contents and datafiles; Note: The schema size is around 300Gig. And its having 11 datafiles each  data-file 30G. But after dropping the User/Tbs. Space is not reclaimed in the Server. SOLUTION: After dropping the datafiles still one process is holding these files. Check like below oracle@/oracle/tmp: /usr/sbin/lsof |grep deleted |grep TMP* oracle     13683    oracle   17u      REG             253,16 33286004736   40550413 /user/TMP/TMP561_01.dbf (deleted) oracle     13683    oracle   18u      REG             253,16 33286004736   40550414 /user/TMP/TMP561_02.dbf (deleted) oracle     13683    oracle   20u      REG             253,16 32212262912  206487553 /user/TMP/TMP561_03.dbf (deleted) oracle     13683    oracle   21u      R

RMAN - Setup of the Recovery Catalog: Setting up the recovery catalog RMAN is the first task the DBA completes when preparing to use RMAN. Lets look at the basic steps required to set up the recovery catalog. Creating The Recovery Catalog Creating the recovery catalog is not a terribly complicated process. It requires the DBA to set up a user in the database that will be used to store the recover catalog schema objects. Then the DBA needs to grant certain user rights to that userid, and then run a script to create the recovery catalog. The example below shows the creation of a user/schema in a database that will store the recovery catalog: CREATE USER MYRMAN IDENTIFIED BY MYRMAN DEFAULT TABLESPACE myrman_catalog_tbs TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON myrman_catalog_tbs; Once the user has been created, follow these steps to complete the setup of the schema and to create the recovery catalog in that schema: GRANT the userid (in this example, myrm

RMAN - Data Dictionary Views Several views exist to assist the DBA in interacting with RMAN. The views from the target database include: View Description V$ARCHIVED_LOG Displays information about archived redo logs from the control file. V$BACKUP_CORRPUTION Displays information about corrupted blocks discovered during database backups with RMAN. V$BACKUP_DATAFILE Provides information from the control file about backup datafiles and control files. V$BACKUP_DEVICE Provides information about various devices available from various third-party manufacturers. V$BACKUP_PIECE Displays information about all backup sets from the control file. V$BACKUP_REDOLOG Displays information about backed up redo logs in backup sets. V$BACKUP_SET Displays information about all backup sets from the control file. V$DATAFILE Lists datafile information from the database. V$DATAFILE_HEADER Lists information about database datafile headers. Selected views that are available f

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN

RMAN - Backup File Optimization Feature Overall backup times of a database can be reduced when you choose not to back up your read-only or offline tablespaces all the time. It used be quite tedious to track when these tablespaces were last backed up. With Oracle9i, RMAN offers the backup file optimization (BFO) feature. With BFO, if RMAN is about to back up a file, and the file has already been backed up by the same device type, RMAN will skip the backup of that file. This includes both data files and archived redo logs backed up by RMAN. Oracle determines if the file has changed by comparing the datafile to the header of the backed up datafile. If they match, the file is not backed up. Before RMAN decides to skip a given datafile, it will determine if the datafile backup that is available meets the established retention policy. If the latest backup violates the retention policy, then RMAN will back up the datafile anyway. There may be cases when you want to back up all tabl

Alert Log: [oracle@ bdump]$ view alert_HDRTG12.log Thu May 28 22:05:42 PDT 2015 Process P021 died, see its trace file Thu May 28 22:05:47 PDT 2015 Process m000 died, see its trace file Thu May 28 22:05:47 PDT 2015 ksvcreate: Process(m000) creation failed Thu May 28 22:06:00 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:10 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:43 PDT 2015 [oracle@bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 28 23:38:59 2015 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------------------------- HDRTG12 SQL> select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions'); RESOURCE

SQL> select name from v$database; NAME --------- UHTG1P SQL> drop tablespace TEST including contents and datafiles; drop tablespace TEST including contents and datafiles * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SOLUTION 1: SQL> drop tablespace TEST including contents and datafiles cascade constraints; Tablespace dropped. NOTE: The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns. SOLUTION 2: Other way we can drop the user first and then the tablespace. No need to specify cascade option here. Thank You!

RECOVER WINDOW AND REDUNDANCY The redundancy retention policy defines a fixed number of backups that need to be retained by RMAN. If there are a number of backups later than the number defined by the retention policy, then those backups can be deleted using the DELETE OBSOLETE command, which we will discuss in the next section. To set a redundancy retention policy, use the configure retention policy command, as shown here: RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 7; RMAN> show all; RMAN configuration parameters are: RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1; - NOTE: Here it will keep only 1 backup copy. Rest all previous backups marked as OBSOLETE RMAN> REPORT OBSOLETE; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type                 Key    Completion Time    Filename/Handle -------------------- ------ ------------------ -----------