-
Notifications
You must be signed in to change notification settings - Fork 14
/
Copy pathimport_fmw.sh
119 lines (105 loc) · 5.75 KB
/
import_fmw.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#!/bin/bash
## import_fmw.sh script version 1.0.
##
## Copyright (c) 2025 Oracle and/or its affiliates
## Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl/
##
### This script imports the tablespaces, schemas and roles generated by the export_fmw.sh script
### It uses the Oracle Data Pump Export and DDL extraction generated by export_fmw.sh to "move" a FMW/RCU DB system (typically JRF or SOA domain)
### to a different PDB or entirely different DB. It would typically be used to "migrate" source FMW schemas without connectivity to the original DB.
### - It identifies the schemas based on the information generated by the export script.
### - It requires a tns alias mapping to a service (attached to a single instance in RAC configuration) to conect to the precise PDB.
### Create an instance-specific service and an alias for it in tnsnames.ora. Pending to be automated. For example:
### [oracle@fmwdbnode1 ~]$ srvctl add service -db $ORACLE_UNQNAME -service export_soaedg.example.com -preferred SOADB231 -pdb SOADB23_pdb1
### [oracle@fmwdbnode1 ~]$ srvctl start service -s export_soaedg.example.com -db $ORACLE_UNQNAME
### [oracle@fmwdbnode1 ~]$ lsnrctl status | grep export_soaedg.example.com
### Service "export_soaedg.example.com" has 1 instance(s).
### [oracle@fmwdbnode1 ~]$ cat /u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/tnsnames.ora | grep export
### EXPORT_SOADB23_PDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=drdbrac12a-scan.dbsubnet.vcnpho80.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=export_soaedg.example.com)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))))
###
###
### Usage:
###
### ./import_fmw.sh [RCU_PASSWORD] [SYS_PASSWORD] [TNS_ALIAS] [IMPORT_DIRECTORY]
### Where:
### RCU_PASSWORD:
### Password provided for schemas when FMW RCU was executed to create the Database artifacts used by a FMW domain.
### SYS_PASSWORD:
### User sys's password in the PDB hosting the FMW systems.
### TNS_ALIAS:
### Alias in tnsnames.ora that identifies the connect string to be used for the export
### IMPORT_DIRECTORY:
### Directory where the export tar generated by export_fmw.sh has been extracted
if [[ $# -eq 4 ]];
then
export dt=`date +%y-%m-%d-%H-%M-%S`
export schema_pass=$1
export sys_pass=$2
export tns_alias=$3
export dumpdir=$4
else
echo ""
echo "ERROR: Incorrect number of parameters used: Expected 4, got $#"
echo ""
echo "Usage:"
echo " $0 [RCU_PASSWORD] [SYS_PASSWORD] [TNS_ALIAS] [IMPORT_DIRECTORY]"
echo ""
echo "Example: "
echo " $0 myrcupasswd123 mysyspasswd123 EXPORT_SOADB23_PDB1 /u01/dbdataexports "
exit 1
fi
if [ -f $dumpdir/schema_list.log ]; then
echo "Found schema log file, continuing..."
export schema_list=$(cat $dumpdir/schema_list.log)
cd $dumpdir
else
echo "Schema log file $dumpdir/schema_list.log does not exist!"
echo "Make sure you have extracted the resulted tar genertaed by the export_fmw.sh script in the $dumpdir directory!"
exit 1
fi
#SYS generic ops
echo "Creating import dir and role for reg access"
sqlplus sys/""${sys_pass}""@${tns_alias} as sysdba << EOF
DROP DIRECTORY DUMP_INFRA;
CREATE DIRECTORY DUMP_INFRA AS '$dumpdir';
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO SYS;
BEGIN EXECUTE IMMEDIATE 'CREATE ROLE REGISTRYACCESS'; END;
BEGIN EXECUTE IMMEDIATE 'CREATE ROLE STBROLE'; END;
BEGIN EXECUTE IMMEDIATE 'CREATE ROLE FMW_RO'; END;
set line 500;
column directory_name format a30;
column directory_path format a60;
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_INFRA';
EOF
echo "Creating tablespaces, creating schemas and assigning roles..."
sqlplus sys/""${sys_pass}""@${tns_alias} as sysdba << EOF
@$dumpdir/create_all_tablespaces.sql;
@$dumpdir/create_all_roles.sql;
@$dumpdir/create_all_schemas.sql;
EOF
echo "Importing SCHEMA REGISTRY INFO"
impdp \"sys/"${sys_pass}"@${tns_alias} as sysdba\" SCHEMAS=SYSTEM directory=DUMP_INFRA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import.log PARALLEL=1 CLUSTER=N encryption_password=${sys_passwd} TABLE_EXISTS_ACTION=APPEND
impdp \"sys/"${sys_pass}"@${tns_alias} as sysdba\" SCHEMAS=$schema_list directory=DUMP_INFRA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import_schemas.log PARALLEL=1 CLUSTER=N encryption_password=${sys_passwd} TABLE_EXISTS_ACTION=APPEND
#Initial grants to schemas
echo "Assigning tablespace and dump dir rights to schemas..."
for schema in $schema_list;do
echo "Updating schema rights for $schema..."
sqlplus sys/""${sys_pass}""@${tns_alias} as sysdba << EOF
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO $schema;
GRANT UNLIMITED TABLESPACE TO $schema;
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "${schema}";
EOF
done
echo "Real Schema list : $real_schema_list"
echo "Importing schemas exports..."
for schema in $schema_list;do
echo "Importing $schema..."
impdp ${schema}/"${schema_pass}"@${tns_alias} schemas=${schema} directory=DUMP_INFRA dumpfile=${schema}_export.dmp logfile=${schema}_import.log PARALLEL=1 CLUSTER=N encryption_password=${schema_pass};
done
echo "Re-assign roles to consolidate"
sqlplus sys/""${sys_pass}""@${tns_alias} as sysdba << EOF
@$dumpdir/create_all_schemas.sql;
EOF
echo "*********************************************************************************"
echo "************************************* DONE! *************************************"
echo "*********************************************************************************"