what is new in Oracle 11g

Oracle Audit Vault

We know that DBAs who are skilled enough can view and change data if they really want to. They can then alter the audit trail—wipe away their footprints—to make sure no one can find out what they've done. It's always been a problem, and now it's also a major compliance issue.

Oracle Audit Vault changes that scenario considerably. The audit trail goes into a one-way "lockbox." No one, not even a highly skilled DBA, can alter that audit trail once it's in the lockbox. People can view it, but no one can change it or delete it.

Hot Patching

Another feature that DBAs will really appreciate is hot patching, which allows you to apply updates and bug fixes to the database without bringing the system down.

Data Compression

Another new feature in Oracle Database 11g is the ability to store data in compressed format—called advanced table compression. Suppose you're storing a lot of long documents. Keeping them in compressed format can save up to two-thirds of the usual space necessary.

Query Result Cache

Oracle Database 11g will cache the results of queries in memory, where they act like tables. Multiple users can see these results without repeating the same query. For common and frequently used queries, this can save system effort and improve performance. You can configure how much space to allocate to query result cache and how long to retain results.

Oracle Enterprise Linux

Oracle offers Linux distribution, that is based on Red Hat Linux.
Of course, for Oracle database server hosting.

download here: http://edelivery.oracle.com/linux

Start and shutdown

For a DBA, starting up and shutting down of oracle database is a routine and basic operation. Sometimes Linux administrator or programmer may end-up doing some basic DBA operations on development database. So, it is important for non-DBAs to understand some basic database administration activities.

In this article, let us review how to start and stop an oracle database.

How To Startup Oracle Database
1. Login to the system with oracle username
Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

$ su - oracle2. Connect to oracle sysdba
Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.

$ env grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0
You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

$ sqlplus '/ as sysdba' (linux)

c:\Documents and settings\will\Desktop> sqlplus "/ as sysdba" (windows)

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:28 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL>3. Start Oracle Database
The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

spfile$ORACLE_SID.ora
spfile.ora
init$ORACLE_SID.ora

Type “startup” at the SQL command prompt to startup the database as shown below.

SQL> startup
ORACLE instance started.

Total System Global Area 812529152 bytes
Fixed Size 2264280 bytes
Variable Size 960781800 bytes
Database Buffers 54654432 bytes
Redo Buffers 3498640 bytes
Database mounted.
Database opened.
SQL>
If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

How To Shutdown Oracle Database
Following three methods are available to shutdown the oracle database:

Normal Shutdown
Shutdown Immediate
Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

Oracle tips

********
TIP1 Shutdown/offline messages
********

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

or

ERROR at line 1:
ORA-12571: TNS:packet writer failure


Essas mensagens aparecem quando o banco de dados esta shutdown. Poderia ser outra mensagem.


********
TIP2 Oracle init.ora file
********

The init.ora file stores the initialization parameters of Oracle. The values that are currently in effect can be viewed through v$parameter. The init.ora file is read when an instance (for example using startup in SQL*Plus). Default location and name

The default location of init.ora is

$ORACLE_HOME/dbs> on unix and
%ORACLE_HOME%\database on Windows.

On Windows, the location can be changed by changing ORA_%ORACLE_SID%_PFILE. The default name for the file is init$ORACLE_SID.ora (unix) or init%ORACLE_SID%.ora (windows)

However, it is possible to start the database with another init.ora file than the default one. In this case, there is no way to determine which init.ora was used when the database is running (at least up to Oracle 9i).


********
TIP 3 Oracle Password file
********

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and
%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

********
TIP 4 Mostrando codigo de views
********


SQL> desc all_views
Name Null? Type
----------------------------------------- -------- ---------------

OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)

SQL> set long 1000000

select text from all_views where view_name='YOURVIEW'


ps. oracle: Ao criar views baseado em colunas de tabelas, tudo fica bem ate qdo uma coluna é removida da tabela e a view fica desatualizada

Relembrando o basico

sqlplus system/senha@orcl9i

create user will identified by senha
default tablespace users
temporary tablespace temp;

grant dba to will;

//to see this role granted, logged with the user:

//select * from user_role_privs;

create table Pessoa(
pessoa_id number primary key,
empresa_id number references Empresa(empresa_id),
nome varchar2(200),
sobrenome varchar2(200),
tratamento varchar2(10),
telefone varchar2(12),
email varchar2(200),
celular varchar2(12)
)


create table Empresa(
empresa_id number primary key,
empresa_name varchar2(200),
website varchar2(100),
address varchar2(200),
address2 varchar2(200),
telefone varchar2(12),
telefone2 varchar2(12),
cidade varchar2(10),
provincia varchar2(10),
comentario varchar2(200)
)


alter table
candidatar_vaga
add constraint
fk_anuncio FOREIGN KEY (anuncio_id)
references
anuncio (anuncio_id)
initially deferred deferrable;


create sequence seq_ex start with 1 increment by 1;




-------------------------------------------- PROMPTING
fichier:SITEEMPREGOS.sql
insert into siteempregos values(seq_siteempregos.nextval,&NOME,&SITE);
---

@'C:\Documents and Settings\Invité\Mes documents\SITEEMPREGOS.sql'

preencher com aspas simples as strings ao responder
-------------------------------------------------------

http://192.168.0.4/isqlplus




*****************************************Fazendo HOT BACKUP
http://www.youtube.com/watch?v=MOoUFIS-uoQ
http://www.cuddletech.com/articles/oracle/node58.html
http://www.dba-oracle.com/concepts/starting_database.htm

1 - archivelog mode on

SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG


Aqui vemos que nao esta ON.

Faremos ficar on entao:

adicionar no fim do init.ora:

############################
# Archive Log Destinations will(16/12/09)
############################
log_archive_dest_1='location=/u02/oradata/cuddle/archive'
log_archive_start=TRUE


parar e iniciar o servico:

entre no prompt e digite:

C:\Documents and Settings\Will>set oracle_sid=orcl9i
C:\Documents and Settings\Will>sqlplus "sys as sysdba"

SQL*Plus: Release 9.1.0.1.0 - Production on Tue Dec 16 23:00:58 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

Verificando - archivelog mode on

SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG


Digitando:

rman target /
RMAN> backup database plus archivelog;

PARECE QUE DPS DESSE PROCEDIMENTO DEVE SE INICIAR O BANCODEDADOS MANUALMENTE:

sqlplus " / as sysdba"
SQL> startup

Oracle 9i

Quebrando a cabeça com a rede P2P.

Apos penar para configurar a rede, constatei o seguinte:

-ao dar ping, vc deve conseguir dar ping em vc mesmo via
ping 127.0.0.1, ping localhost E ping 192.168.0.1 (seu ip)
-verificar se vc consegue pingar das duas pontas, ida e volta.
-verificar se nao existe algum software barrando:
no meu caso Norton internet security. Um firewall por exemplo.
-verificar se o endereco fisico da placa de rede nao é identica, pois nao funciona,
mesmo com ips diferentes e subnet mask iguais.
-constatei que a LUZ minha placa de rede dell inspiron 1300, fica amarela e nunca verde. Mesmo funcionando.
-verificar se os drivers da placa de rede estao ok.
Depois de ver se a rede esta funcionando, configurar o oracle db e o oracle client.

Network configuration (ORACLE DB SERVER):

# TNSNAMES.ORA Network Configuration File: c:\oracle\ora90\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.


ORCL9I=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL9I)
)
)


# LISTENER.ORA Network Configuration File: c:\oracle\ora90\network\admin\listener.ora
# Generated by Oracle configuration tools.

ORCL9I =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\Oracle\Ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL9i)
(ORACLE_HOME = c:\oracle\ora90)
(SID_NAME = ORCL9i)
)
)


# SQLNET.ORA Network Configuration File: C:\oracle\ora90\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = localdomain

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

quick check for duplication in the fact table

select
count(1), num_annee,num_mois,
AG14_HRID.COD_MATRICULE
-- AG14_HRID.LIB_NOM,
-- AG14_HRID.LIB_PRENOM
from
AG14_HRID
--where
-- AG14_HRID.cod_matricule='FRA0592979'
-- and num_annee='2009' and num_mois='01'
group by
num_annee,num_mois, AG14_HRID.COD_MATRICULE
having count(1)>1

Addresses

C:\Oracle\Ora81\network\admin
tnsnames

C:\WINDOWS\system32\drivers\etc
hosts

https://localhost:2002/main.html
logmein

Dicas

oracle: Ao criar views baseado em colunas de tabelas, tudo fica bem ate qdo uma coluna é removida da tabela e a view fica desatualizada, no BO apos clicar ok qdo for rodar a query, a tela volta na tela de objetos.

Ao atualizar a view e depois a estrutura no designer para que a view seja atualizada, deve se apagar o objeto no painel esquerdo pq ele nao eh atualizado/removido.

BO designer: ao copiar relacionamentos, copiar a clausula where ao inves de ligar os relacionamentos.