Showing posts with label RMAN. Show all posts

Wednesday, December 10, 2014

Data Base, linux, ORA-, Oracle, Query, RMAN, Windows

ORACLE - Swiss penknife QUERY

Swiss penknife QUERY

SHOW AND KILL USER CONNECTED 

SQL> alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='TESTE';

SHOW SIZE BY DATAFILE

SQL> select file_id, block_id, blocks*8192/1024 MB,

owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"

from  sys.dba_Extents

where file_id = &&fileid

union

select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P

osition MB"

from sys.dba_free_space

where file_id = &fileid

order by 1,2,3

/

SHOW INFO BY OBJECTS

SQL> select owner, object_name, object_type, status from all_objects where object_name='TSILGT';

OWNER          OBJECT_NAME        OBJECT_TYPE   STATUS

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

TREINA          STP_GRAVATABLOG        PROCEDURE    VALID

SANKHYA          STP_GRAVATABLOG        PROCEDURE    VALID

WMS          STP_GRAVATABLOG        PROCEDURE    VALID

TESTE          STP_GRAVATABLOG        PROCEDURE    INVALID

SQL> alter procedure teste.STP_GRAVATABLOG compile;

Warning: Procedure altered with compilation errors.

SQL> show error

Errors for PROCEDURE TESTE.STP_GRAVATABLOG:

LINE/COL ERROR

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

33/5  PL/SQL: SQL Statement ignored

46/9  PL/SQL: ORA-00942: table or view does not exist

SQL> SELECT text FROM all_source WHERE name = 'STP_GRAVATABLOG'  and owner='TESTE' ORDER BY line;

SHOW SIZE BY TABLE OR SEGMENTS

SQL>select sum(bytes/1024/1024) as Tamanho_MB from dba_segments

where owner = 'SVP'

and segment_type = 'TABLE'

and segment_name in ('TABLE_1','TABLE_2','TABLE_N')

SQL>select onwer, segment_name, segment_type, tablespace_name, bytes/1024/1024 

from dba_segments where segment_name='TABLE_NAME';

SHOW DATAFILES INFO GENERAL/STATUS 

SQL> select tablespace_name, file_name, bytes/1024/1024 mbytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 maxbytes, INCREMENT_BY/1024/1024 nextmb from dba_data_files;

TABLESPACE FILE_NAME     MBYTES AUT   MAXBYTES   NEXTMB

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

USERS    /u01/app/oracle/oradata/XE/users.dbf     100 YES 11264 .001220703

SYSAUX    /u01/app/oracle/oradata/XE/sysaux.dbf    710 YES 32767.9844 .001220703

UNDOTBS1   /u01/app/oracle/oradata/XE/undotbs1.dbf     75 YES 32767.9844 .000610352

SYSTEM    /u01/app/oracle/oradata/XE/system.dbf    360 YES   600 .001220703

SHOW ALL INX / REBUILD

SQL>select 'alter index ' || owner || '.' || index_name ||' rebuild;'
from dba_indexes
where status ='UNUSABLE'
order by index_name
/

in construction ...

Saturday, November 30, 2013

Command Line, Data Base, RMAN, Unix, Windows

Oracle - RMAN RESTORE DB

7:33 AM Unknown No comments

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;
}

Thursday, November 28, 2013

Command Line, Data Base, Oracle, RedHat, RMAN, Unix, Windows

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

4:54 AM Unknown No comments

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

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

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.

Wednesday, November 27, 2013

Command Line, Data Base, Oracle, RedHat, RMAN, Unix, Windows

Oracle - Changing Datafile Size [ Alter autoextend / maxsize ]

3:01 PM Unknown No comments

#!/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;

Wednesday, February 6, 2013

Command Line, Data Base, Oracle, RedHat, RMAN, Unix

ORACLE - [ spreport ]

12:37 PM Unknown No comments

1:
SQL> exec perfstat.statspack.snap

2:
SQL> exec perfstat.statspack.snap

3:
SQL> @$ORACLE_HOME/rdbms/admin/spreport

Tuesday, February 5, 2013

Command Line, Data Base, linux, Mac, Oracle, RedHat, RMAN, Windows

Rman catalog - Time Retention

12:22 PM Unknown No comments

SQL> show parameter keep

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 0

Command line

Wednesday, December 10, 2014

ORACLE - Swiss penknife QUERY

Saturday, November 30, 2013

Oracle - RMAN RESTORE DB

Thursday, November 28, 2013

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

Wednesday, November 27, 2013

Oracle - Changing Datafile Size [ Alter autoextend / maxsize ]

Wednesday, February 6, 2013

ORACLE - [ spreport ]

Tuesday, February 5, 2013

Rman catalog - Time Retention

Categories

Popular Posts

Blog Archive

Total de visualizações

Seguidores

About Me