Oracle Wallet


What is Oracle Wallet

Oracle wallet is a password-protected container for authentication and signing credentials, including private keys, certificates and other secrets that you need to secure over time.

How to create Oracle wallets

Wallets are created using OWM or orapki utility.  To use orapki you can run orapki wallet command (orapki wallet create -wallet  )
Password for walet must be strong :

[oracle@orcl]$ orapki wallet create -wallet /u01/app/oracle/product/11.2/db_1/wallets
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.
Enter password:            
Enter password again: 

[oracle@orcl]$ ls /u01/app/oracle/product/11.2/db_1/wallets/
ewallet.p12

To display the contents of a wallet:


[oracle@orcl wallets]$ orapki wallet display -wallet /u01/app/oracle/product/11.2/db_1/wallets
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
[oracle@orcl wallets]$ 

All of these operations also can be performed by Oracle Wallet Manager (OWM) utility.
Just call owm from command line, then select wallet-> open from wallet manager, select wallets directory and type your password.

2

To create self-signed root certificate use wallet add command:

[oracle@orcl ~]$ orapki wallet add -wallet $ORACLE_HOME/wallets -dn 
"CN=testsecurity Root,O=testsecurity,C=US" -self_signed -validity 365 -keysize 1024
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

[oracle@orcl ~]$ 

When you use self_signed keyword your certificate will created immediately because you are acting as the Certificate Authority(CA).
Let’s display self signed certificate from orapki and OWM:

[oracle@orcl ~]$ orapki wallet display -wallet $ORACLE_HOME/wallets
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Requested Certificates: 
User Certificates:
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Trusted Certificates: 
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US

In OWM you will see the new certificate as shown bellow:


To create and sign certificate request:

[oracle@orcl ~]$ orapki wallet add -wallet $ORACLE_HOME/wallets -dn "CN=test,O=testsecurity,C=US" -keysize 1024
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:            
 
[oracle@orcl ~]$ 

 

If look at the contents of the wallet now you’ll see the new certificate request:

[oracle@orcl ~]$ orapki wallet display -wallet $ORACLE_HOME/wallets
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Requested Certificates: 
Subject:        CN=test,O=testsecurity,C=US
User Certificates:
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Trusted Certificates: 
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US

 

Export the certificate request so that you can sign it anywhere:

[oracle@orcl ~]$ orapki wallet export -wallet /u01/app/oracle/product/11.2/db_1/wallets/ -dn "CN=test,O=testsecurity,C=US" -request ./test.req
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

[oracle@orcl ~]$ ls -l ./test.req 
-rw-------. 1 oracle oinstall 584 Jun 28 18:53 ./test.req

 

Create signed certificate from request:

[oracle@orcl ~]$ orapki cert create -wallet /u01/app/oracle/product/11.2/db_1/wallets/ -request ./test.req -cert ./test.cert -validity 365
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:            

[oracle@orcl ~]$ ls -l ./test.cert 
-rw-------. 1 oracle oinstall 706 Jun 28 19:28 ./test.cert

 

Certificate file is created but it is not in any wallet. If you display wallet you will not see the certificate there:

[oracle@orcl ~]$ orapki wallet display -wallet /u01/app/oracle/product/11.2/db_1/wallets/
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:            

Requested Certificates: 
Subject:        CN=test,O=testsecurity,C=US
User Certificates:
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Trusted Certificates: 
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US

 

You need to add the certificate into a wallet .Both certificate request and the certificates are text files which you can copy over to any other server:


[oracle@orcl ~]$ cat test.cert 
-----BEGIN CERTIFICATE-----
MIIB3zCCAUgCAQAwDQYJKoZIhvcNAQEEBQAwPjELMAkGA1UEBhMCVVMxFDASBgNVBAoTC2RiYXNl
Y3VyaXR5MRkwFwYDVQQDExBkYmFzZWN1cml0eSBSb290MB4XDTE1MDYyODE0MjgwNloXDTE2MDYy
NzE0MjgwNlowMjELMAkGA1UEBhMCVVMxFDASBgNVBAoTC2RiYXNlY3VyaXR5MQ0wCwYDVQQDEwR0
ZXN0MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCvmRabzSLBNcL6GjYK4baLezp3yAq9bGGI
jqAGG4T/XNX0kpIEG3jV9xcrMCiInC56YjZ8FuC1ITmdqqlfn9BxE/ImGJcHLCVJtxXL8GDDqiZK
hgCoT16vOYbIaq4VpQJPwqttaK2JEnlRMUcTkemhMa5NzfmJHmB9p9NI/FG/hwIDAQABMA0GCSqG
SIb3DQEBBAUAA4GBADNUZX4MdP8tPYAxR/dpzQiDAJwrG6ohtSRmLbvRsBHyB86d0pM03yt4bAW5
5QLebXH5VVkzOVaizBDrN/gfL8WJlf/qoMCntGxudOT1Wdn3osGRPOhsT/+q+yBnPribcOCz7N9T
E2aZhJYACULMZfh2n/Jk477/O90CXUfn8mRZ
-----END CERTIFICATE-----
[oracle@orcl ~]$ cat test.req 
-----BEGIN NEW CERTIFICATE REQUEST-----
MIIBcTCB2wIBADAyMQswCQYDVQQGEwJVUzEUMBIGA1UEChMLZGJhc2VjdXJpdHkxDTALBgNVBAMT
BHRlc3QwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAK+ZFpvNIsE1wvoaNgrhtot7OnfICr1s
YYiOoAYbhP9c1fSSkgQbeNX3FyswKIicLnpiNnwW4LUhOZ2qqV+f0HET8iYYlwcsJUm3FcvwYMOq
JkqGAKhPXq85hshqrhWlAk/Cq21orYkSeVExRxOR6aExrk3N+YkeYH2n00j8Ub+HAgMBAAGgADAN
BgkqhkiG9w0BAQQFAAOBgQB+v4nCX/9HhCoqdimfznyVxPeDg6uspLo1uteqxPBkmqSyASWxafob
h+tZaFXY7cDw0VFlycjuot5wCWLqXejMpnAmGiJwi6VgziJt7TUpfJw6k3Ga1uarRCV22OWpNt43
tWvApcY0Z4MJXjFFzCpFFI8teeA146i422Zln0HgWQ==
-----END NEW CERTIFICATE REQUEST-----
[oracle@orcl ~]$ 

 

You can add the certificate  either as a user certificate or as a trusted certificate. For example , to add the certificate as user certificate:

[oracle@orcl ~]$ orapki wallet add -wallet /u01/app/oracle/product/11.2/db_1/wallets/ -user_cert -cert ./test.cert 
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

[oracle@orcl ~]$ 

 

Now certificates will show up in the user session:

[oracle@orcl ~]$ orapki wallet display -wallet /u01/app/oracle/product/11.2/db_1/wallets/
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Requested Certificates: 
User Certificates:
Subject:        CN=test,O=testsecurity,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Trusted Certificates: 
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
[oracle@orcl ~]$ 

 

If you want to this certificate to be part of trust  hierarchy you can add it as a trusted certificate  into the wallet:

[oracle@orcl ~]$ orapki wallet add -wallet /u01/app/oracle/product/11.2/db_1/wallets/ -trusted_cert -cert ./test.cert 
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 

 

The certificate will now show up in the trusted certificate section:

[oracle@orcl ~]$ orapki wallet display -wallet /u01/app/oracle/product/11.2/db_1/wallets/
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:            

Requested Certificates: 
User Certificates:
Subject:        CN=test,O=testsecurity,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Trusted Certificates: 
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        CN=testsecurity Root,O=testsecurity,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
Subject:        CN=test,O=testsecurity,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US
[oracle@orcl ~]$ 

Limit connections to listener by source


1If you want to limit access to database from specific host names or ip addresses you must use feature called valid node checking. This feature allows you to define two lists :

  • invited nodes defines a set of nodes from which connections will be accepted
  • excluded_nodes – defines a list from which connections will be rejected

If you use invited_nodes then any node not in this list will be rejected by listener or if you use excluded_nodes then any node not in this list will be accepted by the listener. If you both of these lists, then invited_nodes take precedence over the excluded_nodes. To test invited_nodes list you must add following lines to the sqlnet.ora file:

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/sqlnet.ora 
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
ADR_BASE = /u01/app/oracle
tcp.validnode_checking=YES
tcp.invited_nodes=(ValehPC,192.168.100.13)

[oracle@orcl u01]$ lsnrctl stop
[oracle@orcl u01]$ lsnrctl start

After changes applied, when you try to connect from any node apart from these two you will get an error like bellow:

when trying to connect with SQL Plus

C:UsersValeh>sqlplus test/test@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db11g)))

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 18 05:01:57 2015

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

ERROR:
ORA-12537: TNS:connection closed

when trying connect with Oracle SQL Developer

1

Secure access to listener


Listener-ə müraciətin təhlükəsiz təşkili listenerin vacib təhlükəsizlik tədirləridən biridir, çunki serverinizə şəbəkə vasitəsilə hücum təşkil edib listeneri ələ keçirən şəxslər listeneri idarə edərək sizin sizin DB-nı sondürə bilər. Lsnrctl vasitəsilə listeneri idarə etməyin qarşısını almaq üçün şifrə təyin olunmalıdır. Əgər lsnrctl status komandasının nəticəsində Security sətrində OFF yazılıbsa deməli listener üçün şifrə təyin olunmamışdır.Oracle 10g and 11g-də listener  üçün susmaya görə Local OS authentication adlanan təhlükəsizlik növü təyin olunur. Local OS authentication o deməkdir ki, siz listeneri  idarə edə bilmək üçün listenerin işlədiyi host-a login olmuş olmalısınız. Biz bunu lsnrctl status komandasını işlədərək  aşağıdakı nəticədə görə bilərik

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUN-2015 01:44:59

::::::::::::::output trimmed::::::::::::::

------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUN-2015 01:17:51
Uptime                    0 days 0 hr. 27 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
::::::::::::::output trimmed::::::::::::::

[/pcsh]

Local OS Authentication-un ləğv edilməsi

Local OS Authentication ləğv etmək üçün listener.ora faylını redaktə edərək local_os_authentication_LISTENER parameterinə OFF  qiymətini mənimsətmək lazımdır:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
#---- OUTPUT TRIMMED ----#
local_os_authentication_LISTENER=off
 

[/pcsh]

Listener-ə açıq tekst formatında şifrənin təyini

Listener-ə açığ text tipli şifrə təyin etmək üçün $ORACLE_HOME/network/admin/listener.ora faylını redaktə edib PASSWORDS_{LISTENER_ADI}=sizinşifrə sətrini əlavə etmək lazımdır.
Biz həmşinin listener-ə bir neçə şifrədə təyin edə bilərik:   PASSWORDS_LISTENER=(password1,password2)

Nümunə:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

#clear type password
PASSWORDS_LISTENER=(test123,test654)

[/pcsh]

Listenerə heşlənmiş şifrənin təyin olunması

Listenerə heşlənmiş şifrə təyin etmək və ya redaktə etmək və yadda saxlamaq üçün set password, change_password və save_config komandalarından aşağıdakı qaydada istifadə etmək lazımdır :

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

#using set password
LSNRCTL> set password
Password: 
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521)))
No changes to save for LISTENER.
The command completed successfully
LSNRCTL> 

#using change password
LSNRCTL> change_password 
Old password:  
New password:  
Reenter new password:  
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Password changed for LISTENER 
The command completed successfully 
LSNRCTL> save_config 
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Saved LISTENER configuration parameters. 
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak 
The command completed successfully 

[/pcsh]

Şifrə təyin edildikdən sonra  security sətri ​Password or Local OS authentication yazısına dəyişir.

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> status 
::::::::::::::output trimmed:::::::::::::: 
Security                  ON: Password or Local OS Authentication 
SNMP                      OFF 
::::::::::::::output trimmed:::::::::::::: 

[/pcsh]

Şifrə listener.ora faylında heşlənmiş formatda saxlanılır, listener.ora faylının işinə baxmaqla bunu görmək olar:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

 
[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521)) 
    ) 
  ) 
ADR_BASE_LISTENER = /u01/app/oracle 
 
#----ADDED BY TNSLSNR 09-JUN-2015 01:56:41--- 
PASSWORDS_LISTENER = 9BD20802761D432E 
#-------------------------------------------- 

[/pcsh]

Listener üçün təyin olunmuş şifrəni ləğv etmək üçün aşağıdakı addımları etmək lazımdır:

İlk olaraq listeneri aşağıdakı komanda vasitəsilə dayandırıq

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
LSNRCTL>   

[/pcsh]

Daha sonra listener.ora faylını redaktə edərək şifrə olan sətri silirik və listeneri restart edirik.
Securing access to the listener is a one of the important aspects of listener security, because if the attacker can access to control the lsnrctl he can shut down your database. To limit access to the ability to control the listener through the lsnrctl you must set a password. If a security row is OFF in the result of the lsnrctl status command it means that there is no password set for the listener. In Oracle 10g and 11g the listener is securing by default using an option called Local OS authentication. Local OS authentication means that, you can control the listener if you are logged on to the account on the host where the listener is running. You can see it with lsnrctl status command:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUN-2015 01:44:59

::::::::::::::output trimmed::::::::::::::

------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUN-2015 01:17:51
Uptime                    0 days 0 hr. 27 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
::::::::::::::output trimmed::::::::::::::

[/pcsh]

Disable Local OS Authentication

To disable Local OS Authentication you must set local_os_authentication_LISTENER parameter to OFF in listener.ora file:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
#---- OUTPUT TRIMMED ----#
local_os_authentication_LISTENER=off
 

[/pcsh]

Setting clear text format  password for listener

To set a listener password in clear text format open the  $ORACLE_HOME/network/admin/listener.ora file and add a line to this file with the following format: PASSWORDS_{LISTENER_NAME}=yourpassword
If you want, you  can set multiple passwords for listener:   PASSWORDS_LISTENER=(password1,password2)

for example:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

#clear type password
PASSWORDS_LISTENER=(test123,test654)

[/pcsh]

Setting an encrypted  password for listener

To set, change and save password you can use set password or change_password and save_config commands as shown above :

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

#using set password
LSNRCTL> set password
Password: 
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521)))
No changes to save for LISTENER.
The command completed successfully
LSNRCTL> 

#using change password
LSNRCTL> change_password 
Old password:  
New password:  
Reenter new password:  
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Password changed for LISTENER 
The command completed successfully 
LSNRCTL> save_config 
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Saved LISTENER configuration parameters. 
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak 
The command completed successfully 

[/pcsh]

After setting password  security option of the listener changes to ​Password or Local OS authentication

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> status 
::::::::::::::output trimmed:::::::::::::: 
Security                  ON: Password or Local OS Authentication 
SNMP                      OFF 
::::::::::::::output trimmed:::::::::::::: 

[/pcsh]

The password is saved in listener.ora file as a hashed entry, you can check what was generated by looking in the listener.ora file:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

 
[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521)) 
    ) 
  ) 
ADR_BASE_LISTENER = /u01/app/oracle 
 
#----ADDED BY TNSLSNR 09-JUN-2015 01:56:41--- 
PASSWORDS_LISTENER = 9BD20802761D432E 
#-------------------------------------------- 

[/pcsh]

To remove the listener password you should  do the following:

First stop the listener

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
LSNRCTL>   

[/pcsh]

Then edit the listener.ora file and remove the lines added above and restart the listener.

Scenario 8 – Loss of all control files


Vəziyyət:

  • Bütün kontrol faylar silinmişdir
  • Kontrol faylın backupını aldıqdan sonra yeni yaradılmış data file silinmişdir
  • Kontrol faylın binary backup-ı vardır

.

Bu vəziyyəti test etmək üçün ilk öncə kontrol faylın binary backup-nı alırıq və daha sonra bütün kontrol faylları silirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';

Database altered.
Create new tablespace

SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;

Tablespace created.

SQL> !rm /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info



[/pcsh]

DB-nı mount etmək üçün kontrol faylın backupını silinmiş faylların yerınə kopyalayırıq və adını dəyişirik. Sonra isə kontrol faylın backupından istifadə edərək recovery etməyə cəhd edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl

[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/

control01.ctl  control02.ctl  control03.ctl
SQL> alter database mount;

Database altered.



SQL> recover database using backup controlfile;

::::::::::::::output trimmed::::::::::::::

ORA-00280: change 13917957 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/DB11G/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'

ORA-01112: media recovery not started

[/pcsh]

ORA-01244 errorundan biz aydın olurkı bizim adı təyin olunmamış datafaylımız var. İndi biz kontrol faylın backupından sonra yaradılmış yeni  datafaylın adını tapmalı və onun əsasında datafaylı yaratmalıyıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005

10 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';

Database altered.

[/pcsh]

Yeniden recover etməyə cəhd edirik və open edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
------------------------------ -------------------------------------------------
SYSTEM        /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX        /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2        /u01/app/oracle/oradata/DB11G/tbs_test2.dbf

10 rows selected.



[/pcsh]Situation:

  • All control files have been lost
  • After creating backup of control file new created datafile lost
  • Control file binary backup is available

.

Take binary backup of the control file and remove all control files to simulate lost of all control files:

[pcsh lang=”sql” tab_size=”” message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';

Database altered.
Create new tablespace

SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;

Tablespace created.

SQL> !rm /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info



--

[/pcsh]

To mount Database copy backup of control file to the original control file locations and rename to original control file names.Then recover database using backup control file

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl

[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl

[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/

control01.ctl  control02.ctl  control03.ctl
SQL> alter database mount;

Database altered.



SQL> recover database using backup controlfile;

::::::::::::::output trimmed::::::::::::::

ORA-00280: change 13917957 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/DB11G/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'

ORA-01112: media recovery not started

[/pcsh]

ORA-01244 says that we have unnamed datafile. Now we must  find this datafile and creat datafile based on it with name of datafile that were created after taking backup of control file:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005

10 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';

Database altered.

[/pcsh]

Now recover the database again and then open  the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
------------------------------ -------------------------------------------------
SYSTEM        /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX        /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2        /u01/app/oracle/oradata/DB11G/tbs_test2.dbf

10 rows selected.



[/pcsh]

Scenario 7 – Recovery read only tablespace from loss of control files


Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylımızın yaranma scripti var
  • Tablespce-lərdən biri read only-dir

.

İlk olaraq bir tablespace yaradırıq , only read only edirik, kontrol faylın backup-nı alırıq  və bütün kontrol faylları silirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

ctl_backup.dat faylından istifadə edərək kontrol faylı yaradırıq və DB-nı işə salmağa cəh edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

Və biz görürük ki kontrol faylın yaranma skriptində read only tablespace barədə məlumat yoxdur.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Data faylların adını sorğuladıqda görürük ki tbs_test readonly tablespacesinin datafaylları barədə məlumat yoxdur:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

DB-nı recover etməyə cəhd edirik və redo məlumatları əlavə etmək üçün redo log-ları təqdim edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery başlamır, çünki oracle itmiş read only datafaylı UNNAMED00004 kimi qəbul etdiyindən redolog-lar əlavə oluna bilmir. Ona görədə biz UNNAMED00004 faylının adını onun orijinal adına dəyişirik

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

İndi recovery davam edə bilər , çünki datafayl öz düzgün adına rename olundu. DB-ni recover edib bazanı açıqırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]Situation:

  • All control files are lost
  • You have a creation script of the control file
  • One of the tablespaces is read only

.

First, create tablespace and make it read only, then take backup of the control file and delete all available control files.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

Create control file using ctl_backup.dat file and try opening the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

And we saw that there is not information about readonly datafile on this script.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Select the name  of datafiles and you say it is no information about tbs_test readonly datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

Try to recover the database and provide the redo log file to applying redo entries:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery hasn’t started because it applied information about missing read only datafile which is missing since Oracle accept it as UNNAMED00004.

Select name of datafiles, now you can see UNNAMED00004 file, which is the our missing read only datafile and rename the datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

Now recovery will continue because the datafile has been renamed to its  correct name. Recover database and open it:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]

Scenario 6 – Recover from lost of a member of Multiplexed Control File


Vəziyyət:

  • Kontrol fayllar multipleks olunmuşdur
  • Kontrol fayllardan biri silinmişdir

Bu halda kontrol fayllar multipleks olunduğu üçün yenidən kontrol fayl yaratmağa ehtiyac qalmır. Silinmiş kontrol faylları bərpa etmək üçün sadəcə  bazanı söndürüb, qaydasında olan kontrol faylı silinmiş faylın yerinə kopylayıb adını silinmiş faylın adına dəyişirik. Gəlin bunu test edək:

İlk olaraq bütün kontrol fayllarımızın adını sorğulayırıq, daha sonra onlardan birini silib DB-nı reboot edirik.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Daha sonra qaydasında olan kontrol faylı silinmiş faylın yerinə kopyalayıb adını dəyişirik və bazanı işə salırıq.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]Situation:

  • Control files multiplexed
  • One of the controlfiles is lost

If you have a multiplexed control file, there is no need to create a new control file. Instead, only shut down the database and copy the available control file to the directory of the missed one. Let’s test it:

Select name of all control files, delete one of them and reboot the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Copy available control file to the directory of the missed control file and open the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]

Scenario 5 – Recovering from loss of all control files using binary backup control file


Recovering from loss of all control files using backup control

Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylın binary backupı var

.

Bu vəziyyəti yoxlamaq üçün ilk olaraq kontrol faylın binary backup-nı alırıq və bazanı söndürürük. DB-nı mount vəziyyətinə gətirmək istədikdə error ilə qarşılaşırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Bu vəziyyətdən çıxış üçün binary backup-ı silinmiş kontrol faylların yerinə adını silinmiş faylların adına dəyişərək kopyalayır və bazanı mount edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

Recover database using backup control file komandasından istifadə edərək bazanı binary backup-dan recover etməyə cəhd edək:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Burada oracle bizə deyirki kontrol faylin backup-ı alındıqdan sonra edilmish deyishiklikler teqdim edilmelidir. Yuxarıda error bizə dəyishiklik olunmush redolog faylin adını ve scn nomresini teqdim edir. Spesifik scn-den 13877247  sonra deyishikler olunmush faylı dəqiq tapmaq üçün biz  log və archivelog  faylların yerləşdiyi view-ni sorğulayırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

Yuxarıdakı nəticədən görünür ki, 2 və 3 inactive olduğu üçün 1-ci qrupda olan dəyişikliklər əlavə olunmalıdır. Ona görədə biz yeniden recovery emrini işə salırıq və ona parametr olaraq redo log faylını /u01/app/oracle/oradata/DB11G/redo01.log veririk.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]Situation:

  • All control files are lost
  • You have binary backup of control file

.

At first lets take a binary backup of control files and shutdown force database. When you attempt to mount the database, you encounter with error:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Then copy binary backup of control file to te original control file’s destination and rename to the original control file name. Then mount the database:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

You can use the recover database using backup control file command to tell Oracle that an attempt to recover the database from the binary copy of the control file:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Changes after backup are not presented . To find a file that contains changes after specific scn 13877247 we query the v$log view

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

From result above we see that because second and third group is inactive ve must apply first group redo file . That’s why retrie recovery command with  /u01/app/oracle/oradata/DB11G/redo01.log parameter.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]