Thursday, December 6, 2012

Multiplexing Controlfile through RMAN in RAC

Login to the first node

column is_recovery_dest_file format a25
column name format a60
set linesize 160
select status, name, is_recovery_dest_file from v$controlfile;


check the control file status and names using the above quey,


i my case i had


STATUS  NAME                                                         IS_RECOVERY_DEST_FILE
------- ------------------------------------------------------------ -------------------------
        +DATA_RAJ/rajesh/controlfile/current.986.790135655           NO

hence ,

login in to sqlplus , and alter the system as below with your new place pointed to

alter system set control_files = '+DATA_RAJ/rajesh/controlfile/current.986.790135655','+RECO_RAJ' scope=spfile sid='*';


in my case i have the data and reco groups created on the asm, hence trying to place the controlfiles in to two different disk groups.


once alterd the system , shutdown the instance using srvctl


srvctl stop database -d xxx

after then , login to sql prmpt and startup the instance in no mount stage , once the db came up on nomount stage exit from the sql prompt and login in rman prompt

example

rman

connect  target

restore controlfile from '+DATA_RAJ/rajesh/controlfile/current.986.790135655';


it will restore/multiplex our control file to the existing and our new path. once the restore complete, shutdown the instances and startup normal

it should come up with the two controlfiles,

issues i was facing here were, one of the instance spfile was not on the shared drive hence i was failing on the below command

alter system set control_files = '+DATA_RAJ/rajesh/controlfile/current.986.790135655','+RECO_RAJ' scope=spfile sid='*';


as spfile could not be modifiable etc ... after which i have mounted the spfile on the shared drive and followed the same

Good Luck !!


Thanks
- No comments:

Monday, September 17, 2012

How to backup oracle home

Follow the steps to backup oracle home


say if u have the oracle home like /opt/oracle/product/11.2.0/ then the binaries , and let say u want to have the home backup taken to some backup directory

cd to the home directory

cd /opt/oracle/product/11.2.0

and then issue

tar -cvf - . |compress > /backup/11.2.0.tar.Z


if u want to untar it

u use tar -xvf
- No comments:

How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema

Refference with the document How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema [ID 1030426.6]


Hi Had faced an issue where the database was created manually some way got corrupted when running the immediate scripts after the db creation catproc/catalog like the pupbld etc ..


due with that i was getting issues like memory issue 4031 , and even i am not able to query the dba tables or the register views, followed the above document that resolved the issue simply the below were the steps that i followed


Below is a SQL*Plus script that will list all objects that have been created in both the SYS and SYSTEM schema: 

column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
   (select object_name||object_type 
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';


The output from this script will either be 'zero rows selected' or will look something like the following:
OBJECT_NAME                OBJECT_TYPE
------------------------------ -------------
ALL_DAYS                       VIEW
CHAINED_ROWS                   TABLE
COLLECTION                     TABLE
COLLECTION_ID                  SEQUENCE
DBA_LOCKS                      SYNONYM
DBMS_DDL                       PACKAGE
DBMS_SESSION                   PACKAGE
DBMS_SPACE                     PACKAGE
DBMS_SYSTEM                    PACKAGE
DBMS_TRANSACTION               PACKAGE
DBMS_UTILITY                   PACKAGE


If the select statement returns any rows then this is an indication that at least 1 script has been run as both SYS and SYSTEM.

Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.


EXCEPTION TO THE RULE

THE REPLICATION SCRIPTS (XXX) CORRECTLY CREATES OBJECTS WITH THE SAME NAME IN THE SYS AND SYSTEM ACCOUNTS. LISTED BELOW ARE THE OBJECTS USED BY REPLICATION THAT SHOULD BE CREATED IN BOTH ACCOUNTS. DO NOT DROP THESE OBJECTS FROM THE SYSTEM ACCOUNT IF YOU ARE USING REPLICATION. DOING SO WILL CAUSE REPLICATION TO FAIL!

The following objects are duplicates that will show up (and should not be removed) when running this script in 8.1.x and higher.

Without replication installed:
INDEX           AQ$_SCHEDULES_PRIMARY
TABLE           AQ$_SCHEDULES

If replication is installed by running catrep.sql:
INDEX           AQ$_SCHEDULES_PRIMARY
PACKAGE         DBMS_REPCAT_AUTH
PACKAGE BODY    DBMS_REPCAT_AUTH
TABLE           AQ$_SCHEDULES

When database is upgraded to 11g using DBUA, following duplicate objects are also created
OBJECT_NAME                OBJECT_TYPE
------------------------------ -------------
Help                          TABLE
Help_Topic_Seq                  Index

The objects created by sqlplus/admin/help/hlpbld.sql must be owned by SYSTEM because when sqlplus retrieves the help information, it refers to the SYSTEM schema only. DBCA runs this script as SYSTEM user when it creates the database but DBUA runs this script as SYS user when upgrading the database (reported as an unpublished BUG 10022360).  You can drop the ones in SYS schema.

Now that you have a list of duplicate objects you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.


If the list of objects is large then you may want to use the following SQL*Plus script to automatically generate an SQL script that contains the appropriate DROP commands:

set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
   (select object_name||object_type 
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit


You will now have a file in the current directory named dropsys.sql that contains all of the DROP commands. You will need to run this script as a normal SQL script as follows:

$ sqlplus
SQL*Plus: Release 3.3.2.0.0 - Production on Thu May  1 14:54:20 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
Enter user-name: system
Enter password: manager
SQL> @dropsys

Note: You may receive one or more of the following errors:
ORA-2266 (unique/primary keys in table referenced by enabled foreign keys): 
If you encounter this error then some of the tables you are dropping have constrints that prevent the table from being dropped. To fix this problem you will have to manually drop the objects in a different order than the script does.
     
ORA-2429 (cannot drop index used for enforcement of unique/primary key):
 This is similar to the ORA-2266 error except that it points to an index. You will have to manually disable the constraint associated with the index and then drop the index.

ORA-1418 (specified index does not exist):
 This occurs because the table that the index was created on  has already been dropped which also drops the index. When the script tries to drop the index it is no longer there and thus the ORA-1418 error. You can safely ignore this error.


- No comments:

To find if the database is patched with CPU or PSU

Pls run the below query to find which cpu psu patch applied on the database ,



col ACTION format a15
col NAMESPACE format a15
col version format a10
col COMMENTS format a42
col BUNDLE_SERIES format a20
col action_time format a30
select * from sys.registry$history;




For PSU:

opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU'

FOR CPU:

Issue the following select to list which CPU is implemented into each database:

set linesize 90
set pagesize 100
select substr(action_time,1,30) action_time,
substr(id,1,8) id,
substr(action,1,10) action,
substr(version,1,8) version,
substr(comments,1,20) comments
from registry$history;



- No comments:

Export errors EXP-00008: ORA-04067: ORA-06508:ORA-06512:ORA-06512:EXP-00083:

While trying to export the db i received the below error

EXP-00008: ORACLE error 4067 encountered
ORA-04067: not executed, package body "SYS.DBMS_REPCAT_UTL" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 45
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling

select grantee,owner,table_name,privilege from dba_tab_privs where grantee='EXPORT_USER';


Solution 
 
 
The workaround for the problem is to grant the two missing privileges explicitly to the user doing the export:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO ;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO ;




There are two execute privileges missing in the DBA role and  the EXP_FULL_DATABASE role.

There is no fix in this bug because the export utility is not longer 
supported in 11g and should be replaced by the Data Pump Export.
- No comments:

Sunday, September 16, 2012

Enabling archivelog mode 


SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG
 
 
show parameter archive 
 
will tell u the archive destination etc ...



so , create spfile from pfile incase of the db running onthe pfile means, then shutdown startup mount and follow the steps 

SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.
 
 
ou can see here that we put the database in ARCHIVELOG mode
by using the SQL statement "alter database archivelog", but 
Oracle won't let us do this unless the instance is mounted but
not open.  To make the change we shutdown the instance, and then
startup the instance again but this time with the "mount" option
which will mount the instance but not open it.  Then we can
enable ARCHIVELOG mode and open the database fully with the "alter
database open" statement.
There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST; 
 
- No comments:

Saturday, September 15, 2012

Last analyzed dates in oracle

Description

Lists the last analyze date for tables, indexes and partitions. This script should be used to determine is any stats are out of date. 

The Last_analyzed column being NULL indcates no stats are present

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 200

col index_owner form a10
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by owner, index_name
/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in ('SYS','SYSTEM')
order by index_owner, index_name, partition_name
/

spool off
- No comments:

Wednesday, September 12, 2012

ORA-12547: TNS:lost contact ( From Rman as well as from Sql plus

- No comments:

Monday, September 3, 2012

Users having dba role in Oracle database

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

select * from dba_role_privs where granted_role='DBA';

 GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES


- No comments:

Monday, August 13, 2012

Oracle import process hanged !! how to handle

Hi

Some times, i use to do a dev refreshes without checking the pre- requisites like size , object counts etc .. but i will pay my time for the same when i see the import taking long time than i expected.

so the below query can be used to see what actually the import process is currently doing inside the db , like which table is getting loaded how many rows are getting updated till  now and we can calculate how long the processs could take etc .. it is a use full quey which i frequently use


select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;


and basically check if the imp process still exist on the server

ps -ef | grep imp
- No comments:

oracle user password expired locked

In 11 r2 , were facing this user lock and expire issue frequently to override it


select profile from dba_users where username ='xxxx';

alter profile default limit passsword_life_time unlimited;
alter profile default limit failed_login_attempts unlimited;


select resource_name,limit from dba_profiles where profile='';
- No comments:

Wednesday, March 28, 2012

Oracle Database scripts basic




Database Scripts Library Index [ID 131704.1]
Modified 03-NOV-2011     Type REFERENCE     Status ARCHIVED

Database Scripts Last updated on October 23, 2009
This document has been archived and will no longer be updated.
AdvancedQueueing ContentMgmt.Spatial ContentMgmt.Text ContentMgmt.UltraSearch
ContentMgmt.XDB ContentMgmt.interMedia DBA.Admin DBA.Architecture
DBA.DBCreationAndConfiguration DBA.Monitoring DBA.SQL DBA.Storage
DBWarehouse.MaterialView DBWarehouse.ParallelExecution DBWarehouse.Partitioning Distributed.General
Distributed.Replication Distributed.Streams Globalization Globalization.DST
HighAvailability.BR HighAvailability.Corruption HighAvailability.DataGuard HighAvailability.RMAN
Install.Installer Install.UnixGeneric JVM Manageability.MemoryMgmt
Manageability.Utilities.ExportImport Manageability.Utilities.SqlLoader OLAP.ExpressServer Performance.Database
Performance.Locking Performance.SqlTuning Scalability.OPS Scalability.RAC
Security.DBSecurity Storage.Exadata
AdvancedQueueing - Advanced Queuing
[Top]
ContentMgmt.Spatial - Oracle Intermedia Spatial and Spatial Data Option
[Top]
ContentMgmt.Text - Oracle Text (formerly interMedia Text)
[Top]
ContentMgmt.UltraSearch - Oracle Ultra Search
[Top]
ContentMgmt.XDB - XML Database
[Top]
ContentMgmt.interMedia - Oracle interMedia (Image, Video, Sound etc)
[Top]
DBA.Admin - General DBA Activities (Copying DB etc...)
[Top]
DBA.Architecture - Oracle Server Architecture (processes and SGA)
[Top]
DBA.DBCreationAndConfiguration - Database Create and Configuration
[Top]
DBA.Monitoring - Database Monitoring
[Top]
DBA.SQL - SQL Scripts, Examples and Reference Information
[Top]
DBA.Storage - Space Management and Object Storage
[Top]
DBWarehouse.MaterialView - Materialized Views - Distributed & Local Summary
[Top]
DBWarehouse.ParallelExecution - Parallel Execution
[Top]
DBWarehouse.Partitioning - Partitioning
[Top]
Distributed.General - Distributed Database Issues
[Top]
Distributed.Replication - Master Replication
- No comments:
Subscribe to: Posts (Atom)

How to Trouble shoot Logfile_sync wait event

   First  Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is). select s...