Create Oracle Standby database in 11gr2 Standard edition

Standard edition Data Guard enabling

Dataguard feature avaliable only in EE, But we can enable it in SE like below,

Primary
=======

Host: 192.168.56.101 (Already Database 11.2.0.4 running Standard edition)

DB: msc

Standby
=======

Host: 192.168.56.102 (11.2.0.4 standard software installed, no db)

DB: msc (we should create as standby)


Steps:
=====

1. Check archive log enabled in primary, otherwise enable it.

   SQL> select name from v$database;
 
   NAME
   ---------
   MSC
 
   SQL> !date
   Fri Mar 30 11:10:54 IST 2018
 
   SQL> archive log list;
   Database log mode              Archive Mode
   Automatic archival             Enabled
   Archive destination            /u01/archive/
   Oldest online log sequence     1
   Next log sequence to archive   3
   Current log sequence           3


2. create pfile from spfile (Primary)

    SQL> create pfile='/tmp/initdr.ora' from spfile;
 
    File created.
 
    SQL> ! ls -ltr /tmp/initdr.ora
    -rw-r--r--. 1 oracle oinstall 973 Mar 30 11:11 /tmp/initdr.ora


3. create standby controlfile (Primary)

   SQL> alter database create standby controlfile as '/tmp/standby.ctl';
 
   Database altered.
 
   SQL> ! ls -ltr /tmp/standby.ctl
   -rw-r-----. 1 oracle oinstall 9748480 Mar 30 11:15 /tmp/standby.ctl


4. backup primary database plus archivelog (Primary)

   [oracle@smartdb ~]$ rman target /
 
   Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 30 11:17:06 2018
 
   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
   connected to target database: MSC (DBID=2695929158)
 
   RMAN> backup database plus archivelog;
 

5. create the directory structure same as primary, it depends (Standby)

   
      mkdir -p /u01/app/oracle/oradata/msc
      mkdir -p /u01/app/oracle/fast_recovery_area/msc
      mkdir -p /u01/app/oracle/admin/msc/adump
      mkdir -p /u01/app/oracle/fast_recovery_area/MSC

6. copy pfile, controlfile backup, passwd file to standby (primary)

     [oracle@smartdb tmp]$ scp -pr initdr.ora oracle@smartdr:/tmp
     initdr.ora                                                                                                                            100%  973    32.5KB/s   00:00
     [oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/oradata/msc/control01.ctl
     standby.ctl                                                                                                                           100% 9520KB  21.7MB/s   00:00
     [oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/fast_recovery_area/MSC/control02.ctl
     standby.ctl                                                                                                                           100% 9520KB  29.3MB/s   00:00
     [oracle@smartdb tmp]$ scp -pr standby.ctl oracle@smartdr:/u01/app/oracle/fast_recovery_area/msc/control02.ctl
     standby.ctl                                                                                                                           100% 9520KB  25.3MB/s   00:00
     [oracle@smartdb MSC]$ scp -pr $ORACLE_HOME/dbs/orapwmsc oracle@smartdr:$ORACLE_HOME/dbs
   



7. copy backup to standby (primary)

    [oracle@smartdb MSC]$ scp -pr backupset oracle@smartdr:/u01/app/oracle/fast_recovery_area/MSC/
    o1_mf_annnn_TAG20180330T111725_fcvmvfz4_.bkp                                                                                          100%  478KB  10.7MB/s   00:00
    o1_mf_nnndf_TAG20180330T111727_fcvmvj0g_.bkp                                                                                          100% 1014MB  21.8MB/s   00:46
    o1_mf_ncsnf_TAG20180330T111727_fcvmwxvz_.bkp                                                                                          100% 9600KB  18.4MB/s   00:00
    o1_mf_annnn_TAG20180330T111815_fcvmwzm7_.bkp                                                                                          100% 3584     2.9MB/s   00:00

8. Crosscheck the files are copied to standby (standby)

 
8. using pfile start standby with nomount

     [oracle@smartdr tmp]$ export ORACLE_SID=msc
     [oracle@smartdr tmp]$ sqlplus / as sysdba
   
     SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 11:36:13 2018
   
     Copyright (c) 1982, 2013, Oracle.  All rights reserved.
   
     Connected to an idle instance.
   
     SQL> startup nomount pfile=/tmp/initdr.ora
     ORACLE instance started.
   
     Total System Global Area 1553305600 bytes
     Fixed Size                  2253544 bytes
     Variable Size             956304664 bytes
     Database Buffers          587202560 bytes
     Redo Buffers                7544832 bytes


 

9. mount standby (standby)

     SQL> alter database mount standby database;
   
     Database altered.


10.restore database (standby)

     [oracle@smartdr tmp]$ rman target /
   
     Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 30 11:38:02 2018
   
     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
     connected to target database: MSC (DBID=2695929158, not open)
   
     RMAN> restore database;
   
Restore finished.

11.recover standby  (standby)

   Copy archives from primary to standby archive location and do below cmd,
   
   SQL> recover standby database
   ORA-00279: change 930348 generated at 03/30/2018 11:06:36 needed for thread 1
   ORA-00289: suggestion : /u01/archive/1_3_972126346.dbf
   ORA-00280: change 930348 for thread 1 is in sequence #3
 
 
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
   auto
   ORA-00279: change 930415 generated at 03/30/2018 11:17:24 needed for thread 1
   ORA-00289: suggestion : /u01/archive/1_4_972126346.dbf
   ORA-00280: change 930415 for thread 1 is in sequence #4
   ORA-00278: log file '/u01/archive/1_3_972126346.dbf' no longer needed for this
   recovery
 
 
   ORA-00279: change 930445 generated at 03/30/2018 11:18:15 needed for thread 1
   ORA-00289: suggestion : /u01/archive/1_5_972126346.dbf
   ORA-00280: change 930445 for thread 1 is in sequence #5
   ORA-00278: log file '/u01/archive/1_4_972126346.dbf' no longer needed for this
   recovery
 
 
   ORA-00308: cannot open archived log '/u01/archive/1_5_972126346.dbf'
   ORA-27037: unable to obtain file status
   Linux-x86_64 Error: 2: No such file or directory
   Additional information: 3


Recovery completed.


Testing the db sync via scripts:
======================

rsync script: (in Primary)
------------
*/5 * * * *   rsync -av --ignore-existing  /u01/archive/* oracle@smartdr.mga.com:/u01/archive/

recover.sql: (standby)
------------
spool /tmp/log.recovery replace
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;
recover standby database
auto
spool off
exit

recover.sh: (standby)
----------
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
ORACLE_SID=msc; export ORACLE_SID
sqlplus / as sysdba @/u01/recover.sql


Scenario:
========

****In Primary,******

[oracle@smartdb archive]$ ls -ltr
total 484
-rw-r-----. 1 oracle oinstall 487936 Mar 30 11:17 1_3_972126346.dbf
-rw-r-----. 1 oracle oinstall   2048 Mar 30 11:18 1_4_972126346.dbf
[oracle@smartdb archive]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 11:52:24 2018

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

SQL> CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);  2    3    4    5    6    7    8

Table created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );  2
1 row created.

SQL> SQL>   2
1 row created.

SQL> SQL>   2
1 row created.

SQL> SQL>   2
1 row created.

SQL> SQL>   2
1 row created.

SQL> SQL>   2

1 row created.

SQL>
SQL> select * from customers;

        ID NAME                        AGE ADDRESS                       SALARY
---------- -------------------- ---------- ------------------------- ----------
         1 Ramesh                       32 Ahmedabad                       2000
         2 Khilan                       25 Delhi                           1500
         3 kaushik                      23 Kota                            2000
         4 Chaitali                     25 Mumbai                          6500
         5 Hardik                       27 Bhopal                          8500
         6 Komal                        22 MP                              4500

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
You have new mail in /var/spool/mail/oracle
[oracle@smartdb archive]$ ls -ltr
total 832
-rw-r-----. 1 oracle oinstall 487936 Mar 30 11:17 1_3_972126346.dbf
-rw-r-----. 1 oracle oinstall   2048 Mar 30 11:18 1_4_972126346.dbf
-rw-r-----. 1 oracle oinstall 352256 Mar 30 11:58 1_5_972126346.dbf
-rw-r-----. 1 oracle oinstall   1024 Mar 30 11:58 1_6_972126346.dbf
[oracle@smartdb archive]$


Then run rsync script in primary. (automate it in cronjob)

rsync * --ignore-existing /u01/archive/ oracle@smartdr:/u01/archive/

[oracle@smartdb archive]$ rsync * --ignore-existing /u01/archive/ oracle@smartdr:/u01/archive/
skipping directory .
You have new mail in /var/spool/mail/oracle
[oracle@smartdb archive]$

****In Secondary****

Scripts placed under directory /u01

[oracle@smartdr u01]$ ls -ltr
total 864
drwxr-xr-x. 2 oracle oinstall      6 Mar 28 21:50 two
drwxr-xr-x. 4 oracle oinstall     38 Mar 28 21:58 app
-rw-r-----. 1 oracle oinstall 872960 Mar 29 14:46 archive1_12_972041417.dbf
-rwxrwxrwx. 1 oracle oinstall    421 Mar 29 18:01 recover.sh
-rwxrwxrwx. 1 oracle oinstall    186 Mar 29 18:01 recover.sql
drwxr-x---. 2 oracle oinstall    102 Mar 30 11:59 archive
[oracle@smartdr u01]$


Run Recover.sh, it will kick recover.sql and recover standby.

[oracle@smartdr u01]$ sh recover.sh

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 12:02:26 2018

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production


TO_CHAR(SYSDATE,'YY
-------------------
2018-03-30 12:02:26


Log id now
----------
         4

ORA-00279: change 930445 generated at 03/30/2018 11:18:15 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_5_972126346.dbf
ORA-00280: change 930445 for thread 1 is in sequence #5


ORA-00279: change 931586 generated at 03/30/2018 11:58:08 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_6_972126346.dbf
ORA-00280: change 931586 for thread 1 is in sequence #6
ORA-00278: log file '/u01/archive/1_5_972126346.dbf' no longer needed for this
recovery


ORA-00279: change 931589 generated at 03/30/2018 11:58:11 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_972126346.dbf
ORA-00280: change 931589 for thread 1 is in sequence #7
ORA-00278: log file '/u01/archive/1_6_972126346.dbf' no longer needed for this
recovery


ORA-00308: cannot open archived log '/u01/archive/1_7_972126346.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@smartdr u01]$


Test the created table in standby,

[oracle@smartdr u01]$ export ORACLE_SID=msc
[oracle@smartdr u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 12:07:50 2018

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> alter database open read only;

Database altered.

SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ ONLY            MSC

SQL> select * from customers;

        ID NAME                        AGE ADDRESS                       SALARY
---------- -------------------- ---------- ------------------------- ----------
         1 Ramesh                       32 Ahmedabad                       2000
         2 Khilan                       25 Delhi                           1500
         3 kaushik                      23 Kota                            2000
         4 Chaitali                     25 Mumbai                          6500
         5 Hardik                       27 Bhopal                          8500
         6 Komal                        22 MP                              4500

6 rows selected.


Automate the scripts now.......ENJOY...........

To unlock apex workspace users account

SQL> SELECT workspace_id FROM apex_workspaces WHERE workspace = 'Worksapce_name';
          WORKSPACE_ID
          10567
Find  the APEX schema’s name for your version:
SQL> select username from dba_users where username like 'APEX%' order by 1;
    USERNAME
    APEX
    APEX_050200
    APEX_PUBLIC_USER


SQL> alter session set current_schema = APEX_050200;                    
Session altered.
SQL>


Unlock your ADMIN account with the following code:
 SQL> begin
      wwv_flow_security.g_security_group_id := 10567;
      wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('username');
      commit;
    end;
    /
PL/SQL procedure successfully completed.
SQL>

Error!! Could not deliver the output for Delivery channel:null . Please check the Log for error details..

while bursting sometimes we got this error.

Solution:

Please check From and To address in XML file.

How to tar the bigsize files in Linux?

While doing "tar" sometimes it shows unable to archive..file size is too large

we can do it by using E identifier

tar -cEvf abcd.tar

it will work..

Oracle 12c Architecture

Oracle Database 12c: Architecture Diagram

Database Architecture Diagram for Oracle Database 12c(cloud) on Oracle Education's website. It lists all the processes and the relationship between processes and other database components.

Multitenanat Architecture:

Multiple tenants share same resources on a mutual benefit for different purposes at a very broad level. The same applies to Oracle Database where Multiple Databases share a single instance of resources aiming for different purposes on the same Server. This Oracle Database which is built on Multitenant foundation is called Container Database(CDB), and each container(tenant) residing inside is called Pluggable Database(PDB, Container). 

 

Secret of Success

1. How You Think is Everything.
Always be positive. Think Success, not Failure. Beware of a negative environment.
This trait has to be one of the most important in the entire list. Your belief that you can accomplish your goals has to be unwavering. The moment you say to yourself “I can’t…”, then you won’t. I was always given the advice “never say I can’t” and I’d like to strike those words from the dictionary.
I’ve found that from time-to-time my attitude waivers. A mentor of mine once said “it’s ok to visit pity city, but you can’t stay and there comes a time when you need to leave”. Positive things happen to positive people.

2. Decide upon Your True Dreams and Goals: Write down your specific goals and develop a plan to reach them.
Write down my dreams and goals? Develop a plan to reach them? You mean like a project plan? Yes, that’s exactly what this means. You may have heard the old adage: A New Years resolution that isn’t written down is just a dream, and dreams are not goals.
Goals are those concrete, measurable stepping stones of achievement that track your progress towards your dreams. My goal is to start a second career as a freelance writer – what are your goals?

3. Take Action. Goals are nothing without action.
Be like Nike and “Just do it”. I took action by reaching out and started writing. Every day I try to take some action towards my goals. It may be small, but it’s still an action. Have you taken action towards your goals?

4. Never Stop Learning: Go back to school or read books. Get training & acquire skills.
Becoming a life long learner would benefit us all and is something we should instill in our kids. It’s funny that once you’re out of school you realize how enjoyable learning can be. What have you learned today?

5. Be Persistent and Work Hard: Success is a marathon, not a sprint. Never give up.
I think every story of success I read entails long hard hours of work. There is no getting around this and there is no free lunch. But, if you’re working towards something that you’re passionate about, something you love – then is it really work?

6. Learn to Analyze Details: Get all the facts, all the input. Learn from your mistakes.
I think you have to strike a balance between getting all the facts and making a decision with incomplete data – both are traits of successful people. Spend time gathering details, but don’t catch ‘analysis paralysis’.

7. Focus Your Time And Money: Don’t let other people or things distract you.
Remain laser focused on your goals and surround yourself with positive people that believe in you. Don’t be distracted by the naysayer’s or tasks that are not helping you achieve your goals.

8. Don’t Be Afraid To Innovate: Be different. Following the herd is a sure way to mediocrity.
Follow through on that break-out idea you have. Ask yourself “What would I do if I wasn’t afraid?”

9. Deal And Communicate With People Effectively: No person is an island. Learn to understand and motivate others.
Successful people develop and nurture a network and they only do that by treating people openly, fairly and many times firmly. There is nothing wrong about being firm – just don’t cross the a-hole line. How do you deal with people?

10. Be Honest And Dependable: Take responsibility, otherwise numbers 1 – 9 won’t matter.
Enough said.