Oracle - Listener

Examples:

-- SQL> ALTER SYSTEM SET service_names='svr_oracle_prd.com.br' SCOPE=MEMORY SID='orcl_prd';

-- SQL> show parameter service_names;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string svr_oracle_prd.com.br




oracle@svr-oracle01 trace]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 28-NOV-2013 10:05:19

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                27-NOV-2013 01:08:38
Uptime                    1 days 8 hr. 56 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/svr-oracle01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.57)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.21)(PORT=1521)))

Edit: 

vim | vi  $ORACLE_HOME/network/admin/tnsnames.ora

NAME_ALIAS_BASE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IP_BASE )(PORT = PORT_LISTENER ))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
   (SERVICE_NAME = SERVICE_NAMES )
             )
    )

Read More

Oracle - CONNECT_PERL_CMD

use strict;
use warnings;


my $connect_string = 'username/password@server'; # connection to the DB
my $file = 'test.sql'; # location of SQL file. The file must end with "exit"

my $sqlcmd = "sqlplus -S $connect_string \@$file"; # sqlcommand
system $sqlcmd; # executes command

-

use warnings;
use DBI;
use strict;

my $user = 'system';
my $passwd = 'pass';
my $dbh = DBI->connect("dbi:Oracle:host=192.168.0.208;sid=ORAPRD;  port=1521", $user, $passwd)  || die "Database connection not made: $DBI::errstr";
$dbh->disconnect;

Read More

Oracle - Alter case sensitive

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
sec_case_sensitive_logon             boolean     TRUE
SQL> alter system set sec_case_sensitive_logon=false;

Sistema alterado.

SQL> show parameter case;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
sec_case_sensitive_logon             boolean     FALSE
SQL>

Read More

Oracle - Analyze Template for Windows

FILE 1 *.bat

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1\BIN\
set PATH=D:\oracle\product\10.2.0\db_1\BIN\

set ORACLE_SID=STDBY
sqlplus USER/PASS @D:\scripts\Analyze.sql

exit;

FILE 2 *.sql

set linesize 180
set pagesize 5000

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
spool D:\scripts\analyze.log
select sysdate from dual;

--select owner,SEGMENT_NAME from dba_segments where SEGMENT_NAME='TB_NAME';

--select owner, object_name, object_type  from ALL_OBJECTS where object_name ='TB_NAME';

exec dbms_stats.gather_schema_stats(ownname=>'OWNER',tabname=>'TB_NAME',cascade=>true,options=>'GATHER');

exec dbms_stats.gather_table_stats(ownname=>'OWNER',tabname=>'TB_NAME',cascade=>true);

spool off

exit;

Read More

Oracle - Changing Datafile Size [ Alter autoextend / maxsize ]

#!/usr/bin/perl
use strict;
use warnings;

#select file_id ,Tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY nextmb from dba_data_files where Tablespace_name='SYSAUX';

#select file_id, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY nextmb from dba_data_files where Tablespace_name='TS_DATA_01';


#column file_name format a45;
#select file_id ,file_name,Tablespace_name from dba_data_files;


my @to_8000 = (1..10);
my @to_4000 = (1..10);

print "\nproducao\n";

for (@to_8000){
   
    print "alter database datafile $_ autoextend on next 500m maxsize 8000m;\n";
   
}  

print "\naerp\n";

for (@to_4000){
   
    print "alter database datafile $_ autoextend on next 500m maxsize 4000m;\n";
   
}  

Example:


producao
alter database datafile 1 autoextend on next 500m maxsize 8000m;
alter database datafile 2 autoextend on next 500m maxsize 8000m;

stdby
alter database datafile 1 autoextend on next 500m maxsize 4000m;
alter database datafile 2 autoextend on next 500m maxsize 4000m;
alter database datafile 3 autoextend on next 500m maxsize 4000m;
alter database datafile 4 autoextend on next 500m maxsize 4000m;
alter database datafile 5 autoextend on next 500m maxsize 4000m;
alter database datafile 6 autoextend on next 500m maxsize 4000m;

Read More

Oracle - Alter database to archivelog mode

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     208

Current log sequence           218

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5745586176 bytes

Fixed Size                  2238736 bytes

Variable Size            3791652592 bytes

Database Buffers         1946157056 bytes

Redo Buffers                5537792 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     208

Next log sequence to archive   218

Current log sequence           218

Read More

Parameter on bash.

MacBook-de-Andre-Silva als$ cat tmp.sh
#!/bin/bash

echo spool  /home/analyze_$2.log
MacBook-de-Andre-Silva als$ ./tmp.sh - info_parameter
spool /home/analyze_info_parameter.log
MacBook-de-Andre-Silva als$ ./tmp.sh - info_number
spool /home/analyze_info_number.log
MacBook-de-Andre-Silva als$

Read More