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'...
Saturday, November 30, 2013
Friday, November 29, 2013
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
$...
Thursday, November 28, 2013
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...
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 ...
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...
Wednesday, November 27, 2013
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;...
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>...
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...
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...
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...
