Tag: 10g

Checkpoint Not Complete

Posted on by Osama Mustafa in Database
Error Will be In AlertSID.log Like This :

/u01/app/oracle/oradata/redo04.log
Thu Jan 14 22:12:55 2011
Thread 1 cannot allocate new log, sequence 46352
Checkpoint not complete

To Solve this Issue you Can Do More Than One Thing But All of the Solution is Simple :

Solution One :

Modify Database Parameter archive_lag_target Like This :

alter system set archive_lag_target=0 scope=both;

Solution two :

1-backup Full Database
2-Check Free Disk Space
3-Start Do the Following :

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#, status from v$log;

Now You need to Work Step by Step On Inactive Logs , and Stat Drop Them like the following :

alter database drop logfile group 1;

Re add the Log File

alter database add logfile group 1 ( ‘/Log-Name01.log‘,‘/Log-Name02.log’ ) size 75M

alter system switch logfile; / Alter System Checkpoint

 Do this For The Group that you have , and rearrange them again By Adding new Group contain more than one redo log inside them

Simple !!!

Enjoy
Osama Mustafa

ORA-29701: Unable to connect to Cluster Manager

Posted on by Osama Mustafa in Database
ORA-29701: Unable to connect to Cluster Manager.

The reason is Oracle cssd daemon process was not running.

As Root User :

Step 1: Go to the /etc folder
Step 2: Execute the following command.

# init.cssd start

startup will be queued to init within 30 seconds

Step 3: Wait for 2 minutes and execute the following commands

# cd $ORACLE_HOME/bin
# ./localconfig delete

Stopping CSSD
Failure in CSS initialization opening OCR.
Shutdown has begun. The daemons should exit soon.

Step 4: Execute the following command

# ./localconfig add

Successfully accumulated necessary OCR keys.
Creating OCR keys for user root, privgrp ‘system’
Operation Successful
Configuration for local css has been initialized.
Adding to inittab
Startup will be queued to init within 30 seconds
Checking the status of new oracle init process..
Expecting the CRS daemons to be up within 600 seconds
CSS is active on these nodes
nodelist
CSS is active on all nodes
Oracle CSS service is installed and running under init(1M)

Step 5: Start the ASM instance now.

drop all tables in a tablespace

Posted on by Osama Mustafa in Scripts
set echo off
prompt
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt
prompt specify tablespace name 1:
define tsname=&1
prompt Add purge clause, y for yes, n for no 2:
define prg=&2
prompt enter output sql file name 3:
define filename=&3
set heading off
set verify off
set feedback off
start del_ts_tb.sql
set verify on
set heading on
set echo on
set feedback on
spool &filename
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off 




Osama mustafa

Documents For Upgrade Oracle 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
I Upload Documents for Upgrading Oracle 10g to 11gR2

The Following Documents like the followings :


1- Upgrade Oracle 10g Clusterware to 11gR2 Clusterware.

 ClusterWare Upgrade

2- Upgrade ASM 10g to 11gR2 .

ASM Upgrade

3- Upgrade Database 10g to 11gR2 .

Database Upgrade

Enjoy

Thank you
Osama mustafa

Clone Oracle Database Steps

Posted on by Osama Mustafa in Database
First :  What is Oracle Database Clone :

complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.

Second : Benefit Of Clone : 

1-useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. 
2-quickly migrate a system from one server to another .
3-fastest way to copy a Oracle database .

How We do it :


Step-1 : On the old system, go into SQL*Plus, sign on as SYSDBA and issue: 

SQL>alter database backup controlfile to trace;

copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system .


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 (‘/u01/oradata/oldlsq/log1a.dbf’,
‘/u01/oradata/olslsq/log1b.dbf’) SIZE 30M,
GROUP 2 (‘/u01/oradata/oldlsq/log2a.dbf’,
‘/u01/oradata/oldlsq/log2b.dbf’) SIZE 30M
DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

 

 Step-2 : Shutdown Old Database

SQL>Shutdown immediate ;

Step-3 : Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: 


CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS

CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS

 Step-4 :Remove the “recover database” and “alter database open” syntax 


Step-5:Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.

Old:

DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’

New:

DATAFILE
‘/u01/oradata/newlsq/system01.dbf’,
‘/u01/oradata/newlsq/mydatabase.dbf’

Step-6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

Step-7 : Create the bdump, udump and cdump directories
  

 cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

Step-8 : Copy-over the old init.ora file 


open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora 
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

Step-9 : Start the new database

SQL>startup nomount pfile=’ /u01/admin/newlsq/pfile/newlsq.ora ‘ ;
SQL> @dbclone_controlfile_creation.sql
SQL>alter database open resetlogs;

SQL>create spfile from pfile;

 Step-10 : Place the new database in archivelog mode.




Thank you 
osama mustafa

delete a duplicate rows in a oracle table

Posted on by Osama Mustafa in Scripts 
 
Check the below Steps to delete duplicate row : 
 
 

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10005 palani                             54544         10
 10001 Scott Tiger                     1000           40
 10002 Frank Naude                 500             20
 10003 rajesh                            21000         140
 10004 ramesh                           3500          220
 10005 suresh                            34567        123
 
 

delete duplicate rows in empid column in emp table 

 
 

 SQL>delete from emp where rowid not in (select max(rowid) from emp group by empid);


1 row deleted.

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10001 Scott Tiger                       1000         40
 10002 Frank Naude                   500           20
 10003 rajesh                             21000        140
 10004 ramesh                           3500           220
 10005 suresh                            34567        123
 
 
to delete the old duplicate row from the table instead of max(rowid) replace min(rowid) 
for exampl
 

SQL>insert into emp values(10005,’Osama’,54544,10);


1 row created.

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                           54544        10
   10001 Scott Tiger                     1000          40
   10002 Frank Naude                 500            20
   10003 rajesh                            21000        140
   10004 ramesh                          3500          220
   10005 suresh                           34567        123

6 rows selected.

SQL>delete from emp where rowid not in (select min(rowid) from emp group by empid);


1 row deleted.
 

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                            54544      10
   10001 Scott Tiger                      1000        40
   10002 Frank Naude                  500          20
   10003 rajesh                             21000      140
   10004 ramesh                           3500        220 
 
 
thank you 
Osama mustafa

DBCA hangs at 85%

Posted on by Osama Mustafa in Database
cause:

kernel BUG at kernel/exit.c:904!
kernel/exit.c:904!

solution:

Upgraded the Kernel to version RHEL4.7

Refer to Metalink Note : 729543.1
Summary by kernel version number:

Bug Introduced in RHEL 4.6:

Broken in RHEL kernel : 2.6.9-67.0.20.EL
Fixed in  RHEL kernel : 2.6.9-67.0.22.EL 

Broken in OEL kernel : 2.6.9-67.0.20.0.1.EL
Fixed in  OEL kernel : 2.6.9-67.0.20.0.2.EL
Fixed in  OEL kernel : 2.6.9-67.0.22.0.1.EL


4.7:
 

Broken in RHEL kernel: 2.6.9-78.EL
Fixed in RHEL kernel : 2.6.9-78.0.1.EL

Broken in OEL kernel : N/A -  OEL 4.7 base (GA) kernel includes the fix for 
                                 this crash (2.6.9-78.0.0.0.1.EL)
Fixed in OEL kernel  : 2.6.9-78.0.0.0.1.EL
Fixed in OEL kernel  : 2.6.9-78.0.1.0.1.EL
 

Upgrade Steps From 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
Please Follow the Below Steps For Upgrading 10g to 11gR2 , Before Doing Anything you need to take backup for the following Files :
1.      BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED.
2.      COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0.  THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Step-1 :
 Install  New Oracle Database Software & Apply Any Patches Necessary.

Step-2 :

Run The Pre-Upgrade Information Tools : (Mandatory) 


1.      Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2.      Set your environment to the one that is being upgraded.  Assuming 10g.
3.      Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4.      Start SQL*Plus and login as ‘/ as sysdba’
5.      Spool the results to a log file:
o   SQL> SPOOL upgrade_info.log
6.      Run the Pre-Upgrade Information Tool:
o   SQL> @utlul12i.sql
o   SQL> SPOOL OFF 

Open The Spool File for Checking Purpose :
 Oracle recommends gathering stats before the upgrade:  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin

Step-3 : 
Prepare 11gR2 Home :

1.      Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2.      Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3.      Update any relative path names in parameter file to fully path names.

Step-4 : 
After all the Above Steps , you will start the upgrade Database for 10g Home :

1.      Shutdown the database:
o   SQL> SHUTDOWN IMMEDIATE;
2.      Make sure the following checks:
o   The oratab file points to Oracle Database 11g Release 2 Oracle Home
o   The following environment variables point to the Oracle 11g Release 2 directories:
§  ORACLE_HOME
§  PATH
3.      Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o   sqlplus ‘/ as sysdba’
4.      Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
o   SQL>  STARTUP UPGRADE;
o   SQL>  SPOOL upgrade.log
5.      Run the catupgrd.sql script:
o   SQL>  @catupgrd.sql
6.      Once completed, shutdown the database and restart it.
o   SQL>  SHUTDOWN IMMEDIATE;
o   SQL>  STARTUP;
7.      Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)
o   SQL> @utlul12s.sql
8.      Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
o   SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9.      Run utlrp to recompile any remaining stored PL/SQL and other objects.
o   SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10.  Verify that all objects are valid:
o   SQL> SELECT count(*) FROM dba_invalid_objects;
o   SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thank you 
Osama mustafa

 


Oracle Real Application Cluster Managment

Posted on by Osama Mustafa in Database
1. CRSCTL: 

Cluster Control utility performs various administrative operation of oracle clusterware. It is located in $ORA_CRS_HOME/bin and must be executed by the “root” user.

a. To check the current state of all oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

b.You can also check the state of individual oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check cssd
CSS appears healthy

[root@rac1 bin]# ./crsctl check crsd
CRS appears healthy

[root@rac1 bin]# ./crsctl check evmd
EVM appears healthy

c. To start oracle clusterware

[root@rac1 bin]# ./crsctl start crs

Attempting to start CRS stack
The CRS stack will be started shortly

d. To stop oracle clusterware

[root@rac1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

e. To disable  oracle clusterware:

[root@rac1 bin]# ./crsctl disable crs

f.  To enable  oracle clusterware:

[root@rac1 bin]# ./crsctl enable crs

g. To get current value of CSS parameter

[root@rac1 bin]# ./crsctl get css
for  example: to get value of misscount parameter
 [root@rac1 bin]# ./crsctl get css misscount
60

h. To set a new value of CSS parameter

[root@rac1 bin]# ./crsctl set css
for  example: to set value of misscount parameter

[root@rac1 bin]# ./crsctl set css misscount 120
Configuration parameter misscount is now set to 120.

i. To unset CSS parameter value

[root@rac1 bin]# ./crsctl unset css
for  example: to unset value of misscount parameter

[root@rac1 bin]# ./crsctl unset css misscount
Configuration parameter misscount is now undefined.

j. To list the module for debugging in CSS

[root@rac2 bin]# ./crsctl lsmodules css

The following are the CSS modules ::
CSSD
COMMCRS
COMMNS

2. CRS_STAT:
 It reports the current state of resources configured in the OCR.

[oracle@rac1 bin]$ ./crs_stat -t
Name                      Type              Target       State         Host
———————————————————————————–
ora….C1.inst             application    ONLINE    ONLINE          rac1
ora….C2.inst             application    ONLINE    ONLINE          rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora.RAC.abc.cs          application    ONLINE    ONLINE          rac1
ora.RAC.db                application    ONLINE    ONLINE         rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora….ice2.cs             application    ONLINE    ONLINE          rac1
ora….AC1.srv            application    ONLINE    ONLINE          rac1

3. CRS_STOP:

 This command used to stop resource or cluster member.

[root@rac1 bin]# ./crs_stop ora.rac1.ons
Attempting to stop `ora.rac1.ons` on member `rac1`
Stop of `ora.rac1.ons` on member `rac1` succeeded.

4.  CRS_START:

 This command used to start resource or cluster member.

[root@rac1 bin]# ./crs_start ora.rac1.ons
Attempting to start `ora.rac1.ons` on member `rac1`
Start of `ora.rac1.ons` on member `rac1` succeeded.

5.  OCRDUMP :

 It dumps the contents of OCR into a text file.

 [root@rac1 bin]# ./ocrdump /home/oracle/ocr.dmp

6. OCRCHECK :

 It verifies the integrity of the OCR.

[root@rac2 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :
Version                                :    2
Total space (kbytes)         :    5237072
Used space (kbytes)         :    9360
Available space (kbytes)  :    5227712
ID                                        :    794527192
Device/File Name             :    /apps/oracle/oradata/ocr
Device/File integrity check succeeded
Cluster registry integrity check succeeded

7. OCRCONFIG:

 It perform various administrative operation on the OCR.

Enabling and Disabling Database Options

Posted on by Osama Mustafa in Database
All New From Oracle ….
Amazing Tools , If you install Oracle Database After that you remember that you should enable/Disable some Feature Like Oracle Security ad you Forget Script That Located in RDBMS/admin the What Reinstall Again

In ORACLE 11gR2 you Can do this Easily Just Follow The Below Steps :

The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin directory. The syntax for chopt is as follows:

chopt [ enable | disable] db_option
 

The possible values for db_option described in the following table.

Value Description
dm Oracle Data Mining RDBMS Files
dv Oracle Database Vault
lbac Oracle Label Security
olap Oracle OLAP
partitioning Oracle Partitioning
rat Oracle Real Application Testing
ode_net_2 Oracle Database Extensions for .NET 2.0

 Example :

To enable the Oracle Label Security option in your Oracle binary files

1-Shut down the database with srvctl or SQL*Plus:  

SQL>Shutdown immediate (single node)
 
srvctl stop database -d ORCL

2-Run the following commands: 

cd %ORACLE_HOME%/bin
chopt enable lbac

3-Start Database : 

srvctl start database -d ORCL
OR 
SQL>Startup  (Single Node)

In linux the “chopt” statement will show the following output 

[oracle@localhost ~]$ chopt
 
usage:
 
chopt [enable|disable] {option}
 
options:
                  dm = Oracle Data Mining RDBMS Files
                  dv = Oracle Database Vault option
                lbac = Oracle Label Security
                olap = Oracle OLAP
        partitioning = Oracle Partitioning
                 rat = Oracle Real Application Testing
 
e.g. chopt enable rat

 Thank you
Osama Mustafa