oraclesysdba / Oracle-Learn- Public
Code Issues Pull requests Actions Projects Security Insights
main
Oracle-Learn- / Oracle Pluggable Database (PDB) Migration [Link]
oraclesysdba Update Oracle Pluggable Database (PDB) Migration [Link] d124638 · 2 minutes ago
253 lines (179 loc) · 6.53 KB
Code Blame Raw
1 ++++++++++++++++++++++++++++++++++++++++++++++++
2 Oracle Pluggable Database (PDB) Migration Guide
3 ++++++++++++++++++++++++++++++++++++++++++++++++
4
5 Version: 1.0
6 Author: Samson J
7 Date: 21032025
8
9 **************************************************************************************
10
11 1. Introduction
12 2. Workflow Tree
13 3. Step 1: Verify Old Primary Container Database
14 4. Step 2: Configure New Primary Container Database
15 5. Step 3: Create Pluggable Database (PDB)
16 6. Step 4: Open PDB and Apply Patches
17 7. Step 5: Set LOCAL_LISTENER
18 8. Conclusion
19
20
21 **************************************************************************************
22
23 $ Introduction
24
25 This document provides a step-by-step guide to migrating an Oracle Pluggable Database
26
27 ***********************
28 Workflow Tree
29 ***********************
30 1. Verify Old Primary Container Database
31 ├── Check database status
32 ├── Switch to the required container
33 ├── Create a user and grant privileges
34 ├── Retrieve and note datafile locations
35 ├── Change database mode to READ ONLY
36
37 2. Configure New Primary Container Database
38 ├── Add TNS entry in [Link]
39 ├── Verify new primary database status
40 ├── Create database link to old database
41
42 3. Create Pluggable Database (PDB)
43 ├── Create a shell script (create_pdb.sh)
44 ├── Execute the script to create the PDB
45
46 4. Open PDB and Apply Patches
47 ├── Open the pluggable database
48 ├── Apply patches using datapatch
49 ├── Run [Link] for validation
50 ├── Perform necessary database upgrade steps
51 ├── Recompile invalid objects
52 ├── Restart the database
53
54 5. Set LOCAL_LISTENER
55 ├── Set LOCAL_LISTENER parameter
56 ├── Register the listener
57 ________________________________________________________________________________
58
59 Step 1: Verify Old Primary Container Database
60
61 Connect to the old primary container database and verify its
62
63 SELECT name, open_mode, database_role, cdb FROM v$database;
64
65 ##Switch to the required container:
66
67 SQL>ALTER SESSION SET CONTAINER=&con_name;
68
69 Create a user with necessary privileges:
70
71 CREATE USER supernovaclone IDENTIFIED BY oracle;
72
73 GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO supernovaclone;
74
75 Retrieve the datafile locations:
76 ===============================
77
78 SELECT name FROM v$datafile;
79 📌 Note: Save the datafile locations for reference.
80
81 Change the database mode:
82 ****************************
83
84 SHUTDOWN IMMEDIATE;
85 STARTUP MOUNT;
86 ALTER DATABASE OPEN READ ONLY;
87 ________________________________________________________________________________
88
89
90 Step 2: Configure New Primary Container Database
91
92
93 Edit the [Link] file on the 19c server:
94
95
96 [Link] =
97 (DESCRIPTION =
98 (ADDRESS_LIST =
99 (ADDRESS = (PROTOCOL = TCP)(HOST = [Link])(PORT = 1521))
100 )
101 (CONNECT_DATA =
102 (SERVICE_NAME = orcl)
103 )
104 )
105
106
107 Verify the new primary database status:
108
109
110 SQL>SELECT name, open_mode, database_role, cdb FROM v$database;
111 SQL>Create a database link to the old primary database:
112
113
114 CREATE DATABASE LINK dbclone
115 CONNECT TO supernovaclone IDENTIFIED BY oracle
116 USING '&tnsname';
117 ________________________________________
118
119 Step 3: Create Pluggable Database (PDB)
120
121 Create a shell script named create_pdb.sh:
122
123
124 vi create_pdb.sh
125 Insert the following script content:
126
127
128 #!/bin/bash
129
130 # Load Oracle Environment Variables
131 . .profile
132
133 # Log File
134 LOG_FILE=/tmp/create_pdb.log
135
136 # Start Time
137 echo "====================" | tee -a $LOG_FILE
138 echo "Start Time: $(date)" | tee -a $LOG_FILE
139 echo "Creating Pluggable Database..." | tee -a $LOG_FILE
140
141 # Execute SQL Command
142 sqlplus -s / as sysdba <<EOF >> $LOG_FILE 2>&1
143 SET ECHO ON;
144 SET TIMING ON;
145 CREATE PLUGGABLE DATABASE newpdb
146 FROM pdbname@dbclone
147 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/stg/brblv_stgpdb/','+DATA',
148 '/u01/app/oracle/oradata/stg/pdbseed/','+DATA',
149 '/u01/app/oracle/oradata/stg/','+DATA',
150 '/u02/datafiles/','+DATA',
151 '/u03/datafiles/','+DATA',
152 '/u05/datafiles/','+DATA')
153 PARALLEL;
154 EXIT;
155 EOF
156
157 # End Time
158 echo "End Time: $(date)" | tee -a $LOG_FILE
159 echo "Pluggable Database creation completed." | tee -a $LOG_FILE
160 Run the script:
161 bash
162 CopyEdit
163 chmod +x create_pdb.sh
164 ./create_pdb.sh
165
166 nohup ./create_pdb.sh > /tmp/pdb_creation.log 2>&1 &
167
168 ________________________________________
180
181 Run [Link] to check for errors:
182
183
184 @?/rdbms/admin/[Link]
185
186 If errors occur on oracle database Component, use the following steps :
187
188 SLECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
189 [Link]
190
191 ******************************************************************************
192
193 sqlplus / as sysdba <<EOF
194 alter pluggable database all close;
195 alter pluggable database all open upgrade;
196 exit;
197 EOF
198
199 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/[Link] -d \
200 $ORACLE_HOME/rdbms/admin -C 'CDB$ROOT PDB$SEED' -l $ORACLE_BASE [Link]
201
202 sqlplus / as sysdba <<EOF
203 alter pluggable database all open read write;
204 exit;
205 EOF
206
207 $ORACLE_HOME/perl/bin/perl \
208 -I$ORACLE_HOME/perl/lib \
209 -I$ORACLE_HOME/rdbms/admin \
210 $ORACLE_HOME/rdbms/admin/[Link] \
211 -l /tmp/ \
212 -b postpatch_${ORACLE_SID}_recompile \
213 -C 'PDB$SEED' \
214 $ORACLE_HOME/rdbms/admin/[Link]
215
216 cd $ORACLE_HOME/OPatch
217 ./datapatch -verbose
218
219 $ORACLE_HOME/perl/bin/perl \
220 -I$ORACLE_HOME/perl/lib \
221 -I$ORACLE_HOME/rdbms/admin \
222 $ORACLE_HOME/rdbms/admin/[Link] \
223 -l /tmp/ \
224 -b postpatch_${ORACLE_SID}_recompile \
225 -C 'PDB$SEED' \
226 $ORACLE_HOME/rdbms/admin/[Link]
227
228 sqlplus / as sysdba <<EOF
229 shutdown immediate;
230 startup;
231 show pdbs;
232 EOF
233
234 select name, cause, type, message, status, action from PDB_PLUG_IN_VIOLATIONS where ty
235 ________________________________________
236 Step 5: Set LOCAL_LISTENER
237 Set the LOCAL_LISTENER parameter:
238
239
240 ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT
241 ALTER SYSTEM REGISTER;
242 ________________________________________
243
244
245 Conclusion
246
247
248 This guide provides a structured approach for:
249 ✅ Verifying the old primary database
250 ✅ Setting up a new primary database
251 ✅ Creating a database link
252 ✅ Migrating a PDB
253 ✅ Applying patches and recompiling objects
