Oracle

How to create a database link in Oracle?

The syntax has been enhanced to remove the need to epscify a TNS service_name:

CREATE PUBLIC DATABASE LINK <Link_Name>  CONNECT TO <remote_username>
IDENTIFIED BY <PWD>
USING ”tns_service_name‘;

 

 

Advertisements
Standard
Oracle

Oracle Trace file (.trc) or Trace map (.trm) cleanup

To clean up old Oracle trace file (.trc) and trace map (.trm) files, use this command on UNIX based system:

Connect sqlplus :

Ekran Resmi 2017-11-29 12.20.07

 

And,

find /u01/app/oracle/diag/rdbms/sbuat01/SBUAT01/trace/*.trc -mtime +14 -exec rm {} \;

find /oracle/diag/rdbms/ssekdb/ssekdb/trace/*.trm -mtime +14 -exec rm {} \;

The above command will delete trace files and trace map files which is older than 14 days.

Standard
Oracle

Flash Recovery Area

Setting up a Flash Recovery Area

The recovery area is defined by setting two initialization parameters. These two parameters can be dynamically altered or disabled.

  • The db_recovery_file_dest_size sets the disk limit, expressed in bytes
  • The db_recovery_file_dest sets the location for the recovery area

Enabling a Flash Recovery Area

This statement sets the disk limit for recovery area to 100 GB:

SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 100G

This statement sets the recovery area destination:

SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST = ‘/dba/backup/’;

Alter a Flash Recovery Area

This statement alters the size of the recovery area:

SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 200G;

Disabling a Flash Recovery Area

This statement disables a recovery area:

SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST = ‘ ’;

 

Other views that we can get information about redo logs;

-Live log information
SELECT * FROM v $ archived_log k ORDER BY k.first_time DESC;

-General redo log information (size, status etc.)
SELECT * FROM v $ log;

-Redo log directories
SELECT * FROM v $ logfile;

– Redo log history
SELECT * FROM v $ log_history

Standard
Oracle

Oracle Database Disk Size

select

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Size in GB”

from

dual;

Standard
Troubleshooting

ERROR: ORA-00257: archiver error. Connect internal only, until freed.

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 27 08:31:46 2017

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

ERROR:

ORA-00257: archiver error. Connect internal only, until freed.

Fast solution is to simply increase the value for db_recovery_file_dest_size.

Steps:

  1. SQL> sho parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /recoveryarea
db_recovery_file_dest_size           big integer 200G
SQL>

 

2.Overall space usage in /recoveryarea mountpoint

$ df -h /recoveryarea/$ORACLE_SID/archivelog/
Filesystem             size   used  avail capacity  Mounted on
/recoveryarea           98G    55G    42G    57%    /recoveryarea

Space usage in database /recoveryarea directory.

$ du -sh /recoveryarea/$ORACLE_SID/archivelog/
  10G    /recoveryarea/$ORACLE_SID/archivelog

3. SQL> alter system set db_recovery_file_dest_size = 15G;

SQL> alter system set db_recovery_file_dest_size = 15G;

System altered.

SQL>

 

 

Standard
Troubleshooting

ORA-01950: no privileges on tablespace ‘USERS’ [closed]

The problem was with quota on the USERS tablespace. Granting unlimited quota for the user on the USERS tablespace resolved the problem:

You cannot insert data because you have a quota of 0 on the tablespace. To fix this, run

ALTER USER <user> quota unlimited on <tablespace name>;

or

ALTER USER <user> quota 100M on <tablespace name>;
Standard
Oracle

CpuCount, SCSI, RAM SIZE 

Cpu Count:

specifies the number of CPUs available for Oracle Database to use

select value   from v$parameter where name like ‘cpu_count’;

SCSI:

To display the SCSI devices currently attached (and recognized) by the SCSI subsystem use.

cat /proc/scsi/scsi
The output looks like this:

[oracle@alsm ~]$ cat /proc/scsi/scsi

Attached devices:

Host: scsi0 Channel: 00 Id: 00 Lun: 00

  Vendor: VMware   Model: Virtual disk     Rev: 1.0

  Type:   Direct-Access                    ANSI  SCSI revision: 02

Host: scsi0 Channel: 00 Id: 01 Lun: 00

  Vendor: VMware   Model: Virtual disk     Rev: 1.0

  Type:   Direct-Access                    ANSI  SCSI revision: 02

Host: scsi0 Channel: 00 Id: 02 Lun: 00

  Vendor: VMware   Model: Virtual disk     Rev: 1.0

  Type:   Direct-Access                    ANSI  SCSI revision: 02

Host: scsi2 Channel: 00 Id: 00 Lun: 00

  Vendor: NECVMWar Model: VMware IDE CDR10 Rev: 1.00

  Type:   CD-ROM                           ANSI  SCSI revision: 05

RAM SIZE 
select max(value)/1024/1024 c1  from dba_hist_osstat
where stat_name = ‘PHYSICAL_MEMORY_BYTES’;

C1
———-
15879.0313

 

Standard