ORA-20079: full resync from primary database is not done

When we added new files in primary database sometime below errors raise in Stand by database while RMAN
backup is running.

RMAN backups are run from Standby server.  Whenever a structural change is made on the primary , attempts to resync from the primary using db_unique_name  during the standby backup fails:

ORA-20079: full resync from primary database is not done

doing automatic resync from primary
resyncing from database with DB_UNIQUE_NAME testdb

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 08/23/2014 22:45:24
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 08/23/2014 22:45:24
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

Files have been added at the Primary site

RMAN is trying to implicitly resync from the Primary using db_unique_name as it is aware that structural changes have been made.

However, RMAN is unable to connect to the primary because no connect string was used when connecting to the standby - in order to resync from another host in a Data Guard configuration , the connection to target must be made with a username, password and alias.

Solution:

Use a TNS connect string when connecting to the standby

Primary: testdb
standby: testdb123

$ rman target  sys/test123@testdb123 catalog rman/rman@rcat
RMAN> resync catalog from db_unique_name testdb;
resyncing from database with DB_UNIQUE_NAME testdb
starting full resync of recovery catalog
full resync complete



Now you can run RMAN backup.

Enable temporary sudo access

Below script would be useful how to give temporary  root sudo access  to user.


sudo -u test1 /home/test1/scripts/tempsudoaccess.sh oracle test101.testdb.com TICK000202


#!/bin/bash
# Para 1 => User
# Para 2 => Server
USAGE="tempsudoaccess.sh <User Name> <FQDN of Server> <Ticket Number>"
if [ $# -ne 3 ]; then
        echo $USAGE
        exit
fi
USERNAME="$1"
USERNAMEOK=""
USERNAMEOK="`id $USERNAME | grep ^id`"
SRVNAME="$2"
TICKET="$3"
if [ "$USERNAMEOK" != "" ]; then
        echo "Invalid User"
else
        echo "rm -f /etc/sudoers.d/$USERNAME" > /tmp/$USERNAME
        echo "# Access Granted per Ticket : $TICKET" > /tmp/${USERNAME}_sudo
        if [ "$USERNAME" == "oracle" ]; then
                echo "$USERNAME ALL=(root) NOPASSWD:ALL" >> /tmp/${USERNAME}_sudo
        else
                echo "$USERNAME ALL=(root) ALL" >> /tmp/${USERNAME}_sudo
        fi
        scp -rq /tmp/$USERNAME* $SRVNAME:/tmp/
        #ssh -n $SRVNAME "sudo mv -f /tmp/$USERNAME /opt; sudo /bin/chown root.root /tmp/${USERNAME}_sudo; sudo /bin/chmod 440 /tmp/${USERNAME}_sudo; sudo mv -f /tmp/${USERNAME}_sudo /etc/sudoers.d/$USERNAME; sudo at now + 7 days < /opt/$USERNAME"
        ssh -n $SRVNAME "sudo /bin/chown root.root /tmp/${USERNAME}_sudo; sudo /bin/chmod 440 /tmp/${USERNAME}_sudo; sudo mv -f /tmp/${USERNAME}_sudo /etc/sudoers.d/$USERNAME; sudo at now + 7 days < /tmp/$USERNAME"
fi

Change the sequence number

CREATE OR REPLACE PROCEDURE SYS.SEQUENCE_NEWVALUE(

seqowner VARCHAR2,

seqname VARCHAR2,

newvalue NUMBER) AS

ln NUMBER;

ib NUMBER;

BEGIN

SELECT last_number, increment_by

INTO ln, ib

FROM dba_sequences

WHERE sequence_owner = upper(seqowner)

AND sequence_name = upper(seqname);

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname ||

' INCREMENT BY ' || (newvalue - ln);

EXECUTE IMMEDIATE 'SELECT ' || seqowner || '.' || seqname ||

'.NEXTVAL FROM DUAL' INTO ln;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname

|| ' INCREMENT BY ' || ib;

END;

GRANT EXECUTE ON sequence_newvalue TO gokhan;

EXEC sequence_newvalue( 'GOKHAN', 'SAMPLE_SEQ', 10000 );


delete child record

 alter table test.Table1 enable constraint table1_FK;
alter table test.Table1 enable constraint table1_FK
                                                     *
ERROR at line 1:
ORA-02298: cannot validate (test.table1_fk) - parent keys not
found

select 'delete from '  ||c.owner||'.'||c.table_name ||' a where not exists (select ''x'' from ' ||r.owner||'.'||r.table_name ||' where '||rc.column_name||' = a.'||cc.column_name||')'
 from dba_constraints c,
 dba_constraints r,
 dba_cons_columns cc,
 dba_cons_columns rc
 where c.constraint_type = 'R'
 and c.owner not in ('SYS','SYSTEM')
 and c.r_owner = r.owner
 and c.owner = cc.owner
 and r.owner = rc.owner
 and c.constraint_name = cc.constraint_name
 and r.constraint_name = rc.constraint_name
 and c.r_constraint_name = r.constraint_name
 and cc.position = rc.position
 and c.owner = 'TEST'
 and c.table_name = 'TABLE1'
 and c.constraint_name = TABLE1_FK'

Change SYSMAN Password in 12C

Steps to follow if the current SYSMAN password is unknown

1. Stop all the OMS:


oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl stop oms

Output.

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
Note:

Execute the same command on the primary OMS machine and Standby as well. Do not include '-all' as the Admin Server needs to be up during this operation.

2. Modify the SYSMAN password:


oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd ktest100 -new_pwd Btest100


Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

3. Stop the Admin server on the primary OMS machine and re-start all the OMS:

oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl stop oms -all

Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl start oms

Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up