Copy-down Process for Remote Servers

Note: This is just the "copy-down" process; it does not include steps for installing or configuring the OS, database, or applications.

1. Create a new directory on "fcadmin01.fullcount.net" with the community code from from the COMMUNITY_CONFIG table:
   
   # COMMUNITY_CODE="<CommunityName>"
   # mkdir -p /home/oracle/communities/${COMMUNITY_CODE}/baseline
   # chown -R oracle:dba /home/oracle/communities/${COMMUNITY_CODE}
    
2. Create a new database baseline from "FC_OBJ_OWNER" schema on "odbprod02.fullcount.net".
   
   # COMMUNITY_CODE="<CommunityName>"
   # su - oracle
   # cd /u01/app/oracle/admin/tools/db_mgmt/baseline/v2/
   # ./baseline_expdp_to_11g.sh | tee baseline_expdp.log 2>&1
   # exit
   
   The "baseline_expdp.sh" script will create a file named "baseline_<mmddyyyy>.zip".
    
3. Upload the "baseline_<mmddyyyy>.zip" file created by the baseline script to the directory on "fcadmin01.fullcount.net" created in step 1.
   
   # cd /u01/app/oracle/admin/tools/db_mgmt/baseline/v2/
   # scp -p baseline_<mmddyyyy>.zip root@fcadmin01.fullcount.net:/home/oracle/communities/${COMMUNITY_CODE}/baseline
    
4. On "fcadmin01.fullcount.net"
   
   # cd /home/oracle/communities/${COMMUNITY_CODE}/baseline
   # unzip baseline_<mmddyyyy>.zip
    
5. Check out scripts from Subversion on "fcadmin01.fullcount.net"
   
   # cd /home/oracle/communities/${COMMUNITY_CODE}
   # svn checkout http://sourcecontrol.fullcount.net/repos/database/trunk/processes/implementation/copydown/sql
   # svn checkout http://sourcecontrol.fullcount.net/repos/database/branches/baseline-reorg
    
6. Execute the "drop_all.sql" script "fcadmin01.fullcount.net" as the "oracle" user. Passwords for below scripts in Databases-Prod KeePass database.
   
   # chown -R oracle:dba /home/oracle/communities/${COMMUNITY_CODE}
   # su - oracle
   # cd /home/oracle/communities/${COMMUNITY_CODE}/sql
   # sqlplus -L fc_user @drop_all.sql  | tee drop_all_fc_user.log 2>&1
   # sqlplus -L fcbo_user @drop_all.sql  | tee drop_all_fcbo_user.log 2>&1
   # sqlplus -L fc_obj_owner @drop_all.sql | tee drop_all_fc_obj_owner.log 2>&1
   # exit
    
7. Execute the "baseline_impdp.sh" script as the "oracle" user on "fcadmin01.fullcount.net" and pass the name of the import file as a parameter.
   
   # cp -p <path_to_file>/baseline.dpdmp /home/oracle
   # chown oracle:dba /home/oracle/baseline.dpdmp
   # su - oracle
   # cd /home/oracle/communities/${COMMUNITY_CODE}/baseline-reorg/v2
   Edit baseline_impdp.sh to change value of S_BASE environment variable to location of baseline_impdp.sh script and create log directory.
   # ./baseline_impdp.sh baseline.dpdmp | tee baseline_impdp.log 2>&1
    
8. Execute the "extract_process.sh" script as the "oracle" user on "fcadmin01.fullcount.net".
   
   # su - oracle
   # cd /usr/lib/oracle/xe/scripts/extraction_process
   # ./extract_process.sh fc_obj_owner <COMMUNITY_ID> ${COMMUNITY_CODE} | tee extract_process.log 2>&1
    
9. Move the database dump file created by the "extract_process.sh" script to /home/oracle/communities/<communityname>
    
10. Upload the directory created in step 1 to the remote server.
     
11. As of early 2022, the COMMUNITIES table should be manually created because of the creation of the FC_LOB_DATA table. COMMUNITIES can be copied by the most recent remote server build.
     
12. Import the dump file into the "FC_OBJ_OWNER" schema on the remote server.
    
    # mv <dumpfile> /home/oracle
    # chown -R oracle:dba /home/oracle
    # su - oracle
    # impdp system directory=dump dumpfile=name_of_your_dump_file schemas=fc_obj_owner remap_tablespace=fc_lob_data:fc_obj_owner_data logfile=import.log
    
    ***Note: The /home/oracle folder needs to be owned by oracle - it may be owned by root.
     
13. Execute the "DBMS_STATS.GATHER_SCHEMA_STATS" procedure to gather statistics for the "FC_OBJ_OWNER" schema on the remote server.
    
    # su - oracle
    # sqlplus / as sysdba
    sql> exec dbms_stats.gather_schema_stats( ownname=>'fc_obj_owner');
     
14. Execute the "grant_privs_plsql.sql" script to set up privileges and assign roles to users from the /home/oracle/[community]/sql folder.
    
    # su - oracle
    # sqlplus /nolog @grant_privs_plsql.sql | tee grant_privs_plsql.log 2>&1
     
15. Execute the "synonym_recreate_plsql.sql" script to drop and recreate synonyms for tables, packages, and sequences from the /home/oracle/[community]/sql folder.
    
    # su - oracle
    # sqlplus /nolog @synonym_recreate_plsql.sql | tee synonym_create_plsql.log 2>&1
     
16. Edit and execute the "Internet Independent Inactive Accounts.sql" script to rename and deactivate accounts in the "FC_OBJ_OWNER" schema on "odbprod02.fullcount.net".
     
17. Set FC_OBJ_ADMIN.COMMUNITY_CONFIG.HAS_REMOTE_SERVER to 'Y' for the community.
18. Upload WAR files for POSWebApplication and backoffice applications to remote server.
19. Configure database connections for POSWebApplication and backoffice applications on remote server.
20. Install new SSL cert for fullcountapp.net domain on remote server.
21. Deactivate application user accounts on odbprod01.

References

1. baseline_expdp.sh: http://sourcecontrol.fullcount.net/repos/database/branches/baseline-reorg/v2
2. extract_process.sh: http://sourcecontrol.fullcount.net/repos/database/trunk/processes/implementation/copydown/extraction_process
3. grant_privs_plsql.sql: http://sourcecontrol.fullcount.net/repos/database/trunk/processes/implementation/copydown/sql
4. synonym_recreate_plsql.sql: http://sourcecontrol.fullcount.net/repos/database/trunk/processes/implementation/copydown/sql
5. Internet Independent Inactive Accounts.sql: http://sourcecontrol.fullcount.net/repos/database/trunk/processes/implementation/internet_independent_inactive_accounts.txt