Wednesday, May 26, 2021

What is SSL/TSL and How to generate Self-Signed SSL Certificate?

What is an SSL certificate?

SSL - Secure Sockets Layer

TSL - Transport Layer Security

 

SSL certificates are what enable websites to move from HTTP to HTTPS

-        Which is more secure.

-        An SSL certificate is a data file hosted in a website's origin server.

-        SSL certificates make SSL/TLS encryption possible.

-        they contain the website's public key and the website's identity, along with related information.

 

Devices attempting to communicate with the origin server will reference this file to obtain the public key and verify the server's identity. The private key is kept secret and secure.

 

Here is the full history of SSL and TLS releases:

SSL 1.0 – never publicly released due to security issues.

SSL 2.0 – released in 1995. Deprecated in 2011. Has known security issues.

SSL 3.0 – released in 1996. Deprecated in 2015. Has known security issues.

TLS 1.0 – released in 1999 as an upgrade to SSL 3.0. Planned deprecation in 2020.

TLS 1.1 – released in 2006. Planned deprecation in 2020.

TLS 1.2 – released in 2008.

TLS 1.3 – released in 2018.

 

What is SSL?

SSL is commonly called as TLS, is a protocol for encrypting Internet traffic and verifying server identity. Any website with an HTTPS web address uses SSL/TLS.

See What is SSL? and What is TLS? to learn more.

 

What information does an SSL certificate contain?

SSL certificates include:

 

A. The domain name that the certificate was issued for

B. Which person, organization, or device it was issued to

C. Which certificate authority issued it

D. The certificate authority's digital signature

E. Associated subdomains

F. Issue date of the certificate

G. Expiration date of the certificate

H. The public key (the private key is kept secret)

 

The public and private keys used for SSL are essentially long strings of characters used for encrypting and decrypting data. Data encrypted with the public key can only be decrypted with the private key, and vice versa.

 

Why do websites need an SSL certificate?

A website needs an SSL certificate in order to keep user data secure, verify ownership of the website, prevent attackers to hack site.

 

Encryption: SSL/TLS encryption is possible because of the public-private key pairing that SSL certificates facilitate. Clients (such as web browsers) get the public key necessary to open a TLS connection from a server's SSL certificate.

 

Authentication: SSL certificates verify that a client is talking to the correct server that actually owns the domain. This helps prevent domain spoofing and other kinds of attacks.

 

HTTPS: Most crucially for businesses, an SSL certificate is necessary for an HTTPS web address. HTTPS is the secure form of HTTP, and HTTPS websites are websites that have their traffic encrypted by SSL/TLS.

 

User data are secure in transit most browsers have started tagging HTTP sites as "not secure"

 

How does a website obtain an SSL certificate?

For an SSL certificate to be valid, domains need to obtain it from a certificate authority (CA).

A CA is an outside organization, a trusted third party, that generates and gives out SSL certificates.

 

The CA will also digitally sign the certificate with their own private key, allowing client devices to verify it.

Most, but not all, CAs will charge a fee for issuing an SSL certificate.

 

Once the certificate is issued, it needs to be installed and activated on the website's origin server. Web hosting services can usually handle this for website operators. Once it's activated on the origin server, the website will be able to load over HTTPS and all traffic to and from the website will be encrypted and secure.

 

What is a self-signed SSL certificate?

Technically, anyone can create their own SSL certificate by generating a public-private key pairing and including all the information mentioned above. Such certificates are called self-signed certificates because the digital signature used, instead of being from a CA, would be the website's own private key.

 

But with self-signed certificates, there's no outside authority to verify that the origin server is who it claims to be. Browsers don't consider self-signed certificates trustworthy and may still mark sites with one as "not secure," despite the https:// URL. They may also terminate the connection altogether, blocking the website from loading.

 

Self-signed certificates can have the same level of encryption as the trusted CA-signed SSL certificate

 

Typically, the self-signed certificates are used for testing purposes or internal usage. You should not use a self-signed certificate in production systems that are exposed to the Internet.

 

Is it possible to get a free SSL certificate?

There are few vendors provide free SSL certificate but very cautious before signing up and generating the SSL certificate.

Better to go with paid service with enterprise provider for SSL certificate.

 

Creating a Self-Signed SSL Certificate

Prerequisites

The OpenSSL toolkit is required to generate a self-signed certificate.
To check whether the openssl package is installed on your Linux system, open your terminal, type openssl version

 

[root@node1 dbs]# openssl version

OpenSSL 1.0.2k-fips  26 Jan 2017

[root@node1 dbs]#

 

If the openssl package is not installed on your system, you can install it with your distribution’s package manager:

 

#yum install openssl

 

Creating Self-Signed SSL Certificate

To create a new Self-Signed SSL Certificate, use the openssl req command:

 

openssl req -newkey rsa:4096 \

            -x509 \

            -sha256 \

            -days 365 \

            -nodes \

            -out example.crt \

            -keyout example.key

 

Let’s breakdown the command and understand what each option means:

-newkey rsa:4096 --- Creates a new certificate request and 4096 bit RSA key (The default is 2048 bits)

-x509 --- Creates a X.509 Certificate.

-sha256 --- Use 265-bit SHA (Secure Hash Algorithm).

-days 365 --- The number of days to certify the certificate for 365 days.

-nodes --- Creates a key without a passphrase.

-out example.crt --- Specifies the filename to write the newly created certificate to. (You can specify any file name)

-keyout example.key --- Specifies the filename to write the newly created private key to. (You can specify any file name)

 

Once you hit Enter, the command will generate the private key and ask you a series of questions. The information you provided is used to generate the certificate.

 

[root@node1 ~]# mkdir Certs

[root@node1 ~]# cd Certs/

[root@node1 Certs]# pwd

/root/Certs

[root@node1 Certs]# ls -ltrh

total 0

[root@node1 Certs]# openssl req -newkey rsa:4096 \

>             -x509 \

>             -sha256 \

>             -days 365 \

>             -nodes \

>             -out example.crt \

>             -keyout example.key

Generating a 4096 bit RSA private key

............................................++

......................................................................++

writing new private key to 'example.key'

-----

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [XX]:

 

Enter the information requested and press Enter.

 

Country Name (2 letter code) [XX]:IN

State or Province Name (full name) []:Karnataka

Locality Name (eg, city) [Default City]:Bangalore

Organization Name (eg, company) [Default Company Ltd]:Mallik

Organizational Unit Name (eg, section) []:IT

Common Name (eg, your name or your server's hostname) []:mallik.com

Email Address []:mallik@gmail.com

 

The certificate and private key will be created at the specified location. Use the ls command to verify that the files were created:

 

[root@node1 Certs]# ls -ltrh

total 8.0K

-rw-r--r-- 1 root root 3.2K May 26 01:24 example.key

-rw-r--r-- 1 root root 2.1K May 26 01:24 example.crt

[root@node1 Certs]#

 

That’s it! You have generated a new self-signed SSL certificate.

It is always a good idea to back up your new certificate and key to external storage.

 

Creating Self-Signed SSL Certificate without Prompt

If you want to generate a self-signed SSL certificate without being prompted for any question use the -subj option and specify all the subject information:

 

openssl req -newkey rsa:4096 \

            -x509 \

            -sha256 \

            -days 3650 \

            -nodes \

            -out example.crt \

            -keyout example.key \

            -subj "/C=IN/ST=Karnataka/L=Bangalore/O=Mallik/OU=IT/CN=mallik.com"

 

[root@node1 Certs]# pwd

/root/Certs

[root@node1 Certs]# ls -ltrh

total 0

[root@node1 Certs]# openssl req -newkey rsa:4096 \

>             -x509 \

>             -sha256 \

>             -days 3650 \

>             -nodes \

>             -out example.crt \

>             -keyout example.key \

>             -subj "/C=IN/ST=Karnataka/L=Bangalore/O=Mallik/OU=IT/CN=mallik.com"

Generating a 4096 bit RSA private key

................................................++

.................................................................................................++

writing new private key to 'example.key'

-----

[root@node1 Certs]# ls -ltrh

total 8.0K

-rw-r--r-- 1 root root 3.2K May 26 01:30 example.key

-rw-r--r-- 1 root root 2.0K May 26 01:30 example.crt

[root@node1 Certs]#

 

The fields, specified in -subj line are listed below:

 

C= --- Country name. The two-letter ISO abbreviation.

ST= --- State or Province name.

L= --- Locality Name. The name of the city where you are located.

O= --- The full name of your organization.

OU= --- Organizational Unit.

CN= --- The fully qualified domain name.

 

Conclusion

We have shown you how to generate a self-signed SSL certificate using the openssl tool. Now that you have the certificate, you can configure your application to use it.

Feel free to leave a comment if you have any questions.

 

How to verify the SSL certificates?

You can verify the certificate using keycdn site

https://tools.keycdn.com/ssl

 

[root@node1 Certs]# pwd

/root/Certs

[root@node1 Certs]#

 

[root@node1 Certs]# cat example.key

-----BEGIN PRIVATE KEY-----

MIIJQgIBADANBgkqhkiG9w0BAQEFAASCCSwwggkoAgEAAoICAQChyzr6xUMfIOxM

Bigm0M4/BWzWbl/S2d63Ctm/ZMZoPPIo8GTPs+vhZqD33XGm7ktkwQv5IqadybXY

dmlKSgl73SS0ccWR14teZVhGolGJFCFZFKTZ8E3hfcvMWU/yP2s8WQ4YNuy06NVq

rwCxNvWTk1s5gXiqpTj1wrSz+jUrHe7I0OLRIfuJYUzCU9ZLsdRwyd4jPWN2oTH3

5evXDqe/4cGBhXrbcikT4wkR/mGy8V4ywJSil2ZDUBiNEyVuQaUiw8IpUdcXz6pN

o+85r+z7GRBtJ86JCGAotIBhEvtt2X48JKRI44Jp50EEz1fYPp+6XdDPApomPpxt

PDndFgiF8Trw3+S74QZZzdoGS7CFYT+4hVVu0ver9zD/D1Eaaqs4W2+I6MnVIqXf

eef6Y4YQDD1iXnPzvWOEEZ8PRWB9lRO9Wt9852UGF3TZALfXcyyX2eudbS4Xa5rY

9nnIzZhCKrxp4QdEAF6JZcPiD9tNW1t6b3KjHDQkPfsq4V4aW7NYJkQXLCQMgFZ0

Uyu5Tas/vGpLomDm34kVhRM53gJi4/LHJyTynIKV0ANW5gNV6q+sgH1erI95IkWe

grxcpj9U28/IX0lDVp3sPThpmKp6oqv5ofC1aI9Pq8C254KNQbz64Vp7baXBAM95

jWyY4H1RPEsSqM65GqZE41Ytsn5sfwIDAQABAoICACdBZQ0FIZUC6vJDKCjKzs/R

pCee3hcNj5D/y/c5Hz6ZUvQF55/RerDUsseQ7gtfk+FdSWdoNd7g2wMrL767mfN4

o3g4hoQtNP5xPmrc5UWKIdE4Rgsu1+aXHAR3wwL2yjD5Noc/hE0clPNuhI960zi8

Lw0T0dAtiFVwqWFlmtLcT1xf+jhds2cQuYwIxVzWR8IZ6JDKS0C0Oah5G7CxOJhA

0S940GdAGqFdm4zHGLNPBtjviuCsNT6tx1crRxCqUsPNlWVrGvIHC74MwXVQrwH5

gdfKKb1rz9DwgpmK/oAXnh4/kbCxqqnBVzAwkpLAFwcjzg78BaSz2kZSr4Np3iUm

sMf/oXRooEjn4oWgyQ1LEaP4eLW8aXZz5V1hpXjHtl6c4afEIeBiTSbP0OAVo1wS

aP6p6WpZvJPxiugQmgeKpAyO8Rry8rAOHeRN50APQxM/SeB6kfuOPMNCpB6BkXD+

NYz/+E8YwFauD8ug35fDFFCA564Pp231Vua4wBLOwOXaqTw0yPo9dQFiaVygUmDJ

5m5k8eRmsWNZbkGzbMlk2mu+4zAfoURU4RiIj1uSk1xtF5PoOiPBQZBSp1XpesXe

m06SWPU5LnyphiAX+mwk5WZwl0PxaD67gsVfjbLQZuxo2Dc2EY6KW/aqlOxyNbUx

ycTKJpLno9ei5PWIUodhAoIBAQDMtk25s26vKdjzsDDDvanExAP/dSwLhmKYGVXS

3nuf9Assc5hHCU9iRs0wY0mlCOYS3+Z//3cb4jkIRz0nNoIf1MRvHeZRgoDOgN8P

dcXo29Qgz8jB4neHRxyr3YZO7OtpSJYUisbUyuhUlnAtFolJUOy8Umvz5KgwfP11

ohKUTS7zvVRIc9Wcm9Cqy/D0u7GCrI5JYXEWuU2M+ONBzJqZVAaxmqcWB37SxIou

Rbaz+XxmAU1Q3uAzdxTEqmXmruh2YmZQn97Y7hXwvZI5Y+FLLGW7/p3a1bno/rBK

EvpW6oOeMmkPyvO6Qw7hHJk6RlB1Mnc9AhtJiaoeLIYEFikRAoIBAQDKVENRUO/I

kyUZBokVypuIaHcqXvYkl3ohpKQVhW8R0zue78IbfKudaRVlGhzQA6YS2Yvbfc3m

OChfQMW+wZLtsZ1QctjFKL9DSW0jcqAkd5avxNknWgP+6yT2819VkVx153/tb+4f

yOVW0Ro6jmdC7z069JHn1vgWfqzvdwCZ7CHUskfbFpNLW7wO2i/4Oa0qHvoBRDdK

2gUR8vj+/U7PQHJa3T11CeTeaFy7/iWlhsIm4ZusUOKtxnqa9mBIxSj/QZr1eS5c

n3dgD3aIA/4vRdKv1vCAT5lWvMuay1oVgaYZj6fhEf9lITFdo+jNQQKvgsI482po

Rm1LAV3spbyPAoIBAQCPxHBtvJA9QDx3WAPHVEoIQEk2fUnDhktz2kcl4s4blb7L

JxuIuAciU+sC0pgD6W37T3eH6RfzYzr+j1lpUqSsSjbyeqMoF2jynhsJtoKZeNlP

ed3aHHwpa029fcQ3Zbgmpq2QG65r/1yPG2AIqk0cSlMYJyJdlPZxpzYyCPLXaCVT

VGH+yCr644r2iGfe2Hpf9WOwMVjExrqqCJlBurnQeOyCisRRek4dI56PxLH9T3eI

A94Sr5PxUO4q+Ci8i5YCnjSQ5tYQr5SFD/3DhFvS+YkxMC4YZAU+lq+kNhPOOsDY

tzqFttjYtQOcIQOTo5IZ3KlVFUeoq5/ntQkhkZahAoIBAHsC8g6tW6uhsqtdatkh

3p/3i4PqSlKC+hEcTVp8TsQSniwKbwRRLvG8IvLeBmh932mUdFCVUyrGN915vwA0

JoV3OELiIGL2XBqnuMAVf8HMJ4Rj9PbU5psFEetGmk6m2wUgI4oTRD504OKArSau

z59FXv/KFC9VZm4exDuvKWFrzEHJxqAwQItPka2iAAo13s8+GBfPBqLunKiGBdbZ

4SFmeGBAJI5U3p5N6xus7+yMWtxSzFfesYKfqokug+maXUub8n+OFp7foS95F0Ko

0tCg7fBUiNIqEvtGO6+4NOPgeEEQMyxGyfUNUjsxXWf1P5G/uR/w9l/Bec9hXF51

gYcCggEANiSeVHpCZSN78v0YTe/QE3wH3TtJtlvPuTpa0RD4ZSjV0KGWECcbT0G+

LXJai6tcIack9RPLJwmkXglC6YEPPpraZnSwJS3zkt0wwK9PJ5purm46qUxdmFZo

sepNfDvQJ1v3bWg4c0AfFP5bHrBRWwu8LcDrYgo+ZdPEdTWXJFqdvHVdtW/Szbc9

uP46IENTpBx97CwfshJgvp8tXq5CRCkyxXPTqvW+y0sicDUJEvMVE3TSkLWGEkL4

JvtRrJ29TDw1loKmf3uUo/AejUpX076xCx/znSbuC4QuSL0d1Zn5sxHngI/QfNE+

eUnqCTMDXkVzefAniD559uI4d6Ef5w==

-----END PRIVATE KEY-----

[root@node1 Certs]# cat example.crt

-----BEGIN CERTIFICATE-----

MIIFozCCA4ugAwIBAgIJALNK4s6wRd+BMA0GCSqGSIb3DQEBCwUAMGgxCzAJBgNV

BAYTAklOMRIwEAYDVQQIDAlLYXJuYXRha2ExEjAQBgNVBAcMCUJhbmdhbG9yZTEP

MA0GA1UECgwGTWFsbGlrMQswCQYDVQQLDAJJVDETMBEGA1UEAwwKbWFsbGlrLmNv

bTAeFw0yMTA1MjUyMDAwMjdaFw0zMTA1MjMyMDAwMjdaMGgxCzAJBgNVBAYTAklO

MRIwEAYDVQQIDAlLYXJuYXRha2ExEjAQBgNVBAcMCUJhbmdhbG9yZTEPMA0GA1UE

CgwGTWFsbGlrMQswCQYDVQQLDAJJVDETMBEGA1UEAwwKbWFsbGlrLmNvbTCCAiIw

DQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAKHLOvrFQx8g7EwGKCbQzj8FbNZu

X9LZ3rcK2b9kxmg88ijwZM+z6+FmoPfdcabuS2TBC/kipp3Jtdh2aUpKCXvdJLRx

xZHXi15lWEaiUYkUIVkUpNnwTeF9y8xZT/I/azxZDhg27LTo1WqvALE29ZOTWzmB

eKqlOPXCtLP6NSsd7sjQ4tEh+4lhTMJT1kux1HDJ3iM9Y3ahMffl69cOp7/hwYGF

ettyKRPjCRH+YbLxXjLAlKKXZkNQGI0TJW5BpSLDwilR1xfPqk2j7zmv7PsZEG0n

zokIYCi0gGES+23ZfjwkpEjjgmnnQQTPV9g+n7pd0M8CmiY+nG08Od0WCIXxOvDf

5LvhBlnN2gZLsIVhP7iFVW7S96v3MP8PURpqqzhbb4joydUipd955/pjhhAMPWJe

c/O9Y4QRnw9FYH2VE71a33znZQYXdNkAt9dzLJfZ651tLhdrmtj2ecjNmEIqvGnh

B0QAXollw+IP201bW3pvcqMcNCQ9+yrhXhpbs1gmRBcsJAyAVnRTK7lNqz+8akui

YObfiRWFEzneAmLj8scnJPKcgpXQA1bmA1Xqr6yAfV6sj3kiRZ6CvFymP1Tbz8hf

SUNWnew9OGmYqnqiq/mh8LVoj0+rwLbngo1BvPrhWnttpcEAz3mNbJjgfVE8SxKo

zrkapkTjVi2yfmx/AgMBAAGjUDBOMB0GA1UdDgQWBBQsKcacavJRZkeUNNKduye/

v3KR+TAfBgNVHSMEGDAWgBQsKcacavJRZkeUNNKduye/v3KR+TAMBgNVHRMEBTAD

AQH/MA0GCSqGSIb3DQEBCwUAA4ICAQBwLVman86V0LNmA01As8Ku2FrPlSs707Jo

6FBh9Ft2krwGv+RfXoruSVQ62pJMGczotvyRdQ/ctlyghQnmVXeJrENNV/bjV5QM

OPx4P49slUv6aP8+1rLzogmeYPuLnq+ZcncUVRBZ2oZHw8M51LRCHSUG+mCHbfk8

VCacI+eU/AgDG+wqVjSzWFJbzZptvsHX1lD3bbg8MswY8x1qUAIKFgpgr3y8GwGO

BWyXemKyDPrjNIKYVYKI5iSErLWWOzEJJPzvjrj30O4DYmAlTfR1RY2D/+di7sAU

o4sjl+1TG5YFyaoGv06YE+qRXTuPI3XGAPb13K+IEo1wZ8Zl3P98wtBKcz66ZERq

mqpX/aNoe+f0P89FpWW1Ju+QhOny7ueu5GviKbvquN+g4GITuM3XV6K5GeMMzSK9

8XZnJpnGPz3/8gcFKM6o7FoAhEjpCkiPhLZ0oJU1eN2VdyKpr27bLQUcFAbgkSPu

U3ACsQ7mxtUNl2FmXPJuBoAq6l51ubsGHHGaRHvdEK8guNBBLhxi4P7ftYmPBqMP

YP9da1Kn8EzBo/sJkgdjCCzH1WHzp8xdExXeJPYqCfGHQx2BUkvFX8y5V0ZfMzDG

5eHsyuSwM1+dnged+mbgDbxrEkDjY/P2zxPtYmEqS/nvnsPzIyu/rp8qiB+6MZs9

nvOTrbKQ/w==

-----END CERTIFICATE-----

[root@node1 Certs]#


URL: https://tools.keycdn.com/ssl


Validate of example.key file:

Validation of example.crt file:


Regards,

Mallik


Monday, May 24, 2021

Oracle DBA Training Announcement & Demo Classes

 

Oracle DBA Training Announcement & Demo Classes:

Join with me on Zoom Session on - Wed 26-May-2021 @ 9:00 AM IST

>>> Do register using below link and get the meeting details:


Check this out and do Register for the course using below link:

https://mallik034.blogspot.com/p/upcoming-batch-schedule.html

Course Details:

https://mallik034.blogspot.com/p/database-training.html

 

Agenda:

Course Details

Course Timings

Course Fees

Training and Demo Classes

Lab Access and Key Notes

Payment Methods

Q&A


#oracle #training #database #OracleDBA #database #oracledatabase #course #DBA


Regards,

Mallik


Sunday, May 23, 2021

19c DataGuard Build Document

Environment:

Primary: RAC12C

2 Node NODE – node1.localdomain.com

                         - node2.lcoaldomain.com


Standby: RACSB

2 Node NODE – oraclenode1.localdomain.com

            - oraclenode2.lcoaldomain.com


Check RAC12C database status

oracle$ srvctl status database -d RAC12C


Check RAC12C database listener and tnsnames.ora

oracle$ cd $ORACLE_HOME/network/admin

oracle$ cat listener.ora

Node1:

oracle$ cat tnsnames.ora

>>> RAC12C

>>> RAC12C1

>>> RAC12C2

oracle$ lsnrcrtl status

oracle$ lsnrctl status LISTENER_RAC12C


Check GI listener status on PROD servers

grid$ cd $ORACLE_HOME/network/admin

grid$ cat listener.ora

grid$ lsnrcrtl status

grid$ lsnrctl status LISTENER


Check for ASM instance status on PROD Server

grid$ ps -ef|grep smon

>>> on all nodes


Check for All listener status on PROD servers

Check for all listeners (LISTENER and LISTENER_RAC12C will run on all the nodes but SCAN listener may run on any available nodes)

ps -ef|grep tns

>>> LISTENER (Should Run on all node)

>>> ASMNET1LSNR_ASM (Should Run on all node)

>>> LISTNERE_SCAN1

>>> LISTNERE_SCAN2

>>> LISTNERE_SCAN3

>>> LISTNERE_RAC12C (Should Run on all node)


Check cluster resource and Listeners are registered with cluster on PROD server

# crsctl stat res -t -init

# crsctl stat res -t

>>> on all nodes

>>> if not addended please add with below commands

srvctl add listener -l LISTENER_RAC12C -p 1522 -o /u01/app/oracle/product/12.2.0.1/dbhome_1

srvctl start listener -l LISTENER_RAC12C

srvctl stop listener -l LISTENER_RAC12C

srvctl status listener -l LISTENER_RAC12C

lsnrctl status LISTENER_RAC12C


Verify local listener and scan listener on all the DB instances

SQL> show parameter local_listener

>>> If you don’t see local listener then add local listener with below commands

alter system set local_listener ='(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1522))))' scope=BOTH SID='RAC12C1';

alter system set local_listener ='(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1522))))' scope=BOTH SID='RAC12C2';

SQL> show parameter remote_listener


Verify TNSPING and the password from primary database on all nodes

oracle$ tnsping RAC12C

oracle$ tnsping RAC12C1

oracle$ tnsping RAC12C2

sqlplus sys/Mallik123@RAC12C as sysdba

sqlplus sys/Mallik123@RAC12C1 as sysdba

sqlplus sys/Mallik123@RAC12C2 as sysdba


Add the standby TNS details on PROD Servers

oracle$ cd $ORACLE_HOME/network/admin

Add below entries

oracle$ vi tnsnames.ora

>>> RACSB

>>> RACSB1

>>> RACSB2


Create initRAC12C.ora in PROD DB servers

On Primary side check for the iniRAC12C.ora under $ORACLE_HOME/dbs directory if not available please create local init file.

oracle$ cd $ORACLE_HOME/dbs

oracle$ ls -lthr


If no initRAC12C.ora found please crease it with below commands

sqlplus / as sysdba

SQL> show parameter spfile;

SQL> create pfile from pfile;


Once created copy it RACSB side

cd $ORACLE_HOME/dbs

scp initRAC12C1.ora oraclenode1:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/

scp initRAC12C1.ora oraclenode2:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/


Copy local password file from ASM on PROD Servers

On primary verify the local password file under $ORACLE_HOME/dbs directory if not available please copy it from ASM.

Get password file lcoation:

ASMCMD>pwget --dbuniquename RAC12C


Copy Password file to local file system

ASMCMD>pwcopy '+DATA/RAC12C/PASSWORD/pwdrac12c.292.1067048971' '/tmp/orapwRAC12C'


Copy Password to Standby

scp /tmp/orapwRAC12C oraclenode1:/tmp/orapwRAC12C

scp /tmp/orapwRAC12C oraclenode2:/tmp/orapwRAC12C


Copy from /tmp to $ORACLE_HOME/dbs on Standby Servers

oraclenode1@oracle$ cp /tmp/orapwRAC12C $ORACLE_HOME/dbs

oraclenode2@oracle$ cp /tmp/orapwRAC12C $ORACLE_HOME/dbs


Move Password file to DiskGroup on Standby Servers

ASMCMD>mkdir +DATA/RACSB

ASMCMD>pwcopy '/tmp/orapwRAC12C' '+DATA/RACSB/orapwRACSB'

RAC12C prechks


Connect to primary database and verify force logging and redo logs groups

sqlplus sys/Mallik123@RAC12C as sysdba

select force_logging from v$database;

>>> FORCE_LOGING Should be YES

ALTER DATABASE FORCE LOGGING;


set lines 190 pages 190

col instance for a8

col member for a50

SELECT t.INSTANCE, l.THREAD#, l.GROUP#,  l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

>>> which will display log groups


col member format a50

select GROUP#,TYPE,MEMBER from v$logfile;

select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,

MEMBERS,STATUS from v$log;


Add Standby Redo’s on RAC12C Server

Verify Primary side Standby Redo’s on RAC12C Server

col DBID for a10

select * from v$standby_log;

>>> if you don’t find any standby redologs then add the standby redo logs with 1 extra redo logs group on each thread

SQL> Alter database add standby logfile thread 1 group 5 ('+DATA', '+RECO') SIZE 200M;

SQL> Alter database add standby logfile thread 1 group 6 ('+DATA', '+RECO') SIZE 200M;

SQL> Alter database add standby logfile thread 1 group 7 ('+DATA', '+RECO') SIZE 200M;

SQL> Alter database add standby logfile thread 1 group 8 ('+DATA', '+RECO') SIZE 200M;

SQL> Alter database add standby logfile thread 1 group 9 ('+DATA', '+RECO') SIZE 200M;

 

Verify the standby redologs after adding at RAC12C Server

SQL>select * from v$standby_log;

SQL>select group#, thread#, sequence#, archived, status from v$standby_log;


Set dataguard parameter at RAC12C

 

Verify the below dataguard parameter at RAC12C and if not set please set as below

 

show parameter log_archive_config

show parameter standby_file_management

show parameter log_archive_dest_2

show parameter log_archive_dest_state_2

show parameter fal_server


>>> Set these parameter using below commands

alter system set log_archive_config = 'DG_CONFIG=(RAC12C,RACSB)' scope=both sid='*';

alter system set standby_file_management=AUTO scope=both sid='*';


alter system set log_archive_dest_2='SERVICE=RACSB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACSB' scope=both sid='*';

ALTER SYSTEM SET log_archive_dest_2='service=RACSB async valid_for=(online_logfiles,primary_role) db_unique_name=RACSB' scope=both sid='*';

 

alter system set log_archive_dest_state_2='DEFER' scope=both;

alter system set fal_server='RACSB' scope=both sid='*';

ALTER SYSTEM SET db_file_name_convert=' +DATA/RAC12C',' +DATA/RACSB' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert=' +DATA/RAC12C',' +DATA/RACSB',' +RECO/RAC12C',' +RECO/RACSB' SCOPE=SPFILE;


DR database restore script

cd /home/oracle/

vi Duplicate_RACSB_from_RAC12C.sh

 

export DT=`date '+%m%d%Y%H%M'`

export CUR_DATE=`date`

echo start_time $CUR_DATE

rman target sys/Mallik123@RAC12C auxiliary sys/Mallik123@RACSB1 log=/home/oracle /rmanLog_RACSB_dg_${DT}.log  << xx > dup_RACSB1_${DT}.log

run

{

ALLOCATE  AUXILIARY CHANNEL c1  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c2  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c3  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c4  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c5  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c6  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c7  DEVICE TYPE DISK ;

ALLOCATE  AUXILIARY CHANNEL c8  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c9  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c10  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c11  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c12  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c13  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c14  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c15  DEVICE TYPE DISK ;

ALLOCATE CHANNEL c16  DEVICE TYPE DISK ;

duplicate target database for standby from active database

spfile

parameter_value_convert 'RAC12C','RACSB'

set db_file_name_convert '+DATA/RAC12C/','+DATA/RACSB/'

set db_create_file_dest='+DATA'

set db_recovery_file_dest='+RECO'

set db_recovery_file_dest_size='50G'

set standby_file_management='AUTO'

set diagnostic_dest='/u01/app/oracle'

set db_name='RAC12C'

set db_unique_name='RACSB'

set log_file_name_convert='+DATA/RAC12C/','+DATA/RACSB/','+RECO/RAC12C/','+RECO/RACSB/'

set control_files='+DATA/RACSB/controlfile/control01.ctl','+RECO/RACSB/controlfile/control02.ctl'

set cluster_database='false'

set  core_dump_dest='/u01/app/oracle/diag/rdbms/racsb/RACSB1/cdump'

set  service_names='RACSB'

set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclenode1-vip .localdomain.com)(PORT=1523))))'

set remote_listener= orclscan.localdomain.com:1521'

;

}

exit;

xx

echo end_time $CUR_DATE

 

Create dump and audit directories at RACSB servers

Verify the dump and audit directories and if not created create them

ls -ld /u01/app/oracle/diag/rdbms/racsb/RACSB1/adump

ls -ld /u01/app/oracle/diag/rdbms/racsb/RACSB1/cdump

ls -ld /u01/app/oracle/diag/audit


mkdir -p /u01/app/oracle/diag/rdbms/RACSB/RACSB1/tnodee

mkdir -p /u01/app/oracle/diag/rdbms/RACSB/RACSB1/cdump

mkdir -p /u01/app/oracle/diag/audit

 

Create initRACSB.ora at Standby Servers

Create initRACSB.ora file under $ORACLE_HOME/dbs directory

vi initRACSB.ora

db_name=RAC12C

db_unique_name=RACSB

RACSB.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclenode1-vip.localdomain.com)(PORT=1523)))'

remote_listener='orclscan.localdomain.com:1521'

 

Verify RACSB listener and tnsnames.ora file at RACSB Server

oracle$ cd $ORACLE_HOME/network/admin

oracle$ cat listener.ora

 

>>> Add the static listener registration method in listener.ora

[oracle@oraclenode1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

ADR_BASE_LISTENER_RACSB = /u01/app/oracle

LISTENER_RACSB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclenode1-vip.localdomain.com)(PORT = 1523))

  )

SID_LIST_LISTENER_RACSB =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = RACSB)

      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)

      (SID_NAME = RACSB)

    )

  )

 

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_RACSB=ON               # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER_RACSB=SUBNET         # line added by Agent

 

oracle$ cat tnsnames.ora

>>> You should see all these below entries

 

>>> STANDB

>>> STANDB1

>>> STANDB2

>>> RAC12C

>>> RAC12C1

>>> RAC12C2

 

Start RACSB in nomount mode

Start the RACSB instance with local initRACSB.ora created in nomount mode.


. oraenv

>>> RACSB

 

vi .bash_profile

>>> set ORACLE_SID=RACSB

 

vi db.env

>>> export ORACLE_SID=RACSB


sqlplus / as sysdba

startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initRACSB.ora';

 

Add RACSB listener to cluster at RACSB Server

Will verify the STANDBD listener and add them to cluster.


srvctl add listener -l LISTENER_RACSB -p 1523 -o /u01/app/oracle/product/12.2.0.1/dbhome_1

srvctl start listener -l LISTENER_RACSB

srvctl stop listener -l LISTENER_RACSB

srvctl status listener -l LISTENER_RACSB

lsnrctl status LISTENER_RACSB

lsnrctl start LISTENER_RACSB

lsnrctl stop LISTENER_RACSB

 

srvctl remove listener -l LISTENER_RACSB

srvctl config listener -l LISTENER_RACSB

 

oracle$ lsnrcrtl status

oracle$ lsnrctl status LISTENER_RACSB

ps -ef|grep tns

 

Verify the tnsping and password connection at RACSB Server

oracle$ tnsping RAC12C

oracle$ tnsping RAC12C1

oracle$ tnsping RAC12C2

 

oracle$ tnsping RACSB

oracle$ tnsping RACSB1

oracle$ tnsping RACSB2

 

sqlplus sys/Mallik123@RAC12C as sysdba

sqlplus sys/Mallik123@RAC12C1 as sysdba

sqlplus sys/Mallik123@RAC12C2 as sysdba


sqlplus sys/Mallik123@RACSB as sysdba

sqlplus sys/Mallik123@RACSB1 as sysdba

 

Verify the RACSB listener are registered with cluster

grid$ crsctl stat res -t

 

Verify the RMAN connection with axillary instance

rman target sys/Mallik123@RAC12C auxiliary sys/Mallik123@RACSB

rman target sys/Mallik123@RAC12C auxiliary sys/Mallik123@RACSB1

 

Start RMAN active database duplication


Start the RMAN active database duplication and monitor the RACSB restore status using restore log file.

Run the Active database duplicate for standby restore script as below.

cd /home/oracle

nohup ./Duplicate_RACSB_from_RAC12C.sh > /dev/null &

tail -f /home/oracle/rmanLog_RACSB_dg_<DAT_TIMESTAMP>.log

 

Check RACSB restore status

Check the restore status by logging into ASM and datafile directory size.

>>> At RACSB ASM side

ASMCMD> cd +DATA/RACSB/DATAFILE

ASMCMD>ls -l

 

>>> At using restore logfile

tail -f /home/oracle/rmanLog_RACSB_dg_<DAT_TIMESTAMP>.log


After Restore complete verify the RACSB

sqlplus / as sysdba

select name,open_mode from v$database;

select instance_name, status from v$instance;

shut immediate

 

Create NODE initRACSB1.ora from from initRAC12C1.ora at RACSB Server

We will rename initRAC12C1.ora (already created and moved to RACSB in step 10) as initRACSB1.ora and modify the parameter as per RACSB servers.

 

oracle$ cp initRAC12C1.ora initRACSB1.ora

>>> Edit initRACSB1.ora and replace all RAC12C parameter as RACSB parameters

Example: .......

RAC12C1.instance_number=1>>> RACSB1.instance_number=1

RAC12C2.instance_number=2>>> RACSB2.instance_number=2

 

>>> Also we will change the SCAN listener and Local listener as per RACSB name.

node-sacn >>> node-sscan

node1-vip >>> oraclenode1-vip 

node2-vip >>> oraclenode2-vip 

 

Start RACSB instance using initSTANDB1.ora

Start the RACSB1 instance using above modified pfile initRACSB1.ora and create SPFILE inside DATA diskgroup


. oraenv

>>> RACSB1


vi .bash_profile

>>> export ORACLE_SID=RACSB1

 

vi db.env

>>> export ORACLE_SID=RACSB1

 

sqlplus / as sysdba

startup mount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initRACSB1.ora';

create spfile='+DATA/RACSB/PARAMETERFILE/spfileRACSB.ora' from pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initRACSB1.ora';

shut immediate;

 

ASMCMD>cd '+DATA/RACSB/PARAMETERFILE/

ASMCMD> ls -l

 

Create local pfile initSTANDB1.ora point to SPFILE inside diskgroup

Create local pfile initRACSB1.ora to point to SPFILE inside disk_group.

cd $ORACLE_HOME/dbs

mv initRACSB1.ora initRACSB1.ora_after_NODE_modified_GOLD

cp initRACSB1.ora_Final_point_to_SPfile initRACSB1.ora

 

Node1>>>

cd $ORACLE_HOME/dbs

cat > initRACSB1.ora

spfile='+DATA/RACSB/PARAMETERFILE/spfileRACSB.ora'

cat initRACSB1.ora


Node2 >>>

cat > initRACSB2.ora

spfile='+DATA/RACSB/PARAMETERFILE/spfileRACSB.ora'

cat initRACSB2.ora


Start RACSB1 instance and verify the SPFILE

Start RACSB1 instance and verify the it should start with SPFILE which is inside Disk_Group

 

sqlplus / as sysdba

startup mount;

 

show parameter spfile

shut immediate


Convert RACSB database to NODE and add NODE parameter

srvctl add database -d RAC12C -n RAC12C -o '/u01/app/oracle/product/12.2.0.1/dbhome_1' -p '+DATA/RACSB/PARAMETERFILE/spfileRACSB.ora' -t IMMEDIATE -a 'DATA,RECO'

 

Add the NODE instances

srvctl add instance -d RAC12C -i RACSB1 -n oraclenode1

srvctl add instance -d RAC12C -i RACSB2 -n oraclenode2

srvctl config database -d RAC12C

srvctl modify database -d RAC12C -r physical_standby

srvctl remove database -d RAC12C

 

srvctl status database -d RAC12C

srvctl stop database -d RAC12C

srvctl start database -d RAC12C -o mount

srvctl status database -d RAC12C


Verify the RACSB instance started with SPFILE on all nodes

sqlplus / as sysdba

show parameter spfile

Enable Log Archive Destination on Primary RAC12C Server


sqlplus / as sysdba

show parameter log_archive_dest_state_2

alter system set log_archive_dest_state_2=enable scope=both sid='*';

alter system set log_archive_dest_state_2= DEFER scope=both sid='*';

show parameter log_archive_dest_state_2

archive log list


SELECT   a.thread#,  b. last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq-a.applied_seq   ARCHIVE_GAP

FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp

FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,

(SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;


Start MRP process on node1 at RACSB Server

sqlplus / as sysdba

set lines 300

select INST_ID,name,open_mode,db_unique_name,database_role from gv$database;

 

alter database recover managed standby database disconnect;

alter database recover managed standby database cancel;

 

select INST_ID,name,open_mode,db_unique_name,database_role from gv$database;

 

set lines 300 pages 300

select message from v$dataguard_status;

 

SELECT THREAD#,sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


Regards,

Mallik


Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month. [oracle@o...