Oracle - RMAN RESTORE DB

SQL> select dbid from v$database; PRD

      DBID
----------
 250744165

BASE :TESTE_RESTORE

alter system set db_create_file_dest='D:\Oracle\oradata\wms1' scope=spfile;

alter system set db_file_name_convert='E:\ORACLE\ORADATA\WMS','D:\Oracle\oradata\wms1' scope=spfile;

alter system set log_file_name_convert='E:\ORACLE\ORADATA\WMS','D:\Oracle\oradata\wms1' scope=spfile;

alter system set db_create_online_log_dest_1='D:\Oracle\oradata\wms1' scope=spfile;




$ rman target / nocatalog

RMAN> SET DBID=ID_PRD   
RMAN> RMAN RESTORE CONTROLFILE FROM ‘ FILE.BAK’

RMAN> RMAN CATALOG START WITH ‘PATH_BACKUP’;

RMAN> RESTORE DATABASE;

Or Rename datafile destination 

run{
set newname for datafile 1 to ‘d:\oracle\oradata\wms\’;
set newname for datafile 2 to ‘d:\oracle\oradata\wms\’;
set newname for datafile 3 to ‘d:\oracle\oradata\wms\’;
set newname for datafile 4 to ‘d:\oracle\oradata\wms\’;
set newname for datafile 5 to 'd:\oracle\oradata\wms\';
set newname for datafile 6 to 'd:\oracle\oradata\wms\';
set newname for datafile 7 to 'd:\oracle\oradata\wms\';
set newname for datafile 8 to 'd:\oracle\oradata\wms\';
set newname for datafile 9 to 'd:\oracle\oradata\wms\';
set newname for datafile 10 to 'd:\oracle\oradata\wms\';
set newname for datafile 11 to 'd:\oracle\oradata\wms\';
set newname for datafile 12 to 'd:\oracle\oradata\wms\';
set newname for datafile 13 to 'd:\oracle\oradata\wms\';
set newname for datafile 14 to 'd:\oracle\oradata\wms\';
set newname for datafile 15 to 'd:\oracle\oradata\wms\';
set newname for datafile 16 to 'd:\oracle\oradata\wms\';
set newname for datafile 17 to 'd:\oracle\oradata\wms\';
set newname for datafile 18 to 'd:\oracle\oradata\wms\';
set newname for datafile 19 to 'd:\oracle\oradata\wms\';
set newname for datafile 20 to 'd:\oracle\oradata\wms\';
set newname for datafile 21 to 'd:\oracle\oradata\wms\';
set newname for datafile 22 to 'd:\oracle\oradata\wms\';
set newname for datafile 23 to 'd:\oracle\oradata\wms\';
set newname for datafile 24 to 'd:\oracle\oradata\wms\';
set newname for datafile 25 to 'd:\oracle\oradata\wms\';
set newname for datafile 26 to 'd:\oracle\oradata\wms\';
set newname for datafile 27 to 'd:\oracle\oradata\wms\';
restore database;
}

Read More

Oracle - Automating Database Startup / Linux

# vi /etc/oratab


SINTEXA:
[NOME_DA_INSTÂNCIA]:[ORACLE_HOME]:[Y/N]

[NOME_DA_INSTÂNCIA] : USER/ORACLE $echo $ORACLE_SID;
[ORACLE_HOME] : USER/ORACLE  $echo $ORACLE_HOME.
[Y/N] : Y= Automating Startup /  N= No Automating Startup  .

Example

root@aix(/)# su - oracle
[YOU HAVE NEW MAIL]
$ . ./profileoracle
$ echo $ORACLE_HOME
/opt/app/oracle/product/11.2.0/db_1
$ echo $ORACLE_SID
orcl
$ cat /etc/oratab
orcl:/opt/app/oracle/product/11.2.0/db_1:Y              # line added by Agent
#orcl:/opt/app/oracle/product/11.2.0/db_1:N             # line added by Agent

Read More

Lunix - SSH Authorized

Machine Authorized

[root@srv-gama .ssh]# ssh-keygen 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
[root@srv-gama .ssh]#
[root@srv-gama .ssh]#
[root@srv-gama .ssh]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
5c:26:56:84:00:63:a8:5e:12:6a:40:e2:0d:c1:78:45 root@srv-gama
[root@srv-gama .ssh]# ls -lat
total 24
drwx------  2 root root 4096 Jun 20 08:36 .
-rw-------  1 root root 1675 Jun 20 08:36 id_rsa
-rw-r--r--  1 root root  395 Jun 20 08:36 id_rsa.pub
drwxr-x--- 25 root root 4096 Jun 20 08:29 ..
-rw-r--r--  1 root root 1183 Jul 10  2009 known_hosts
[root@srv-gama .ssh]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
79:ad:fc:b1:a9:19:6d:6d:5d:f4:ec:08:c8:6e:ef:76 root@srv-gama
[root@srv-gama .ssh]# ls -lat
total 32
drwx------  2 root root 4096 Jun 20 08:36 .
-rw-------  1 root root  672 Jun 20 08:36 id_dsa
-rw-r--r--  1 root root  603 Jun 20 08:36 id_dsa.pub
-rw-------  1 root root 1675 Jun 20 08:36 id_rsa
-rw-r--r--  1 root root  395 Jun 20 08:36 id_rsa.pub
drwxr-x--- 25 root root 4096 Jun 20 08:29 ..
-rw-r--r--  1 root root 1183 Jul 10  2009 known_hosts
[root@srv-gama .ssh]# more id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAvyxuEL8ruqzgQQd3yT9kpjQDCoOw1lyxNo9/OVtXkrgOQjS5uc4gpPgZULmHsafsFAP4Rv6JVevD3zyQP30tF/Yr04Hqe8vVVlyj962SWGvd2/T8Nk7jAFvdDVlrnkzUya++QmrUxJZlADF
PfPxquBJbHOuguhZcJmQN5nzPlvEMyiI1JpcT8kIReE8iNMfQ9Bwr2J4wPZRhc5AfLjjXgxP7356Zzss3YcRGbO4NAx+h0SN5ULingkrdjqrj9TLfem+uLj5KFt2dgHdoPvW+FdEHEEgrjngzzaGpRXn7zle5wg7cWoJCmWhM2C9AACPlbk
ev5hmWwid+r68FxX/jXw== root@srv-gama
[root@srv-gama .ssh]# more id_dsa.pub 
ssh-dss AAAAB3NzaC1kc3MAAACBANrW/auJEH+kvPFZ5WDaBULUv/sUVEOWkhpvSmEFg9WWoceMsBG9wvZWON3VzrLjwbvJRckxzUIzHUx3oQhNo2GYT++SNI4oMPv9M7/a1tdVcIwtpvhwQ/DolzQjmUvuPs8ErcvW9oxgbglLI/UjESa
t6f1HEAyMJHAsVT76K753AAAAFQDX306rlTgEZ2nNnO3ROQ8W4UE8cQAAAIEA0FfFW+i+xm/sL0HVJRx6ax83ul1QClOwi3tSugNCFyTIWhrNMv8UfA+Dx4x1r2vx/l9+1S8NLTnob9nQVmCv4Qrtv5VRQwriGA2wagnfXGcSjbVE6Dq3PE
DQB8oxFNHy3N4JXF0wCJZGpRvMNl77mcXE7Jz46Dhxi7NSaMpXvdsAAACBANFma3xWHXqP/d6bbVpzMlmgzMHZR8rjAh7AlyKj2FNiqgXBxuoJTls9mEJf/2sJksrAi1bqzLGH8jw/6qENPYnwHJeFQigogr7/KdZOtf5hWV96sYfxTwUG0
0nKRR8jyBr9v3JJVggoRsIB2/B6iVRC5BFoYXG0VSHNFUeJaLRV root@srv-gama



Machine destination

[root@srv-alpha .ssh]# ls -lat
total 16
drwxr-x--- 22 root root 4096 Jun 20 10:01 ..
drwx------  2 root root 4096 Jul 15  2010 .
-rw-r--r--  1 root root  395 Jul 15  2010 known_hosts
[root@srv-alpha .ssh]# vi authorized_keys

COPY TEXT "ssh-dss  + ssh-rsa"  >>  authorized_keys

Read More

Oracle - Alter profile / user [ ORA-01017/ ORA-28009 ]

SQL> connect sys as sysdba

Enter password: 

Connected.

SQL> SELECT * FROM DBA_PROFILES;

PROFILE         RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT         COMPOSITE_LIMIT KERNEL  UNLIMITED

DEFAULT         SESSIONS_PER_USER KERNEL  UNLIMITED

DEFAULT         CPU_PER_SESSION KERNEL  UNLIMITED

DEFAULT         CPU_PER_CALL KERNEL  UNLIMITED

DEFAULT         LOGICAL_READS_PER_SESSION KERNEL  UNLIMITED

DEFAULT         LOGICAL_READS_PER_CALL KERNEL  UNLIMITED

DEFAULT         IDLE_TIME KERNEL  UNLIMITED

DEFAULT         CONNECT_TIME KERNEL  UNLIMITED

DEFAULT         PRIVATE_SGA KERNEL  UNLIMITED

DEFAULT         FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT         PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT         PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT         PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT         PASSWORD_VERIFY_FUNCTION  PASSWORD NULL

DEFAULT         PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT         PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

SQL> ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

Profile altered.

SQL> SELECT * FROM DBA_PROFILES;

PROFILE         RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT         COMPOSITE_LIMIT KERNEL  UNLIMITED

DEFAULT         SESSIONS_PER_USER KERNEL  UNLIMITED

DEFAULT         CPU_PER_SESSION KERNEL  UNLIMITED

DEFAULT         CPU_PER_CALL KERNEL  UNLIMITED

DEFAULT         LOGICAL_READS_PER_SESSION KERNEL  UNLIMITED

DEFAULT         LOGICAL_READS_PER_CALL KERNEL  UNLIMITED

DEFAULT         IDLE_TIME KERNEL  UNLIMITED

DEFAULT         CONNECT_TIME KERNEL  UNLIMITED

DEFAULT         PRIVATE_SGA KERNEL  UNLIMITED

DEFAULT         FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT         PASSWORD_LIFE_TIME PASSWORD UNLIMITED

DEFAULT         PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT         PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT         PASSWORD_VERIFY_FUNCTION  PASSWORD NULL

DEFAULT         PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT         PASSWORD_GRACE_TIME PASSWORD 7

SQL> connect sys 
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect sys as sysdba

Enter password: 

Connected.

-- USER IN LOCK

SQL> alter user sys identified by PASS account unlock;

User altered.

[oracle@svr-oracle01 ~]$ sqlplus sys/PASS@oralee

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 28 10:50:23 2013

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

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Enter user-name: 

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name: 

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@svr-oracle01 ~]$ oerr ORA 28009

28009, 00000, "connection as SYS should be as SYSDBA or SYSOPER"

// *Cause:    connect SYS/<password> is no longer a valid syntax

// *Action:   Try connect SYS/<password> as SYSDBA or

//            connect SYS/<password> as SYSOPER

//

[oracle@svr-oracle01 ~]$ sqlplus sys/PASS as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 28 10:51:28 2013

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

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> exit

# ALTER PASS

SQL> alter user USER identified by PASS;

User altered.

Read More

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