Rule Set "Applsys.Wf_deferred_r" Has Errors after upgrade

Rule Set "Applsys.Wf_deferred_r" Has Errors

After upgraded database from 10.2.0.4 to 11.2.0, Rule Set WF_DEFERRED_R  became invalid

MOS: 553673.1

To implement the solution, please execute the following steps:

1. Run that PL/SQL

sqlplus applsys/
declare
lagent sys.aq$_agent;
subscriber_exist exception;
pragma EXCEPTION_INIT(subscriber_exist, -24034);
begin
lagent := sys.aq$_agent('WF_DEFERRED',null,0);
dbms_aqadm.add_subscriber(queue_name=>'APPLSYS.WF_DEFERRED',subscriber=>lagent,rule=>'1=1');
exception
when subscriber_exist then
-- just add the rule if subscriber exists
dbms_aqadm.alter_subscriber(queue_name=>'APPLSYS.WF_DEFERRED',subscriber=>lagent,rule=>'1=1');
end;
/

Hope your issue resolved!!

Lsnrctl Start Fails with TNS-12546 or TNS: Permission Denied

Getting this error in log.xml,

 <txt>TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied

Reason:

The sqlnet.ora file is configured for TCP.VALIDNODE_CHECKING with a TPC.INVITED_NODES list that does not include the ip address on which the listener is trying to start. 

 The "permission denied" message is the result of the SQL*Net layer refusing a connection from the listener.

Solution:

Either unset TCP.VALIDNODE_CHECKING in the server side sqlnet.ora file or configure the TCP.INVITED_NODES list to include the ip address or hostname of the server where the listener is failing to start.

You can also refer--> What is TCP_VALIDNODE_CHECKING and How to Use It (Doc ID 462933.1)

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).