7 Replies Latest reply: Feb 21, 2012 11:42 PM by NikolayIvankin RSS

    sys and system schema are expired & locked after doing disaster recovery.

    Viren Patel
      Hi All,

      In one of our development database, I performed disaster recovery and it was successfull.
      After completing thre recovery, sys and system schema are expired & locked.
      So, I am not able to perform any kind of operation which is performed by sys and system user.

      Database Version : 11g r2
      Operating system : Red hat 5.5

      Please kindly suggest me, How can i solve this problem?

      Thanks,
      Viren Patel.
        • 1. Re: sys and system schema are expired & locked after doing disaster recovery.
          NikolayIvankin
          This is some sort of a mirracle. SYS even if expired and locked - can login:
          SQL> SELECT d.username, u.account_status
            2  FROM DBA_USERS_WITH_DEFPWD d, DBA_USERS u
            3  WHERE d.username = u.username and u.username=’SYS’
            4  ORDER BY 2,1;
          
          USERNAME                       ACCOUNT_STATUS
          ------------------------------ --------------------------------
          SYS                            OPEN
          
          SQL> alter user sys account lock;
          
          User altered.
          
          SQL> alter user sys password expire;
          
          User altered.
          
          SQL> quit
          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          
          C:\Tools\Oracle\scripts_oracle>sqlplus sys/manager as sysdba
          
          SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 20 17:09:14 2012
          
          Copyright (c) 1982, 2010, Oracle.  All rights reserved.
          
          
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          
          SQL> SELECT d.username, u.account_status
            2  FROM DBA_USERS_WITH_DEFPWD d, DBA_USERS u
            3  WHERE d.username = u.username and u.username=’SYS’
            4  ORDER BY 2,1;
          
          USERNAME                       ACCOUNT_STATUS
          ------------------------------ --------------------------------
          SYS                            EXPIRED & LOCKED
          Please, show us exact connection string and an error you are getting, while logging in.
          • 2. Re: sys and system schema are expired & locked after doing disaster recovery.
            CKPT
            It could be

            what is the users status in source database from where you have taken backup? Might it's locked so even after performing refresh the status remains same.

            See your below profile stats. All are unanswered. You simply testing patience? If not close all the threads as answered. Keep the forum clean.


            User Profile for Viren Patel
            Viren Patel     
                  
                  
            Handle:     Viren Patel  
            Status Level:     Newbie
            Registered:     Mar 4, 2010
            Total Posts:     9
            Total Questions:      8 (8 unresolved)
            Name     Viren

            Edited by: CKPT on Feb 20, 2012 7:47 PM
            • 3. Re: sys and system schema are expired & locked after doing disaster recovery.
              Viren Patel
              Hi,

              If it could be...
              Then what is the why to get out of this problem..?
              How to unlock sys and system account.?

              Thanks,
              Viren Patel.
              • 4. Re: sys and system schema are expired & locked after doing disaster recovery.
                NikolayIvankin
                set $ORACLE_HOME and $ORACLE_SID then try to connect by sqlplus
                post results here
                • 5. Re: sys and system schema are expired & locked after doing disaster recovery.
                  Viren Patel
                  Hi,

                  This is my ORACLE_HOME and ORACLE_SID output
                  [oracle@localhost ~]$ echo $ORACLE_HOME
                  */u01/app/oracle/product/11.2.0/db_1*
                  [oracle@localhost ~]$ echo $ORACLE_SID
                  osprod

                  Thanks,
                  Viren Patel
                  • 6. Re: sys and system schema are expired & locked after doing disaster recovery.
                    CKPT
                    If it could be...
                    Then what is the why to get out of this problem..?
                    How to unlock sys and system account.?
                    Unlock as below
                    sys@ORCL> alter user sys account lock;
                    
                    User altered.
                    
                    sys@ORCL> alter user system account lock;
                    
                    User altered.
                    
                    sys@ORCL> select username,account_status from dba_users where username in ('SYS','SYSTEM');
                    
                    USERNAME                       ACCOUNT_STATUS
                    ------------------------------ --------------------------------
                    SYSTEM                         LOCKED
                    SYS                            LOCKED
                    
                    sys@ORCL> alter user sys account unlock;
                    
                    User altered.
                    
                    sys@ORCL> alter user system account unlock;
                    
                    User altered.
                    
                    sys@ORCL> select username,account_status from dba_users where username in ('SYS','SYSTEM');
                    
                    USERNAME                       ACCOUNT_STATUS
                    ------------------------------ --------------------------------
                    SYSTEM                         OPEN
                    SYS                            OPEN
                    
                    sys@ORCL>
                    • 7. Re: sys and system schema are expired & locked after doing disaster recovery.
                      NikolayIvankin
                      Viren Patel wrote:
                      Hi,

                      This is my ORACLE_HOME and ORACLE_SID output
                      [oracle@localhost ~]$ echo $ORACLE_HOME
                      */u01/app/oracle/product/11.2.0/db_1*
                      [oracle@localhost ~]$ echo $ORACLE_SID
                      osprod
                      now connect by
                      sqlplus / as sysdba
                      Post here if any errors. Otherwise - unlock users like CKPT had wrote.