Anuj Singh Oracle DBA

Saturday, 25 June 2011

Unix Search a TEXT in file

Unix Search a staring
Search a TEXT in file
search a word in file

find . -type f -exec grep -il "iapi" {} \;

Friday, 24 June 2011

Is Oracle 32-bit or 64-bit and Oracle version ?

Oracle 32-bit or 64-bit ? oracle 32bit or 64bit oracle 32 bit or 64 bit Oracle version [code]

 
-bash-3.2$ sqlplus / as sysdba


apt-rdbms-01.tus.co.uk:APTDB\sys> 

select length(addr)*4 || '-bits' word_length 
from  v$process where rownum<2

-----
             
 
                                                                                                                 SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


or



SQL> define
DEFINE _DATE           = "04-AUG-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE _RC             = "0" (CHAR)




Oracle vesrion


col COMP_NAME format a50
SQL> r
  1* select COMP_NAME ,VERSION,STATUS from dba_registry

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Application Express                         4.0.2.00.07                    VALID
OWB                                                11.2.0.1.0                     VALID
OLAP Catalog                                       11.2.0.1.0                     VALID
Spatial                                            11.2.0.1.0                     VALID
Oracle Multimedia                                  11.2.0.1.0                     VALID
Oracle XML Database                                11.2.0.1.0                     VALID
Oracle Text                                        11.2.0.1.0                     VALID
Oracle Expression Filter                           11.2.0.1.0                     VALID
Oracle Rules Manager                               11.2.0.1.0                     VALID
Oracle Workspace Manager                           11.2.0.1.0                     VALID
Oracle Database Catalog Views                      11.2.0.1.0                     VALID
Oracle Database Packages and Types                 11.2.0.1.0                     VALID
JServer JAVA Virtual Machine                       11.2.0.1.0                     VALID
Oracle XDK                                         11.2.0.1.0                     VALID
Oracle Database Java Packages                      11.2.0.1.0                     VALID
OLAP Analytic Workspace                            11.2.0.1.0                     VALID
Oracle OLAP API                                    11.2.0.1.0                     VALID

17 rows selected.



perl $ORACLE_HOME/OPatch/opatch.pl version
Invoking OPatch 11.1.0.6.6

OPatch Version: 11.1.0.6.6

OPatch succeeded.


SELECT PLATFORM_NAME
    FROM V$TRANSPORTABLE_PLATFORM
    WHERE PLATFORM_ID =
       ( SELECT PLATFORM_ID FROM V$DATABASE );


SQL --  !lsnrctl version

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2011 09:09:24

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
        TNS for Linux: Version 11.2.0.1.0 - Production
        Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production,,
The command completed successfully

Thursday, 23 June 2011

To Change the permissions On Unix

To Change the permissions On Unix
Unix permissions
Unix permission

666 : Default permission
002 : - umask value
664 : final permission

#=root

# chmod 777 *.log

u user
g group
o others

Owner Group others Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute
5 (u+Rx) 5 (g+Rx) 5 (o+Rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character equivalents can be used in the chmod command .

#chmod o+rwx *.log
#chmod g+r *.log
#chmod -Rx *.log

chmod -R g+rw hft/

-R recursive

# chown -R oinstall.dba *

Owner Group The others Permission

7 7 7 read + write + execute
6 6 6 write + execute
5 5 5 read + execute
4 4 4 read only
2 2 2 write only
1 1 1 execute only

Oracle Create Soft link

-bash-3.2$ pwd
/usr/lib/oracle/instantclient10_1
-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1

ln -s libclntsh.so.10.1 libclntsh.so.11.1

-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 23 09:28 libclntsh.so.11.1 -> libclntsh.so.10.1
-bash-3.2$

Monday, 20 June 2011

Oracle patch info

on new metalink

for Oracle and grid patch info !!!!!!!!

KA958 Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases

KA958

https://support.oracle.com/support/?page=sptemplate&sptemplate=km-article&documentId=KA958

Quick Links

https://updates.oracle.com/Orion/QuickLinks/process_form?type=nonapps

oracle@apt-amd-02:/opt/app/oracle/product/11.2/OPatch> ./opatch lsinventory -bugs_fixed

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Oracle Home : /opt/app/oracle/product/11.2

Central Inventory : /opt/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.6

OUI version : 11.2.0.1.0

OUI location : /opt/app/oracle/product/11.2/oui

Log file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch2011-06-20_14-51-20PM.log

Patch history file: /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2011-06-20_14-51-20PM.txt

------------------------------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

------------------------------------------------------------------------------------------------------

OPatch succeeded.

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

opatch lsinventory -bugs_fixed | grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

to download opatch utility go to

metalink >> patch and update

then search for 6880880 patch and OS

-bash-3.00$ opatch lsinventory -detail

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

Oracle Home : /opt/oracle/product/10.2

Central Inventory : /opt/oracle/oraInventory

from : /var/opt/oracle/oraInst.loc

OPatch version : 10.2.0.4.3

OUI version : 10.2.0.4.0

OUI location : /opt/oracle/product/10.2/oui

Log file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/opatch2011-07-22_11-14-02AM.log

Lsinventory Output file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory2011-07-22_11-14-02AM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

There are 3 products installed in this Oracle Home.

Installed Products (188):

Agent Required Support Files 10.2.0.1.0

Agent Required Support Files Patch 10.2.0.4.0

Assistant Common Files 10.2.0.1.0

=======================

opatch lsinventory

select * from dba_registry_history;

select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

select * from sys.registry$history;

col COMP_NAME for a50

set lines 200

col action_time for a30

col comments for a30

col ACTION for a15

select action_time, action,namespace, version, id, bundle_series, comments from registry$history;

select action_time, action, version, id, comments from dba_registry_history order by action_time;

select COMMENTS, ACTION_TIME from sys.registry$history;

select COMP_NAME, VERSION, STATUS from dba_registry;

How to Use MOS Patch Planner to Check and Request the Conflict Patches? (Doc ID 1317012.1)

google it and find patch info

Map of Public Vulnerability to Advisory/Alert

http://www.oracle.com/technetwork/topics/security/public-vuln-to-advisory-mapping-093627.html

===========

to download

https://updates.oracle.com/download/6880880.html

unzip p6880880_210000_Linux-x86-64.zip -d $ORACLE_HOME/

set serveroutput on;

execute dbms_optim_bundle.getBugsforBundle;

12.2.0.1.190416DBRU:

Bug: 25405100, fix_controls: 25405100

execute dbms_optim_bundle.listBundlesWithFCFixes; ----<<<<

bundleId: 180116, bundleName: 12.2.0.1.180116DBRU

bundleId: 181017, bundleName: 12.2.0.1.181017DBRU

bundleId: 190115, bundleName: 12.2.0.1.190115DBRU

bundleId: 190416, bundleName: 12.2.0.1.190416DBRU

PL/SQL procedure successfully completed.

====

with a as

(select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

select x.*

from a,

xmltable('InventoryInstance/patches/*' passing a.patch_output

columns patch_id number path 'patchID',

patch_uid number path 'uniquePatchID',

description varchar2(80) path 'patchDescription',

applied_date varchar2(30) path 'appliedDate',

sql_patch varchar2(8) path 'sqlPatch',

rollbackable varchar2(8) path 'rollbackable') x

Oracle Database 19c Release Update & Release Update Revision October 2022 Known Issues (Doc ID 19202210.9)

RDBMS Online Patching Aka Hot Patching (Doc ID 761111.1)

https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=6880880



cd $ORACLE_HOME
mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

ls -ld OPatch*
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

===

cd $ORACLE_HOME
unzip -oq /home/oracle/Downloads/p6880880_210000_Linux-x86-64.zip

ls -ld OPatch*
drwxr-x---. 15 oracle oinstall 4096 Apr 15 14:41 OPatch
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

db_1]$

===

export PATH=$ORACLE_HOME/OPatch:$PATH

opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

or


./opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

***********

export OPATCH_DEBUG=TRUE  <<<< if requited


 export PATH=$ORACLE_HOME/OPatch:$PATH
 opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"

opatch lsinventory | grep -E "(^Patch)|(^Sub-patch)"


 export PATH=$ORACLE_HOME/OPatch:$PATH

to clean ..
 export PATH=$ORACLE_HOME/OPatch:$PATH
opatch util listorderedinactivepatches
opatch util deleteinactivepatches
opatch util cleanup


mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`
mv: cannot move 'OPatch' to 'OPatch.2024-12-13': Permission denied

[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ logout

login as root 

[root@oragrid 19.2.0]# cd /u01/app/21.3.0/grid

[root@oragrid grid]# id
uid=0(root) gid=0(root) groups=0(root)
[root@oragrid grid]# mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

[root@oragrid grid]# ls -ld OPatch*
drwxr-xr-x 13 grid oinstall 303 Jul 27  2021 OPatch.2024-12-13
[root@oragrid grid]#



As root 
[root@oragrid ~]# cd /u01/app/21.3.0/grid
[root@oragrid grid]# unzip -oq /home/grid/p6880880_210000_Linux-x86-64.zip

[root@oragrid grid]# ls -ld OPatch*
drwxr-x--- 15 root root     4096 Oct  4 19:48 OPatch
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2024-12-13


[root@oragrid grid]# pwd
/u01/app/21.3.0/grid


[root@oragrid grid]# chown -R grid:oinstall OPatch/

to download patch


https://support.oracle.com/support/?anchorId=&documentId=KA958&page=sptemplate&sptemplate=km-article


Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases
KA958

===================

for Grid



cd /u01/app/21.3.1/grid
mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall 303 Jul 27  2021 OPatch.2026-01-02


unzip -qqd /u01/app/21.3.1/grid /home/grid/p6880880_230000_Linux-x86-64.zip

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2026-01-02
drwxr-x--- 14 root root     4096 Oct  7 15:16 OPatch


chown -R grid:oinstall OPatch

[root@oragrid grid]# chown -R grid:oinstall OPatch

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2026-01

========================

opatch listorderedinactivepatches
opatch util deleteinactivepatches






set linesize 300 pagesize 300
  col action_time for a20
  col id for a10
  col action for a12
  col COMMENTS for a25
    select substr(action_time,1,30) action_time,substr(id,1,10) id,substr(action,1,10) action,substr(version,1,8) version,
	substr(BUNDLE_SERIES,1,6) bundle,substr(comments,1,20) comments 
	from registry$history;
  
  

set lines 132  verify  off head off  feedback on  long 1000
select distinct
'======================================================'||chr(10)||
'id...........................: '||id   		||Chr(10)||
'ACTION.......................: '||ACTION 		||Chr(10)||
'ACTION_TIME..................: '||ACTION_TIME 	||Chr(10)||
'NAMESPACE....................: '||NAMESPACE   	||Chr(10)||
'VERSION......................: '||VERSION   	||Chr(10)||
'BUNDLE_SERIES................: '||BUNDLE_SERIES||Chr(10)||
'COMMENTS.....................: '||COMMENTS    
from registry$history  ---<<<<---DBA_REGISTRY_HISTORY does not get updated  Bundle Patch
order by 1
/

set lines 132  verify  on head on  feedback on  long 1000


set linesize 300
with /*+ PARALLEL */  a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
     select x.*
       from a,
     xmltable('InventoryInstance/patches/*'
     passing a.patch_output
     columns
        patch_id number path 'patchID',
        patch_uid number path 'uniquePatchID',
        description varchar2(80) path 'patchDescription',
     constituent number path 'constituent',
     patch_type varchar2(20) path 'patchType',
     rollbackable varchar2(20) path 'rollbackable',
     sql_patch varchar2(8) path 'sqlPatch',
     DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'
   ) x
/



set lines 132 verify off head off feedback on long 1000

select distinct
  '======================================================'||chr(10)||
  'PATCH_ID.....................: '||PATCH_ID           ||chr(10)||
  'PATCH_UID....................: '||PATCH_UID          ||chr(10)||
  'ACTION.......................: '||ACTION             ||chr(10)||
  'ACTION_TIME..................: '||ACTION_TIME        ||chr(10)||
  'STATUS.......................: '||STATUS             ||chr(10)||
  'VERSION......................: '||VERSION            ||chr(10)||
  'BUNDLE_SERIES................: '||BUNDLE_SERIES      ||chr(10)||
  'BUNDLE_ID....................: '||BUNDLE_ID          ||chr(10)||
  'INSTALL_ID...................: '||INSTALL_ID         ||chr(10)||
  'FLAGS........................: '||FLAGS              ||chr(10)||
  'LOGFILE......................: '||LOGFILE            ||chr(10)||
  'POST_LOGFILE.................: '||POST_LOGFILE       ||chr(10)||
  'DESCRIPTION..................: '||DESCRIPTION
from sys.registry$sqlpatch
where 1=1
and STATUS not like '%ERROR%'
order by 1
/

select distinct
  '======================================================'||chr(10)||
  'PATCH_ID.....................: '||PATCH_ID           ||chr(10)||
  'PATCH_UID....................: '||PATCH_UID          ||chr(10)||
  'ACTION.......................: '||ACTION             ||chr(10)||
  'ACTION_TIME..................: '||ACTION_TIME        ||chr(10)||
  'STATUS.......................: '||STATUS             ||chr(10)||
  'VERSION......................: '||VERSION            ||chr(10)||
  'BUNDLE_SERIES................: '||BUNDLE_SERIES      ||chr(10)||
  'BUNDLE_ID....................: '||BUNDLE_ID          ||chr(10)||
  'INSTALL_ID...................: '||INSTALL_ID         ||chr(10)||
  'FLAGS........................: '||FLAGS              ||chr(10)||
  'LOGFILE......................: '||LOGFILE            ||chr(10)||
  'POST_LOGFILE.................: '||POST_LOGFILE       ||chr(10)||
  'DESCRIPTION..................: '||DESCRIPTION
from sys.registry$sqlpatch
where 1=1
--and STATUS not like '%ERROR%'
order by 1
/

set lines 132 verify on head on feedback on long 1000





alter session set nls_date_format='dd-mm-yyyy hh24:mi';
SET LINESIZE 500 PAGESIZE 1000  SERVEROUT ON LONG 2000000

COLUMN action_time FORMAT A26
COLUMN action FORMAT A29
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A50
COLUMN namespace FORMAT A20
COLUMN status FORMAT A16
COLUMN version FORMAT A10

--spool check_patches.txt

SELECT 
action_time
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;







SQL> alter session set "_oracle_script" = true;
SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed open read write;
SQL> create table registry$sqlpatch_org as select * from registry$sqlpatch ;
SQL> delete from registry$sqlpatch where PATCH_ID=xxx;
SQL> commit;
SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed OPEN READ ONLY;  
SQL> alter session set "_oracle_script"=FALSE;





Datapatch precheck failed : Release update is BEING installed to PDB but is not installed in CDB$ROOT.This will cause a patch mismatch between this PDB and CDB$ROOT (Doc ID 2604940.1)
Execute datapatch individually in each of the containers, starting with CDB$ROOT :


1. Apply datapatch is CDB$ROOT :
  ===================================
  ./datapatch -verbose -pdbs CDB\$ROOT

2. Apply datapatch is PDB :
  ===================================
  $ ./datapatch -verbose -pdbs <pdbname>

3. Apply datapatch is PDB$SEED :
  ==================================
  SQL> alter session set container=PDB$SEED;
  SQL> alter session set "_oracle_script"=TRUE;
  SQL> alter pluggable database pdb$seed close immediate instances=all;
  SQL> alter pluggable database pdb$seed OPEN READ WRITE;
  SQL> select open_mode from v$database;
  SQL> exit

  Execute: ./datapatch -verbose -pdbs PDB\$SEED

  SQL> alter session set "_oracle_script"=FALSE;
  
4. show pdbs -> If this shows the PDB$SEED in restricted mode, bounce the database once.

5. Check for the relevant patch existence in the registry$sqlpatch for all the containers (CDB$ROOT, PDB$SEED and PDB) :

  set linesize 150
  col logfile for a90
  select patch_id, action, logfile, status from registry$sqlpatch;



 $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/sqlpatch -I$ORACLE_HOME/rdbms/admin -I$ORACLE_HOME/sqlpatch/lib $ORACLE_HOME/sqlpatch/sqlpatch.pl $@

SQL Patching tool version 12.2.0.1.0 Production on Sun May 24 12:47:20 2026
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_116489_2026_05_24_12_47_20/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 260120 in the binary registry and ID 260120 in PDB CDB$ROOT, ID 260120 in PDB PDB$SEED, ID 260120 in PDB ORCLPDB1

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB1
    Nothing to roll back
    Nothing to apply

SQL Patching tool complete on Sun May 24 12:47:35 2026

Thursday, 16 June 2011

How To Resize the Online Redo Logfiles

How To Resize the Online Redo Logfiles
resize Redo Logfiles
how to drop redo logfile

Log status:

*UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS,when it is not the current redo log.

*CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

*ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

*CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared,the status changes to UNUSED.

*CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

*INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 CURRENT
2 50 INACTIVE
3 50 INACTIVE

col MEMBER format a40
select group#, member from v$logfile

GROUP# MEMBER
---------- ----------------------------------------
3 /opt/app/oracle/oradata/orcl/redo03.log
2 /opt/app/oracle/oradata/orcl/redo02.log
1 /opt/app/oracle/oradata/orcl/redo01.log

create new redo log files

alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;

SQL> alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

Database altered.

SQL> alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

Database altered.

SQL> alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;

Database altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT

SQL> alter database drop logfile group 2 ;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 CURRENT
6 ACTIVE

>>>>>>>>>>>>>>>>>>>>>>>>>>>wait some time <<<<<<<<<<<<<<<<<<<<<<<<

SQL> select group#, status from v$log;

now group 1 is inactive

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 INACTIVE
5 CURRENT
6 INACTIVE

now drop the 1

SQL> alter database drop logfile group 1;

Database altered.

finally delete the file from OS level

SQL> !
oracle@apt-amd-02:/opt/app/oracle/product/11.2/sysman/admin/emdrep/bin> cd /opt/app/oracle/oradata/orcl/

oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rm redo01.log redo02.log redo03.log

Tuesday, 14 June 2011

How to change the SYSMAN user password

sysman password change

SQL> !emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...

Cannot determine Oracle Enterprise Manager 11g Database Control process. /opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/emctl.pid does not exist.

SQL> !emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:~> emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
oracle@apt-amd-02:~> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 14:16:32 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sysman identified by vihaan123;

User altered.

$emctl setpasswd dbconsole

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman/config> emctl setpasswd dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.
oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config>

emctl start dbconsole

https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication

cd $ORACLE_HOME

oracle@apt-amd-02:/opt/app/oracle/product/11.2> cd orcl
-bash: cd: orcl: No such file or directory
oracle@apt-amd-02:/opt/app/oracle/product/11.2> cd apt-amd-02.aptus.co.uk_orcl/

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl> cd sysman/
oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman> ls -ltr
total 8
drwxr----- 3 oracle oinstall 17 2011-01-10 15:44 opmn
drwxr----- 4 oracle oinstall 4096 2011-01-10 15:46 config
drwxr-x--- 3 oracle oinstall 4096 2011-02-07 09:46 log
drwxr----- 5 oracle oinstall 152 2011-02-07 11:14 emd
drwxr----- 4 oracle oinstall 30 2011-02-07 12:00 recv

go to this file

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman/config> vi emoms.properties

search for this file

oracle.sysman.eml.mntr.emdRepPwd=89b2c01e41fc69697990aae431c32604

to

oracle.sysman.eml.mntr.emdRepPwd=vihaan123

Search for the line:

oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE to oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config> emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ........

=================

# Check that the password has been encrypted
Edit the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties

1. Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
2. Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE

#Tue Jun 14 14:41:57 GMT 2011
oracle.sysman.emSDK.svlt.ConsoleServerName=apt-amd-02.aptus.co.uk\:1158_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=04154901177159bfb36808486e4de27b <<<<<---- check this this should be encrypted
emdrep.ping.pingCommand=/usr/sbin/ping6
em_oob_shutdown=false
LargeRepository=false
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=orcl.aptus.co.uk
EMD_URL=https\://apt-amd-02.aptus.co.uk\:3938/emd/main
em_email_address=%EM_EMAIL_ADDRESS%
oracle.sysman.eml.mntr.emdRepPwdSeed=8927514978106692198
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
em_oob_crash=false
em.oms.dumpModules=omsThread,repos
oracle.sysman.emRep.dbConn.statementCacheSize=50
oracle.sysman.db.isqlplusUrl=http\://apt-amd-02.tus.co.uk\:/isqlplus/dynamic
em_oob_startup=false
oracle.sysman.emSDK.svlt.ConsoleServerPort=1158
oracle.sysman.eml.mntr.emdRepRAC=FALSE
em_from_email_address=%EM_FROM_EMAIL_ADDRESS%
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE <<<<<<----- check this this should be true
oracle.sysman.db.isqlplusWebDBAUrl=http\://apt-amd-02.tus.co.uk\:/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=apt-amd-02.aptus.co.uk
oracle.sysman.eml.mntr.emdRepDBID=1267852645
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=1158
em_email_gateway=%EM_EMAIL_GATEWAY%
oracle.sysman.eml.mntr.emdRepServer=apt-amd-02.aptus.co.uk
oracle.sysman.eml.mntr.emdRepSID=orcl
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=apt-amd-02.aptus.co.uk)(PORT\=1521)))(CONNECT_DATA\=(SERVICE_NAME\=orcl.aptus.co.uk)))
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.emkeyfile=/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config/emkey.ora
em.ip.ui.enable=true
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.emSDK.svlt.PublicServletEnabled=true