Network, Oracle

Changing Listener Port Number for Database Connection Requests

To change the listener port number for database connection requests to 1522:To change the listener port number for database connection requests to 1522:
Stop the listener.
See “Stopping and Starting the Listener” for instructions.
Open the file listener.ora with a text editor.
Table: Location of the listener.ora File shows the location of this file on each platform.
Location of the listener.ora File
Platform LocationLinux
cd $ORACLE_HOME/network/admin/vi listener.ora
Windows
c:\oracledba\app\oracle\product\10.2.0\server\NETWORK\ADMIN\

Locate the following section of the file:
LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))      (ADDRESS = (PROTOCOL = TCP)(HOST = dberman)(PORT = 1521))    )  )
Note that the line indicated in bold may or may not be present in the file.
Change the text (PORT = 1521) to (PORT = 1522).
Save the modified listener.ora file.
Start the listener.
See “Stopping and Starting the Listener” for instructions.
Start SQL Command Line and connect to the database as user SYSTEM.
See “Connecting Locally with SQL Command Line” for instructions. You must supply the SYSTEM password. You set this password upon installation (Windows) or configuration (Linux) of Oracle Database dba.
Enter the following two commands:
ALTER SYSTEM SET LOCAL_LISTENER = “(ADDRESS=(PROTOCOL=TCP)(HOST=dberman.mydomain.com)(PORT=1522))”;
ALTER SYSTEM REGISTER;
Exit SQL Command Line and run the lsnrctl status command to verify the port number change.
The new port number should be displayed in the Listening Endpoints Summary section of the status report, and the report should include the following lines:
Service “dba” has 1 instance(s).  Instance “dba”, status READY, has 1 handler(s) for this service…

Advertisements
Standard
Oracle

change / move the Oracle Inventory

Hi,

The below steps will help you to change / move the Oracle Inventory to other locations for Linux flavor setups:

Find the current location of the Oracle Inventory (default $ORACLE_BASE/oraInventory):

For example:

[oracle@tto ~]$ cd $ORACLE_BASE

[oracle@tto app]$ pwd
/opt/app

find . -name oraInventory -print

[oracle@tto app]$ find . -name oraInventory -print
find: ./oracle/product/11.2.0/db_1/opmn/conf: Permission denied
find: ./oracle/product/11.2.0/db_1/opmn/logs: Permission denied
find: ./oracle/product/11.2.0/db_1/inventory/ContentsXML/ConfigXML: Permission denied
find: ./oracle/product/11.2.0/db_1/cfgtoollogs/cfgfw: Permission denied
./oraInventory

Open the oraInst.loc file in /etc/ and check the value of inventory_loc

[oracle@tto app]$ cat /etc/oraInst.loc
inventory_loc=/opt/app/oraInventory
inst_group=oinstall

Copy the oraInventory directory to the destination directory

cp -Rp /opt/app/oraInventory /home/oracle/oraInventory

Edit the oraInst.loc file to point to the new location

For example:

vi /etc/oraInst.loc
inventory_loc=/home/oracle/oraInventory inst_group=dba

Note: The oraInst.loc file is simply a pointer to the location of the Oracle Inventory (oraInventory)

Regards, Erman

Standard
Linux

Change Diskgroup Redundancy – ASM

 

(Change Diskgroup Redundancy)

1. Create a new Diskgroup with desired redundancy and move the existing data to newly created Diskgroup.
2. Drop the existing Diskgroup after backing up data and create a new Diskgroup with desired redundancy.
Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.
1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it.
— Initially we have two diskgroup with external redundancy as:
SQL> select state,name from v$asm_diskgroup;
STATE NAME———– ——————–MOUNTED ERMAN

2) Create a new diskgroup with normal redundancy as :
SQL > create diskgroup ERMANNEW normal redundancy failgroup
SQL> select state,name,type from v$asm_diskgroup;
STATE NAME TYPE———– ——————- ——MOUNTED ERMAN EXTERNMOUNTED ERMANNEW NORMAL
3)Backup the current database as follows:
SQL> show parameter db_name
NAME TYPE VALUE—————- ———– —————————-db_name string orcl10g
SQL> create pfile=’/tmp/ermantest/demo/initorcl+ASM.ora’ from spfile;
SQL> alter database backup controlfile to ‘+ERMANNEW’;
SQL> alter system set control_files=’+ERMANNEW\ORCL11G\CONTROLFILE\<system generated control file name from diskgroup ERMANNEW>’ SCOPE=SPFILE;

— Connect to rman$ rman target /
RMAN > shutdown immediate;
RMAN > startup nomount;
RMAN> restore controlfile to ‘<new_diskgroup +ERMANNEW>’ from ‘+ERMAN\ORCL10G\CONTROLFILE\mycontrolfile.ctl’;
RMAN > alter database mount;
RMAN> backup as copy database format ‘+ERMANNEW’;

4)Switch the database to copy. At this moment we are switching to the new Diskgroup
RMAN> switch database to copy;
RMAN> recover database ;
This will recover the backup controlfile taken and restored before to be in sync with database/datafiles

RMAN> alter database open resetlogs;

5)Add new tempfile to newly created database.
SQL> alter tablespace TEMP add tempfile ‘+ERMANNEW’ SIZE 10M;

Drop any existing tempfile on the old diskgroup

SQL> alter database tempfile ‘+ERMAN/orcl10g/tempfile/temp.265.626631119’ drop;

6)Find out how many members we have in redolog groups, make sure that we have only one member in each log group.(drop other members).
Suppose we have 3 log groups, then add one member to each log group as following:
SQL> alter database add logfile member ‘+ERMANNEW’ to group 1;
SQL> alter database add logfile member ‘+ERMANNEW’ to group 2;;
SQL> alter database add logfile member ‘+ERMANNEW’ to group 3;
Then we can drop the old logfile member from earlier diskgroups as:
SQL> alter database drop logfile member ‘complete_name’;

7)Use the following query to verify that all the files are moved to new diskgroup with desired redundancy:

SQL> select name from v$controlfileunionselect name from v$datafileunionselect name from v$tempfileunionselect member from v$logfileunionselect filename from v$block_change_tracking;

+ERMANNEW/orcl/controlfile/backup.261.980091055
+ERMANNEW/orcl/datafile/sysaux.263.980093225
+ERMANNEW/orcl/datafile/system.262.980093223
+ERMANNEW/orcl/datafile/undotbs1.264.980093229
+ERMANNEW/orcl/datafile/users.265.980093229
+ERMANNEW/orcl/onlinelog/group_1.266.980093395
+ERMANNEW/orcl/onlinelog/group_3.268.980093417
+ERMANNEW/orcl/tempfile/temp.267.980094287

good.
8) Enable block change tracking using  ALTER DATABASE command.
SQL> alter database enable block change tracking using file ‘<FILE_NAME>’;

asm disk group changed fto erman to ermannew. have a good day.

ASM

Standard
AIX, Troubleshooting

HPUX-ia64 Error: 28: No space left on device

Hello, problem HPUX-ia64 Error: 28: No space left on device .

ROCPSTEST oracle@rocpstest:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 27 14:04:48 2018

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

ERROR:
ORA-09817: Write to audit file failed.
HPUX-ia64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

I solve it immediately. Is database   runnig ?

ROCPSTEST oracle@rocpstest:/home/oracle>ps -ef |grep smon
root 5200 1 0 Nov 12 ? 7:31 /etc/opt/resmon/lbin/p_client
root 4443 1 0 Nov 12 ? 5:35 /etc/opt/resmon/lbin/emsagent
oracle 5452 1 0 Nov 12 ? 9:25 asm_smon_+ASM
oracle 9690 9400 0 14:06:07 pts/1 0:00 grep smon

yes. After this I checked space available in the FS. I got this:
ROCPSTEST oracle@rocpstest:/home/oracle> df -g
/home (/dev/vg00/lvol4 ) :
8192 file system block size 8192 fragment size
655360 total blocks 652013 total free blocks
646929 allocated free blocks 163232 total i-nodes
163000 total free i-nodes 163000 allocated free i-nodes
1073741828 file system id vxfs file system type
0x10 flags 255 file system name length
/home file system specific string
/opt (/dev/vg00/lvol5 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 497345 total free blocks
493471 allocated free blocks 214080 total i-nodes
124307 total free i-nodes 124307 allocated free i-nodes
1073741829 file system id vxfs file system type
0x10 flags 255 file system name length
/opt file system specific string
/oracle (/dev/vgrocps/lvol3 ) :
8192 file system block size 1024 fragment size
157286400 total blocks 0 total free blocks  -> the problem is here
0 allocated free blocks 365152 total i-nodes
155812 total free i-nodes 155812 allocated free i-nodes
1073872899 file system id vxfs file system type
0x10 flags 255 file system name length
/oracle file system specific string
/rocps_data2 (/dev/vgrocps/lvol1 ) :
8192 file system block size 8192 fragment size
268451840 total blocks 220795054 total free blocks
219085406 allocated free blocks 56915456 total i-nodes
55198754 total free i-nodes 55198754 allocated free i-nodes
1073872897 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_data2 file system specific string
/rocps_home2 (/dev/vgrocps/lvol2 ) :
8192 file system block size 1024 fragment size
62914560 total blocks 53639679 total free blocks
50290045 allocated free blocks 13412360 total i-nodes
13409919 total free i-nodes 13409919 allocated free i-nodes
1073872898 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_home2 file system specific string
/tmp (/dev/vg00/lvol6 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 1198551 total free blocks
1189189 allocated free blocks 301152 total i-nodes
299637 total free i-nodes 299637 allocated free i-nodes
1073741830 file system id vxfs file system type
0x10 flags 255 file system name length
/tmp file system specific string
/usr (/dev/vg00/lvol7 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 860929 total free blocks
854207 allocated free blocks 245344 total i-nodes
215213 total free i-nodes 215213 allocated free i-nodes
1073741831 file system id vxfs file system type
0x10 flags 255 file system name length
/usr file system specific string
/var (/dev/vg00/lvol8 ) :
8192 file system block size 8192 fragment size
3682304 total blocks 2872951 total free blocks
2850644 allocated free blocks 770176 total i-nodes
718236 total free i-nodes 718236 allocated free i-nodes
1073741832 file system id vxfs file system type
0x10 flags 255 file system name length
/var file system specific string
/stand (/dev/vg00/lvol1 ) :
8192 file system block size 8192 fragment size
262144 total blocks 191626 total free blocks
190135 allocated free blocks 48256 total i-nodes
47901 total free i-nodes 47901 allocated free i-nodes
1073741825 file system id vxfs file system type
0 flags 255 file system name length
/stand file system specific string
/ (/dev/vg00/lvol3 ) :
8192 file system block size 8192 fragment size
262144 total blocks 228445 total free blocks
226673 allocated free blocks 59808 total i-nodes
57110 total free i-nodes 57110 allocated free i-nodes
1073741827 file system id vxfs file system type
0x10 flags 255 file system name length
/ file system specific string
ROCPSTEST oracle@rocpstest:/home/oracle>ls -la

I changed /oracle directory space size:

ROCPSTEST oracle@rocpstest:/home/oracle>df -g
/home (/dev/vg00/lvol4 ) :
8192 file system block size 8192 fragment size
655360 total blocks 652013 total free blocks
646929 allocated free blocks 163232 total i-nodes
163000 total free i-nodes 163000 allocated free i-nodes
1073741828 file system id vxfs file system type
0x10 flags 255 file system name length
/home file system specific string
/opt (/dev/vg00/lvol5 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 497345 total free blocks
493471 allocated free blocks 214080 total i-nodes
124307 total free i-nodes 124307 allocated free i-nodes
1073741829 file system id vxfs file system type
0x10 flags 255 file system name length
/opt file system specific string
/oracle (/dev/vgrocps/lvol3 ) :
8192 file system block size 1024 fragment size
157286400 total blocks 41163168 total free blocks
38629464 allocated free blocks 10499968 total i-nodes
10290792 total free i-nodes 10290792 allocated free i-nodes
1073872899 file system id vxfs file system type
0x10 flags 255 file system name length
/oracle file system specific string
/rocps_data2 (/dev/vgrocps/lvol1 ) :
8192 file system block size 8192 fragment size
268451840 total blocks 220794999 total free blocks
219085351 allocated free blocks 56915456 total i-nodes
55198747 total free i-nodes 55198747 allocated free i-nodes
1073872897 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_data2 file system specific string
/rocps_home2 (/dev/vgrocps/lvol2 ) :
8192 file system block size 1024 fragment size
62914560 total blocks 53640771 total free blocks
50291069 allocated free blocks 13412632 total i-nodes
13410191 total free i-nodes 13410191 allocated free i-nodes
1073872898 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_home2 file system specific string
/tmp (/dev/vg00/lvol6 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 1198551 total free blocks
1189189 allocated free blocks 301152 total i-nodes
299637 total free i-nodes 299637 allocated free i-nodes
1073741830 file system id vxfs file system type
0x10 flags 255 file system name length
/tmp file system specific string
/usr (/dev/vg00/lvol7 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 860929 total free blocks
854207 allocated free blocks 245344 total i-nodes
215213 total free i-nodes 215213 allocated free i-nodes
1073741831 file system id vxfs file system type
0x10 flags 255 file system name length
/usr file system specific string
/var (/dev/vg00/lvol8 ) :
8192 file system block size 8192 fragment size
3682304 total blocks 2873730 total free blocks
2851423 allocated free blocks 770176 total i-nodes
718413 total free i-nodes 718413 allocated free i-nodes
1073741832 file system id vxfs file system type
0x10 flags 255 file system name length
/var file system specific string
/stand (/dev/vg00/lvol1 ) :
8192 file system block size 8192 fragment size
262144 total blocks 191626 total free blocks
190135 allocated free blocks 48256 total i-nodes
47901 total free i-nodes 47901 allocated free i-nodes
1073741825 file system id vxfs file system type
0 flags 255 file system name length
/stand file system specific string
/ (/dev/vg00/lvol3 ) :
8192 file system block size 8192 fragment size
262144 total blocks 228507 total free blocks
226735 allocated free blocks 59808 total i-nodes
57110 total free i-nodes 57110 allocated free i-nodes
1073741827 file system id vxfs file system type
0x10 flags 255 file system name length

after delete old *trm and *trc files older than 7 days.

find /oracle/diag/rdbms/rocpstest/ROCPSTEST/trace/*.trc -mtime +7 -exec rm {} \;

find /oracle/diag/rdbms/rocpstest/ROCPSTEST/trace/*.trm -mtime +7 -exec rm {} \;

connect to oracle as sysdba:

ROCPSTEST oracle@rocpstest:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 27 15:42:44 2018

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select version from v$instance;

VERSION
—————–
11.2.0.4.0

SQL>

 

problem is solved,

Br.Erman

Standard
Oracle

How to kill session in Oracle RAC database?

We use v$session for indetifying target session to kill in non-rac database.Here in Oracle RAC instance we should need to check and identify session using gv$session. Because only gv$session contains rac instance identifier column.

SQL>select sid,serial#,inst_id from gv$session where machine=’GPTWORKGROUP’;
SID SERIAL# INST_ID
——— ——— ——-
9 171 1
1 row selected
SQL> alter system kill session ‘9,171,@1’;
System altered.
OR optionally you can use following command to kill session in Oracle RAC…
SQL>alter system kill session ‘9,171,@1’ immediate;
System altered.

In the single database ;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||sid||’,’||serial#||’;’ FROM v$session;

Standard
Oracle

alter database set time_zone doesn’t work

Hello, We have a Oracle database 12c  run on Sun Solaris Server, the Unix server timezone is set to ‘EST'(Eastern Standard Time). When we created the database, the db timezone wasn’t explicitly specified. So when I issued the following statement:
“Select dbtimezone from dual”
I got +02:00

Syntax :

ALTER DATABASE <my db name> SET TIME_ZONE = ‘US/Eastern’
or
ALTER DATABASE <my db name> SET TIME_ZONE = ‘EST’

What do I do to change my Oracle timezone?

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-02:00

clear@db> alter database set time_zone = ‘EST’;
Database altered.

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-03:00
clear@db> connect / as sysdba;
Connected.
clear@db> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
clear@db> startup
ORACLE instance started.

Total System Global Area 358909124 bytes
Fixed Size 279748 bytes
Variable Size 335544320 bytes
Database Buffers 16777216 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.

clear@db> select dbtimezone from dual;
DBTIMEZONE
—-
EST

*******

For OMS( Oracle Management Server ) , log into the OMS repository as the SYSMAN user, the above command ’emctl resetTZ agent’ tells you exactly what needs to be executed in the repository.

SQL> exec mgmt_target.set_agent_tzrgn(‘‘, ‘Etc/GMT-6’);
SQL> commit;
SQL> emctl start agent — (or emctl start dbconsole)

 

Br. Erman.

Standard
RMAN

Oracle Incremental Backup Level 0 or 1

A level 1 incremental backup can be either of the following types:

  • differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
  • cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Incremental backups are differential by default.

Incremental Backup full, 0 or  1 :

Backup level Advantage Disadvantage
Full backup Recovery requires just one single read form the backup storage device.No dependency between the two full backup. If one is lost you may use another recent full backup to recover certain amount of data, not everything. The type of backup cause longest type of outage during the backup time.It is the most expensive type of backup because it takes maximum amount of storage space among all types of backup.It also takes  much longer time to run than its other counterparts.
incremental Less media storage is required since it back up only the files that changed since the last backup.It take much less time in comparison to other types of backup. Recover requires a full backup and all the incremental backup.A complete data recovery depends on the all the incremental backups and the last full backup.
differential since differential backup takes backup of a series of changes since the last full backup, it requires less of number of backup sets to restore.They provide efficient recovery when full backup is taken rarely( e.g. monthly)  The amount of storage required for a differential backup may exceed than that is required for incremental backup when significant amount of data changes regularly.

Incremental_backup

Incremental_backup._1JPG

The following commands performs a level 0 backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

 

The following commands performs a level 1 backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;
br: erman.
Standard