Oracle

Comandos pós instalação

Abaixo seguem os comandos de parametrização que devem ser executados no banco de dados após a instalação inicial:

1. CONECTAR VIA PROMPT DE COMANDO
   • Iniciar -> Executar -> sqlplus sys as sysdba
     
     
2. ALTERAR O CHARSET
   • shut immediate;
   • startup restrict;
   • alter database character set INTERNAL_USE WE8ISO8859P1;
   • shut;
   • startup;
     
     
3. AUMENTAR O Nº DE CONEXÕES (Se necessário, realizar backup do arquivo de configuração do Oracle deste diretório: C:\app\product\11.2.0\dbhome_1\database)
   • shut immediate;
   • startup restrict;
   • alter system set PROCESSES=3000 scope=SPFILE;
   • alter system set TRANSACTIONS=3000 scope=SPFILE;
   • alter system set SESSIONS=3000 scope=SPFILE;
   • shut;
   • startup;
     
     
4. ALTERAR FORMATO DATA/HORA E IDIOMA
   • alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';
   • alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
   • call dbms_session.set_nls('NLS_LANGUAGE','PORTUGUESE');
   • call dbms_session.set_nls('NLS_TERRITORY','BRAZIL');
   • call dbms_session.set_nls('NLS_NUMERIC_CHARACTERS','''.,''');
     
     
5. CRIAR USUÁRIO EMA
   • CREATE USER EMA IDENTIFIED BY "dbaaccesskey"
         DEFAULT TABLESPACE "USERS"
         QUOTA UNLIMITED ON "USERS";
   • GRANT "CONNECT" TO EMA WITH ADMIN OPTION;
     GRANT "DBA" TO EMA WITH ADMIN OPTION;
     ALTER USER EMA DEFAULT ROLE "CONNECT", "DBA";
     
     
6. CONFIGURAR ORACLE 11G PARA NÃO EXPIRAR A SENHA DO USUÁRIO
   • ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Comandos de manutenção

1 - CONFIGURAR ORACLE 11G PARA NÃO EXPIRAR A SENHA DO USUÁRIO

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

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

2 - OTIMIZAR E CORRIGIR PROBLEMAS COM O BANCO

1ª opção: EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('EMA', 'COMPUTE');

2ª opção: EXEC DBMS_STATS.GATHER_DATABASE_STATS;

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

3 - VERIFICAR INFORMAÇÕES SOBRE MEMÓRIA E OUTRAS PARAMETRIZAÇÕES

Verificar o nº de processos: show parameter processes;
Verificar o nº de sessões: show parameter sessions;
Verificar o nº de transações: show parameter transactions;
Verificar informações sobre a memória: show parameter memory;
Verificar informações sobre a memória sga: show parameter sga;
Verificar informações sobre a memória pga: show parameter pga;

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

4 - VERIFICAR SESSÕES, PROCESSOS E TRANSAÇÕES

select name, value
from v$parameter
where name in ('processes', 'sessions', 'transactions');

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

5 - VERIFICAR SESSÕES EM USO E LIMITE CONFIGURADO

select current_utilization, limit_value
from v$resource_limit
where resource_name='sessions';

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

6 - AUMENTAR MEMÓRIA RAM ALOCADA PARA O ORACLE

*Fazer um backup do arquivo antes de executar os comandos "$ORACLE_HOME/database/spfileXE-ORCL.ora"

alter system set memory_max_target=6500M scope=spfile;
alter system set memory_target=4500M scope=spfile;
shut immediate;
startup;

ou

alter system set sga_target=4G scope=spfile;
alter system set sga_max_size=5G scope=spfile;
shut immediate;
startup;

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

7 - VERIFICAR Nº DE TABELAS

select count(1) from all_tables where owner = 'EMA';

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

8 - VERIFICAR USUÁRIOS CONECTADOS

select sid,serial#,username, osuser, machine from v$session;

PARA DESCONECTAR O USUÁRIO DA BASE

alter system disconnect session 'sid,serial' immediate;

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

9 - ADICIONAR NOVO TABLESPACE

CREATE TABLESPACE NOME_TABLESPACE LOGGING DATAFILE 'C:\APP\ORADATA\ORCL\NOME.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL;

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

10 - CRIAR UM NOVO DBF EM UM TABLESPACE EXISTENTE

ALTER TABLESPACE USERS ADD DATAFILE 'C:\APP\ORADATA\ORCL\USERS02.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

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

11 - CRIAR NOVO DBF EM NO TABLESPACE TEMP

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

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

12 - EXCLUIR DBF DE UM TABLESPACE

ALTER TABLESPACE USERS DROP DATAFILE 'C:\APP\ORADATA\ORCL\WMW01.DBF';

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

13 - COMANDO PARA OBTER TAMANHO OCUPADO POR CADA SCHEMA DO BANCO

select owner BANCO, tablespace_name,
cast(sum(bytes / 1024/*KB*/ / 1024/*MB*/) as numeric(15,2)) TAMANHO_MB,
cast(sum(bytes / 1024/*KB*/ / 1024/*MB*/ / 1024/*GB*/) as numeric(15,2)) TAMANHO_GB
from dba_segments
where tablespace_name = 'USERS'
group by owner, tablespace_name
order by 4 desc;

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

14 - COMANDO PARA OBTER TAMANHO OCUPADO PELO SCHEMA EMA

select sum(bytes/1024/1024/1024)GB from dba_segments where owner='EMA';

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

15 - IDENTIFICAR O TAMANHO DO DATAFILE

select substr(a.tablespace_name,1,20) "Tablespaces",
(b.BYTES/1048576) as "TotalMB",
(b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",
(c.BYTES/1048576) as "FreeMB"
from dba_tablespaces a,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_data_files
group by tablespace_name ) b,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_free_space
group by tablespace_name) c
where
a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
order by a.tablespace_name;

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

16 - LIBERAR BLOQUEIO DE TABELA PARA DROPAR USUÁRIO

alter table bloqueio_tabela enable table lock;

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

17 - OBTER ESTATÍSTICAS DO BANCO DE DADOS

exec DBMS_STATS.GATHER_SCHEMA_STATS('EMA'); --SCHEMA
exec dbms_stats.gather_fixed_objects_stats(); --Objetos fixos do oracle
exec dbms_stats.gather_database_stats(); --Todo o Banco
exec dbms_stats.gather_dictionary_stats(); --Dicionario de dados do oracle
exec dbms_stats.gather_system_stats(); --S.O.

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

18 - DESBLOQUER CONTA DE USUÁRIO NO ORACLE

ALTER USER "NOME DO USUARIO" IDENTIFIED BY "NOVA SENHA" ACCOUNT UNLOCK;

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

19 - ALTERAR SENHA DO USUÁRIO NO ORACLE

ALTER USER EMA IDENTIFIED BY "senha" ACCOUNT UNLOCK;

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

20 - COLOCAR DATAFILE PARA AUTOEXTEND=ON

alter database datafile 'C:\app\Administrador\oradata\orcl\USERS05.DBF' autoextend on;

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

21 - DROPAR DATAFILE QUE NÃO EXISTE MAIS

alter database datafile 'c:\oracle\oradata\datafiles\file01.db' offline drop;

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

22 - SETAR O TABLESPACE USERS COMO PRINCIPAL

--Pegar o FILE_ID do USERS com esse SELECT:

select file_id,
file_name,
TABLESPACE_NAME
from dba_data_files
where tablespace_name = 'USERS';

--Colocar o FILE_ID no lugar do número 4:

alter database datafile 4 resize 1024M;

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

23 - SCRIPT PARA DROPAR TODOS OS USUÁRIOS DO ORACLE

begin
for tab in (
select distinct owner
from all_tables
where tablespace_name = 'USERS'
)
loop
execute immediate 'drop user ' || tab.owner || ' cascade';
end loop;
end;

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

24 - VERIFICAR QUANTIDADE DE PROCESSOS EM USO E LIMITE

select * from v$resource_limit where resource_name = 'processes';

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

25 - DELETER DATAFILE (EXCLUIR O ARQUIVO E DEPOIS DROPAR DO ORACLE)

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size 2255904 bytes
Variable Size 1275069408 bytes
Database Buffers 436207616 bytes
Redo Buffers 6795264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\ORCL\TRASHED01.DBF'

SQL> alter database datafile 6 offline drop;

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

26 - OBTER VERSÃO DO ORACLE

select * from v$version;

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

27 - RESOLVER PROBLEMA "ORA-12514 - TNS:Listener..." e outros erros de Listener

Antes de executar as ações a seguir, tente reiniciar os serviços do Oracle e do Listener nos Serviços do Windows. Dependendo do tipo de problema apresentado, a reinicialização dos serviços pode fazer com que o Listener volte a funcionar corretamente.

Realizar também um teste para confirmar se o serviço Listener está configurado corretamente, executando o seguinte comando no CMD do Windows: TNSPING ORCL

## SOLUÇÃO 1

Executar os seguintes comandos no terminal do Oracle:

- Consultar o valor da string LOCAL_LISTENER (pode estar vazio): SHOW PARAMETER LOCAL LISTENER

- Setar um valor para string LOCAL_LISTENER*: alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.21)(PORT=1521))'scope=both;

*Substituir o endereço IP na sintaxe HOST pelo endereço IP do servidor de banco de dados

## SOLUÇÃO 2

- Consultar o valor da variável v$parameter: select value from v$parameter where name='service_names'

- Após consultar o valor da variável, copiar o resultado do "service_names" no arquivo "tnsnames.ora":

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *<Endereco_IP_Servidor>*)(PORT = *<1521>*))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <service_names>)
)
)

- Executar o seguinte comando no CMD do Windows para testar se a conectividade com o Listener está funcionando: sqlplus system/dbaaccesskey@orcl

Se funcionar, a conexão com o banco será feita normalmente sem erros de Listener 

## SOLUÇÃO 3

Substituir o conteúdo do arquivo "LISTENER.ORA" pelo conteúdo abaixo, informando o nome do servidor Oracle no parâmetro HOST:

# listener.ora Network Configuration File: C:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Nome_do_Servidor)(PORT = 1521))
)
)

ADR_BASE_LISTENER = C:\app

## SOLUÇÃO 4

Alterar no arquivo listener.ora o endereço de IP do host para o nome do host (reiniciar serviços do Oracle)

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

28 - CONCEDER PERMISSÃO DE CONSULTA EM TODAS AS TABELAS DE UM SCHEMA PARA O USUÁRIO

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='EMA') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to MXCRICIUMA';
END LOOP;
END;

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

29 - VERIFICAR PRIVILÉGIOS DE USUÁRIOS NO ORACLE

select * from (
select 'GRANT '||privilege||' TO '||grantee||';' from dba_sys_privs
where grantee in ('EMA','EMA_CONSULTA')
union all
select 'grant '||privilege||' on '||grantor||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantee in ('EMA','EMA_CONSULTA')
union all
select 'GRANT '||GRANTED_ROLE||' TO '||grantee||';' from dba_role_privs
where grantee in ('EMA','EMA_CONSULTA'));

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

30 - PERMISÃO PARA ACESSO A FUNÇÃO

GRANT EXECUTE ON EMA.VALOR_VARIAVEL_PROCESSO TO PUBLIC

CREATE PUBLIC SYNONYM VALOR_VARIAVEL_PROCESSO FOR EMA.VALOR_VARIAVEL_PROCESSO

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

31 - NÃO USAR O "EMA." NA FRENTE DOS SELECTS

ALTER SESSION SET CURRENT_SCHEMA="EMA";

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

32 - REVOGAR PERMISSÕES PARA USUÁRIO

REVOKE DBA FROM nome_usuario;

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

33 - ALTERAR A SENHA DO USUÁRIO

ALTER USER EMA IDENTIFIED BY "dbaaccesskey_99";

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

34 - SENHA PADRÃO USUÁRIOS WMW

db97!#!!

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

35 - CONCEDER PERMISSÃO PARA LOGAR NO ORACLE

GRANT create session to MXCRICIUMA;

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

36 - CONCEDER PERMISSÃO PARA USUÁRIO ACESSAR DETERMINADAS TABELAS NO ORACLE

GRANT select,update,delete,insert ON EMA.exl_modeloicliente TO mxcriciuma;

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

37 - CONCEDER PERMISSÃO PARA USUÁRIO CRIAR VIEWS NO ORACLE

GRANT create view TO mxcriciuma;

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

38 - REVOGAR PERMISSÃO DE CONSULTA EM TODAS AS TABELAS DE UM USUÁRIO

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='EMA') LOOP
EXECUTE IMMEDIATE 'revoke select on '||R.owner||'.'||R.table_name||' from MXCRICIUMA';
END LOOP;
END;

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

39 - ERRO DURANTE A INSTALAÇÃO DO ORACLE

"Causa: Falha ao acessar local temporario oracle"

6.2.23 INS-30131 Error When Installing Oracle Database or Oracle Client
If the administrative shares are not enabled when performing a single instance Oracle Database or Oracle Client installation for 12c Release 1 (12.1) on Microsoft Windows 7, Microsoft Windows 8, and Microsoft Windows 10, then the installation fails with an INS-30131 error.

Workaround:
Execute the net share command to ensure that the administrative shares are enabled. If they are disabled, then enable them by following the instructions in the Microsoft Windows documentation. Alternatively, perform the client or server installation by specifying the following options:

For a client installation:

-ignorePrereq -J"-Doracle.install.client.validate.clientSupportedOSCheck=false"

For a server installation:

-ignorePrereq -J"-Doracle.install.db.validate.supportedOSCheck=false"

This issue is tracked with Oracle bug 21452473.

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

40 - CONSULTAR SQLS QUE MAIS CONSOMEM CPU

SELECT *
FROM ( SELECT ROUND ( ( (cpu_time / 1000000) / 60), 2) AS "Tempo total de CPU",
executions AS "Quant. exec.",
rows_processed AS "Quant. linhas proc.",
disk_reads AS "Leituras no disco",
first_load_time AS "Primeira utilização",
last_load_time AS "Última utilização",
parsing_schema_name AS "Usuário analisado",
sql_text AS "SQL exec."
FROM v$sqlarea
WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY 1 DESC)
WHERE ROWNUM <= 10;

Nesse caso, a seleção da consulta mais externa (cláusula where) faz com que o retorno sejam de apenas os 10 scripts com maior tempo total de CPU (garantido no sort da consulta interna), mas podemos alterar esse valor ou retirar a condição. Na seleção da consulta mais interna, retirei os usuários de controle do DBA e do próprio SGBD Oracle, para que a análise possa ser feita apenas por aplicações e pessoas.

Vou explicar rapidamente as informações da projeção (cláusula select):

"Tempo total de CPU" – nesse “campo” é retornado o tempo total de CPU gasto para executar todas as vezes o script em questão. Fiz uma continha para exibir o tempo em minutos;

"Quant. exec" – nesse “campo” é retornado a quantidade de vezes que o SQL em questão foi executado desde sua primeira vez que foi submetido;

"Quant. linhas proc." – nesse “campo” é exibido a quantidade total de linhas processadas em todas as execuções da SQL em questão;

"Leituras no disco" – nesse “campo” é retornado a quantidade total de leituras realizadas no disco;

"Primeira utilização" – informa a data e hora da primeira utilização da SQL em questão, ou pelo menos a mais recente das “primeiras vezes”;

"Última utilização" – informa a data e hora da última utilização da SQL em questão;

"Usuário analisado " – informa qual o usuário da instância Oracle que realizou a SQL;

"SQL exec." – informa o script SQL submetido.

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

41 - CRIAR USUÁRIO NO ORACLE SOMENTE PARA CONSULTA

CREATE USER seu_usuario IDENTIFIED BY sua_senha;
GRANT CONNECT TO seu_usuario;
GRANT SELECT ANY TABLE TO seu_usuario;

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

42 - CRIAR NOVO .DBF NO TABLESPACE SYSTEM

ALTER TABLESPACE SYSTEM
ADD DATAFILE 'C:\app\oradata\orcl\SYSTEM02.DBF' SIZE 1G;

*Conferir caminho do diretório ORADATA e o nome do arquivo .DBF

Backup e restore

EXP/IMP

exp ema/dbaaccesskey@orcl full=n consistent=y constraints=y statistics=none object_consistent=y file=C:\ema_backup\bkp-ema.dmp log=C:\ema_backup\export.log

exp ema/dbaaccesskey@zenthi full=y consistent=y constraints=y statistics=none object_consistent=y file=c:\backup_ema\backup_full.dmp log=c:\backup_ema\export_full.log

exp ema/dbaaccesskey@192.168.8.71/orcl full=n consistent=y constraints=y statistics=none object_consistent=y file=c:\ema.dmp log=c:\backup.log

exp user/password@ip:port/service_name

imp ema/dbaaccesskey@orcl file=D:\jean.dmp fromuser=ema touser=ema ignore=y STATISTICS=NONE log=D:\import.log

EXPDP/IMPDP

Configuração inicial:

CREATE OR REPLACE DIRECTORY datapump AS 'C:\Ema Software\Ema_Cloud_Bkp\';
GRANT READ, WRITE ON DIRECTORY datapump TO ema;
execute dbms_metadata_util.load_stylesheets

impdp system/dbaaccesskey directory=datapump dumpfile=bkp-ema.dmp schemas=ema remap_schema=ema:hml_ema

----OPÇÃO PARA QUANDO HOUVER ERROS ORA-31693, ORA-02354, ORA-39776, ORA-00600 DURANTE A IMPORTAÇÃO
impdp system/dbaaccesskey directory=datapump dumpfile=ema.dmp ACCESS_METHOD=CONVENTIONAL remap_schema=ema:ema remap_tablespace=users:users
-----

---- IMPORTAR SÓ TABELAS ESPECÍFICAS
impdp system/dbaaccesskey directory=datapump dumpfile=ema.dmp TABLES=EMA.CLIENTE ACCESS_METHOD=CONVENTIONAL remap_schema=ema:ema remap_tablespace=users:users
-----

---- IMPORTAR BASE SEM DROPAR (SÓ ATUALIZANDO AS TABELAS)
impdp system/dbaaccesskey directory=datapump dumpfile=bkp-ema.dmp remap_schema=ema:ecoville logfile=export.log full=y TABLE_EXISTS_ACTION=REPLACE
----

expdp system/dbaaccesskey directory=datapump dumpfile=ema.dmp schemas=EMA logfile=export.log

expdp system/dbaaccesskey directory=datapump dumpfile=full.dmp schemas=EMA_DESENV,TESTCOMPLETE,UNIEMA logfile=export.log

expdp system/dbaaccesskey directory=datapump dumpfile=table.dmp tables=ema.cliente logfile=export_table.log

**Backup Full
expdp system/dbaaccesskey directory=datapump dumpfile=bkp_full.dmp full=y logfile=backup_full.log

impdp system/dbaaccesskey directory=datapump dumpfile=ema.dmp schemas=ema remap_schema=ema:ema remap_tablespace=users:users

**Argumento para exclusão de tabelas no backup
EXCLUDE=TABLE:\"IN \(\'ITEM_FORMULA_FOTO\',\'ANEXO\',\'LOG\'\)\" nologfile=y

Outros comandos

Verificar o nº de processos: show parameter processes;
Verificar o nº de sessões: show parameter sessions;
Verificar o nº de transações: show parameter transactions;
Verificar informações sobre a memória: show parameter memory;
Verificar informações sobre a memória sga: show parameter sga;
Verificar informações sobre a memória pga: show parameter pga;

VERIFICAR SESSÕES, PROCESSOS E TRANSAÇÕES
select name, value
from v$parameter
where name in ('processes', 'sessions', 'transactions');

VERIFICAR SESSÕES EM USO E LIMITE CONFIGURADO
select current_utilization, limit_value
from v$resource_limit
where resource_name='sessions';