Restore Banner Account

We have several processes which can result in the deletion of a Banner account. They are:

  • PWPDTEA - This is a Banner job that deletes the Banner accounts of persons marked as terminated employees in Banner. It is scheduled to run on the first Monday of every month. It deletes only the Banner account, but it also identifies Workflow, Banner Relationship Manager, and Banner Document Management accounts for these persons, which must be deleted manually through their respective applications.

  • PWPDLEA - This is a Banner job that deletes the Banner accounts of persons marked as being on long-term leave in Banner. It is scheduled to run on the first Monday of every month. It deletes only the Banner account, but it also identifies Workflow, Banner Relationship Manager, and Banner Document Management accounts for these persons, which must be deleted manually through their respective applications.

  • /home/oracle/oakdba/daily/purge_unused_user_accounts.shl - This is a shell script that deletes the Banner accounts of persons who have not used their accounts for at least one year. The script runs from oracle's crontab on the Banner database server. It is scheduled to run every evening at 10pm. It does NOT identify Workflow, BRM, or BDM accounts. A suggested future task is to create a Banner job modeled after one of the above jobs, and to retire the crontab script.

  • DBREFRESH_TEST - This is a process flow in AMTST8 Automic, which is used to replace the TEST Banner database with a copy of what is currently in the PROD Banner database. It is run manually two or three times per year by consensus of the Banner Operating Committee. During the refresh process, a full export is taken of the previous incarnation of the TEST database before replacing it with PROD data, so it is generally possible after a refresh to restore any account that had been created in TEST only.

All of these processes back up the schema and the Banner privileges of each Banner account that they delete. Therefore, it is possible to restore them.

Information needed

In order to restore a Banner account, you need to know the userid of the account, and the export files that contained the backed-up data.

The userid of the account will be the same as the person's NetID. All export files will be located on the corresponding database server (TEST or PROD), in the directory /oracle/backedup/exp.

You will need to be able to login to the Banner database server, with sufficient privilege to become the oracle user. You will also need the password of the SYSTEM database user, and a personal DBA account, such as OAKDBA, OAKDBA3, &c.

Restoring the account

After a refresh

If you are restoring after a TEST database refresh, there are two relevant export files on the TEST database server: TEST_full.dmp and BANSECR.dmp.

Login to the database server and become the oracle user.

You will need to create a parameter file to restore the schema. Several model parameter files are located in the /oracle/backedup/exp directory. Select one (for example, joelmorrah_restore.par) to use as a model. Replace the SCHEMAS= and LOGFILE= values with values corresponding to the account you are restoring, and save it under a new name. Then run:

impdp system parfile=<your_parfile>.par

You will then need to restore the Banner permissions. To do this:

  1. Review the parameter files guruobj_restore.par and gurucls_restore_2.par. On the REMAP_SCHEMA= line, replace the second schema in the list with personal DBA account (e.g. OAKDBA, OAKDBA3, &c), if necessary, and save the file.

  2. Run:
    1. impdp system parfile=guruobj_restore.par
    2. impdp system parfile=gurucls_restore_2.par
  3. Open SQL Developer, and login to the database using your personal DBA account.
  4. Run:

    1. create table temp_guruobj as (select * from <your_dba_account>.guruobj where guruobj_userid = upper('<the_account_you_want_to_restore>'));

    2. create table temp_gurucls as (select * from <your_dba_account>.gurucls where gurucls_userid = upper('<the_account_you_want_to_restore>'));

    3. insert into bansecr.guruobj (select * from temp_guruobj);
    4. insert into bansecr.gurucls (select * from temp_gurucls);
    5. commit;
  5. You may need to recreate the GOBEACC record to associate the PIDM to the Banner account. In Banner as BANSECR, go to the GOAEACC form and search for the username. If it is not found, enter it as a new record and associate it with the user's GrizzlyID.

After PWPDTEA or PWPDLEA

The export files are located in /oracle/backedup/exp on the database server, but you will need to search the PWPDTEA and PWPDLEA log files in $BANJOBS_HOME to identify the files you need. You can reach those files from the database server (you do not have to login separately to the job server, though you could if you wanted to).

After you have become the oracle user on the database server:

  1. cd $BANJOBS_HOME

  2. grep -l -i -w <the_account_you_want_to_restore> pwpdlea* pwpdtea*

  3. If PWPDLEA and/or PWPDTEA were run in audit mode at any time, you may see multiple files with the name. Note the highest job number found in the file names returned.
  4. cd /oracle/backedup/exp

  5. There will be two files you need (substitute "pwpdlea" for "pwpdtea" as needed): pwpdtea_schemas_<job_number>.dmp and pwpdtea_privs_<job_number>.dmp.

Follow the instructions in the above section. When editing the parfiles, you will also need to change the value of DUMPFILE= to correspond to the dumpfile you need.

DataAdminHowTo