Restoring from an Oracle backup
There are two ways to restore an Oracle database from a backup.
Method 1
This method is recommended for restoring an Oracle database from a backup. Follow these steps:
1. Run Toad for Oracle 11.6.
2. Connect as the system user (by default, the parameters and password are saved): User (User/Schema) - SYS, connection type (Connect as) - SYSDBA.
3. Create a database and users with the request
create user AIST1 identified by q1234567;
grant all PRIVILEGES to AIST1;
GRANT SELECT ON "SYS"."GV_$RESERVED_WORDS" TO AIST1;
GRANT SELECT ON "SYS"."V_$RESERVED_WORDS" TO AIST1 ;
where: AIST1 - database name, q1234567 - connection password
Press F5.
5. Start the command prompt (cmd) as the administrator.
6. Execute the request (this request will work correctly if the names of the backup and the database match).
impdp aist/q1234567@ORATEST schemas=Aist directory=dpump_aist dumpfile=aist.dmp logfile=aist1_imp.log
where: impdp aist - import to the database
q1234567@ORATEST - connect to the database
schemas=Aist session
directory=dpump_aist - backup folder
dumpfile=aist.dmp - backup name
logfile=aist_imp.log - log is created
select * from dba_directories - view the directory, backup can be restored from
7. Backup from other schemas (i.e. database and backup names do not match):
impdp aist1/q1234567@ORATEST directory=dpump_aist dumpfile=aist.dmp logfile=aist1.log REMAP_SCHEMA=AIST:AIST1 TRANSFORM=oid:n:type
8. After restoring, open the created database AIST1 with the Normal connection type:
9. Execute the request.
When you restore an Oracle database from a backup, indexes are optimized but not entirely correct. For example, there are two indexes: on (Primary key) and second complex on (Primary key, Any column). After restoring the database from a backup, optimization takes place and only one index remains: (Primary key, Any column), which is bound to the primary key. Thus it becomes the primary key index and cannot be deleted without deleting the primary.
Changing this behavior is impossible since it is related to the Oracle export/import utilities.
ALTER TABLE ORGANIZATIONITEM DROP PRIMARY KEY CASCADE;
DROP INDEX IX_ORGANIZATIONITEM_N2;
ALTER TABLE ORGANIZATIONITEM ADD CONSTRAINT ORGANIZATIONITEM_PK PRIMARY KEY ("Id");
ALTER TABLE BASEMESSAGE DROP PRIMARY KEY CASCADE;
DROP INDEX BASEMESSAGEIDINDEX;
ALTER TABLE BASEMESSAGE ADD CONSTRAINT BASEMESSAGE_PK PRIMARY KEY ("Id");
ALTER TABLE WORKFLOWINSTANCE DROP PRIMARY KEY CASCADE;
DROP INDEX IX_WORKFLOWINSTANCE_STATUS;
ALTER TABLE WORKFLOWINSTANCE ADD CONSTRAINT WORKFLOWINSTANCE_PK PRIMARY KEY ("Id");After running the script, start ELMA.
Method 2
Use this method to restore a database only if the first method did not work. Follow these steps:
1. Run Toad for Oracle 11.6.
2. Connect as the system user (by default, the parameters and password are saved): User (User/Schema) - SYS, connection type (Connect as) - SYSDBA.
3. Create a database and users with the request
create user AIST1 identified by q1234567;
grant all PRIVILEGES to AIST1;
GRANT SELECT ON "SYS"."GV_$RESERVED_WORDS" TO AIST1;
GRANT SELECT ON "SYS"."V_$RESERVED_WORDS" TO AIST1 ;
where: AIST1 - database name, q1234567 - connection password.
Press F5.
4. Restore from the backup to the created database Import - SQL “LoaderWizard:
5. Select Import database (Imports the entire export file)
6. Click Next and select the database for importing in the Select schema field, in this case, AIST1
7. Click Next. At the next step, leave everything by default.
8. Click Next and specify the backup location.
9. Click Next and leave everything by default.
10. Click Next. The process of restoring the database will take a while.
11. After restoring, open the created database AIST1 with the Normal connection type
12. Execute the request.
When you restore an Oracle database from a backup, indexes are optimized but not entirely correct. For example, there are two indexes: on (Primary key) and second complex on (Primary key, Any column). After restoring the database from a backup, optimization takes place and only one index remains: (Primary key, Any column), which is bound to the primary key. Thus it becomes the primary key index and cannot be deleted without deleting the primary.
Changing this behavior is impossible since it is related to the Oracle export/import utilities.
ALTER TABLE ORGANIZATIONITEM DROP PRIMARY KEY CASCADE;
DROP INDEX IX_ORGANIZATIONITEM_N2;
ALTER TABLE ORGANIZATIONITEM ADD CONSTRAINT ORGANIZATIONITEM_PK PRIMARY KEY ("Id");
ALTER TABLE BASEMESSAGE DROP PRIMARY KEY CASCADE;
DROP INDEX BASEMESSAGEIDINDEX;
ALTER TABLE BASEMESSAGE ADD CONSTRAINT BASEMESSAGE_PK PRIMARY KEY ("Id");
ALTER TABLE WORKFLOWINSTANCE DROP PRIMARY KEY CASCADE;
DROP INDEX IX_WORKFLOWINSTANCE_STATUS;
ALTER TABLE WORKFLOWINSTANCE ADD CONSTRAINT WORKFLOWINSTANCE_PK PRIMARY KEY ("Id");After running the script, start ELMA.