This Blog will help you to do database refresh from Prod(source) database to Test/Dev(target) database :-
Prechecks :-
------------------------
To finding the size of each databases source and target :-
=================================
select sum(bytes)/1024/1024/1024 from dba_data_files;
==============================================
Do test connection from prod and test separately.
show parameter undo (both servers)
=====================================================
1. Stop the database on target server.
> shut immediate (Target)
Database closed.
Database dismounted.
ORACLE instance shut down.
$ nslookup zne-db1021
Truncated, retrying in TCP mode.
Server: 143.97.1.115
Address: 143.97.1.115#53
Name: zne-db1021.statoil.no
Address: 10.80.129.25
========================================
On Source : Check for any scheduled backup, if yes the stopped it during activity.
crontab -l
crontab -e
put # in archivelog files then save it
select * from v$backup; (Source)
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- ------------------- ----------
1 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
2 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
3 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
4 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
5 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
6 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
7 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
8 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
9 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
10 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
11 NOT ACTIVE 1.1163E+13 04/09/2021 17:38:29 0
alter database begin backup;
//////alter database end backup;/////////////
select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- ------------------- ----------
23 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
24 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
25 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
26 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
28 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
29 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
30 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
31 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
32 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
33 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
34 ACTIVE 1.1167E+13 05/10/2021 14:02:04 0
=========================================
select name from v$datafile;
cd /db/ORACLE/P535/
pwd
nohup scp /db/ORACLE/P535/* zne-db1002.statoil.no:/dba/ORACLE/T561I/ &
sqldba
alter database end backup;
Create temporary control file.
-------------------------------------------------------------------
alter database backup controlfile to trace as '/tmp/control__1.ctl';
#######################################################################
*
ERROR at line 1:
ORA-01277: file '/tmp/control_P535.ctl' already exists
if get like this error then change the Control file name.
========================================================
>alter system switch logfile;(do one or two times)
>alter system switch logfile;
alter system checkpoint;
exit
cdarch : Go to archive log location and copy all switch logfiles to target server.
ls -lrt
-rw-r-----. 1 oracle oinstall 1846579712 Oct 9 17:11 archP535.log1_7111_1075889375.dbf
-rw-r-----. 1 oracle oinstall 452724736 Oct 9 17:21 archP535.log1_7112_1075889375.dbf
-rw-r-----. 1 oracle oinstall 5672960 Oct 9 17:21 archP535.log1_7113_1075889375.dbf
-rw-r-----. 1 oracle oinstall 1767936 Oct 9 17:21 archP535.log1_7114_1075889375.dbf
$ date
$ Sat Oct 9 17:21:32 CEST 2021
by the date you can copy the above files:-
-----------------------------------------
pwd
/dbprog/oracle/admin/P535/arch
scp archP535.log1_71* zne-db1021.statoil.no:/dbprog/oracle/admin/T535AZ/arch
Password
archP535.log1_7100_1075889375.dbf 100% 1772MB 139.7MB/s 00:12
archP535.log1_7101_1075889375.dbf 100% 1765MB 135.7MB/s 00:12
###################################################################################
%cd /tmp
ls -lrt|grep control
-rw-r--r--. 1 oracle oinstall 19231 Oct 8 18:20 control_P535.ctl
-rw-r--r--. 1 oracle oinstall 19231 Oct 9 17:20 control_new_P535.ctl
cat control_new_P535.ctl(select new control file)
CREATE CONTROLFILE REUSE DATABASE "P535" RESETLOGS ARCHIVELOG
MAXLOGFILES 315
MAXLOGMEMBERS 2
MAXDATAFILES 60
MAXINSTANCES 63
MAXLOGHISTORY 149577
LOGFILE
GROUP 21 (
'/dblogA/ORACLE/P535/redo021.log',
'/dblogB/ORACLE/P535/redo021.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 22 (
'/dblogA/ORACLE/P535/redo022.log',
'/dblogB/ORACLE/P535/redo022.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 23 (
'/dblogA/ORACLE/P535/redo023.log',
'/dblogB/ORACLE/P535/redo023.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 24 (
'/dblogA/ORACLE/P535/redo024.log',
'/dblogB/ORACLE/P535/redo024.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 25 (
'/dblogA/ORACLE/P535/redo025.log',
'/dblogB/ORACLE/P535/redo025.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 26 (
'/dblogA/ORACLE/P535/redo026.log',
'/dblogB/ORACLE/P535/redo026.log'
) SIZE 2000M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 31 (
-- '/dblogA/ORACLE/P535/standby_redo031.log',
-- '/dblogB/ORACLE/P535/standby_redo031.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 32 (
-- '/dblogA/ORACLE/P535/standby_redo032.log',
-- '/dblogB/ORACLE/P535/standby_redo032.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 33 (
-- '/dblogA/ORACLE/P535/standby_redo033.log',
-- '/dblogB/ORACLE/P535/standby_redo033.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 34 (
-- '/dblogA/ORACLE/P535/standby_redo34.log',
-- '/dblogB/ORACLE/P535/standby_redo34.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 35 (
-- '/dblogA/ORACLE/P535/standby_redo35.log',
-- '/dblogB/ORACLE/P535/standby_redo35.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 36 (
-- '/dblogA/ORACLE/P535/standby_redo36.log',
-- '/dblogB/ORACLE/P535/standby_redo36.log'
-- ) SIZE 2000M BLOCKSIZE 512
DATAFILE
'/db/ORACLE/P535/system.1573.1034588157',
'/db/ORACLE/P535/sysaux.1572.1034588157',
'/db/ORACLE/P535/undotbs2.1564.1034591343',
'/db/ORACLE/P535/data02blob.1568.1034588157',
'/db/ORACLE/P535/data02blob.1571.1034588157',
'/db/ORACLE/P535/data02blob.1570.1034588157',
'/db/ORACLE/P535/data02blob.1567.1034588157',
'/db/ORACLE/P535/sysaudit.4565.1034588157',
'/db/ORACLE/P535/undotbs1.4575.1034591057',
'/db/ORACLE/P535/data02blob.4630.1035524311',
'/db/ORACLE/P535/data02blob.3834.1050989947',
'/db/ORACLE/P535/data02blob.1565.1034588157',
'/db/ORACLE/P535/data05.1563.1034588157',
'/db/ORACLE/P535/supply01.4567.1034588157',
'/db/ORACLE/P535/remit.1566.1034588157',
'/db/ORACLE/P535/data05.4568.1034588157',
'/db/ORACLE/P535/data02blob.1738.1052717133',
'/db/ORACLE/P535/data05.4566.1034588157',
'/db/ORACLE/P535/data05.4564.1034588157',
'/db/ORACLE/P535/data02blob.1913.1053326743',
'/db/ORACLE/P535/data02blob.2309.1054444387',
'/db/ORACLE/P535/data02blob.1794.1055654467',
'/db/ORACLE/P535/data03blob.5035.1069943247',
'/db/ORACLE/P535/data03blob.5068.1069943279',
'/db/ORACLE/P535/data03blob.5069.1069943295',
'/db/ORACLE/P535/data03blob.5070.1069943301',
'/db/ORACLE/P535/data05.1560.1034588157',
'/db/ORACLE/P535/data05.4563.1034588157',
'/db/ORACLE/P535/data05.1562.1034588157',
'/db/ORACLE/P535/data07.1569.1034588157',
'/db/ORACLE/P535/data07.4433.1034588157',
'/db/ORACLE/P535/data07.1574.1034588157',
'/db/ORACLE/P535/data07.1531.1034588155',
'/db/ORACLE/P535/data07.1561.1034588157'
CHARACTER SET WE8DEC
;
copy the start nomount point script and modify the Database_name(source to Target) and also check the db/dbfra/dbprog1/dbprog2 with Target db:-
=============================================================================================================
sqldba
exit
===================================================================================== Now come to Target Server and do the follow steps =========================
pwd
/db/ORACLE/T535AZ
ls -lrt
-rw-r-----. 1 oracle oinstall 22086144 Oct 9 16:12 change_tracking.f
-rw-r-----. 1 oracle oinstall 220971008 Oct 9 16:12 current1.ctl
-rw-r-----. 1 oracle oinstall 255705088 Oct 9 16:12 current1.ctl_old
-rw-r-----. 1 oracle oinstall 21474844672 Oct 9 16:15 data02blob.1565.1034588157
-rw-r-----. 1 oracle oinstall 33554440192 Oct 9 16:19 data02blob.1567.1034588157
-rw-r-----. 1 oracle oinstall 30245134336 Oct 9 16:22 data02blob.1568.1034588157
-rw-r-----. 1 oracle oinstall 33554440192 Oct 9 16:26 data02blob.1570.1034588157
-rw-r-----. 1 oracle oinstall 21474844672 Oct 9 16:28 data02blob.1571.1034588157
-rw-r-----. 1 oracle oinstall 26830970880 Oct 9 16:31 data02blob.1738.1052717133
-rw-r-----. 1 oracle oinstall 6429876224 Oct 9 16:32 data02blob.1794.1055654467
-rw-r-----. 1 oracle oinstall 20401102848 Oct 9 16:35 data02blob.1913.1053326743
-rw-r-----. 1 oracle oinstall 11798585344 Oct 9 16:36 data02blob.2309.1054444387
-rw-r-----. 1 oracle oinstall 33554440192 Oct 9 16:40 data02blob.3834.1050989947
it shows the all copy files which your copied from source to Target:-
=====================================================================
dsid
sqldba
startup nomount
show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 90
control_files string /db/ORACLE/T535AZ/control_01.c
tl
control_management_pack_access string DIAGNOSTIC+TUNING
!rm -f /db/ORACLE/T535AZ/control_01.ctl
--------------------------------------------------
use REUSE SET to create control file without
removing old file.
--------------------------------------------------
>CREATE CONTROLFILE SET DATABASE "T535AZ" RESETLOGS ARCHIVELOG
MAXLOGFILES 315
MAXLOGMEMBERS 2
MAXDATAFILES 60
MAXINSTANCES 63
MAXLOGHISTORY 149577
LOGFILE
GROUP 21 (
'/dblogA/ORACLE/T535AZ/redo021.log',
'/dblogB/ORACLE/T535AZ/redo021.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 22 (
'/dblogA/ORACLE/T535AZ/redo022.log',
'/dblogB/ORACLE/T535AZ/redo022.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 23 (
'/dblogA/ORACLE/T535AZ/redo023.log',
'/dblogB/ORACLE/T535AZ/redo023.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 24 (
'/dblogA/ORACLE/T535AZ/redo024.log',
'/dblogB/ORACLE/T535AZ/redo024.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 25 (
'/dblogA/ORACLE/T535AZ/redo025.log',
'/dblogB/ORACLE/T535AZ/redo025.log'
) SIZE 2000M BLOCKSIZE 512,
GROUP 26 (
'/dblogA/ORACLE/T535AZ/redo026.log',
'/dblogB/ORACLE/T535AZ/redo026.log'
) SIZE 2000M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 31 (
-- '/dblogA/ORACLE/T535AZ/standby_redo031.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo031.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 32 (
-- '/dblogA/ORACLE/T535AZ/standby_redo032.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo032.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 33 (
-- '/dblogA/ORACLE/T535AZ/standby_redo033.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo033.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 34 (
-- '/dblogA/ORACLE/T535AZ/standby_redo34.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo34.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 35 (
-- '/dblogA/ORACLE/T535AZ/standby_redo35.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo35.log'
-- ) SIZE 2000M BLOCKSIZE 512,
-- GROUP 36 (
-- '/dblogA/ORACLE/T535AZ/standby_redo36.log',
-- '/dblogB/ORACLE/T535AZ/standby_redo36.log'
-- ) SIZE 2000M BLOCKSIZE 512
DATAFILE
'/db/ORACLE/T535AZ/system.1573.1034588157',
'/db/ORACLE/T535AZ/sysaux.1572.1034588157',
'/db/ORACLE/T535AZ/undotbs2.1564.1034591343',
'/db/ORACLE/T535AZ/data02blob.1568.1034588157',
'/db/ORACLE/T535AZ/data02blob.1571.1034588157',
'/db/ORACLE/T535AZ/data02blob.1570.1034588157',
'/db/ORACLE/T535AZ/data07.1574.1034588157',
'/db/ORACLE/T535AZ/data07.1531.1034588155',
'/db/ORACLE/T535AZ/data07.1561.1034588157'
CHARACTER SET WE8DEC
;
Control file created.
exit
******************************* SOURCE SIDE ************************************
#% cdarch
oracle@s126-noe-oracle-prod01 1038%
oracle@s126-noe-oracle-prod01 1038%
oracle@s126-noe-oracle-prod01 1038%ls -lrt
total 286220
-rw-r-----. 1 oracle oinstall 167954432 Apr 20 18:42 o1_mf_1_412_j7wqw036_.arc
-rw-r-----. 1 oracle oinstall 4437504 Apr 20 18:42 o1_mf_1_413_j7ws51yw_.arc
-rw-r-----. 1 oracle oinstall 114102784 Apr 20 18:42 o1_mf_1_414_j7xxzoxv_.arc
-rw-r-----. 1 oracle oinstall 1536 Apr 20 18:42 o1_mf_1_415_j7xxzq8f_.arc
-rw-r-----. 1 oracle oinstall 3072 Apr 20 18:42 o1_mf_1_416_j7xxzs3l_.arc
-rw-r-----. 1 oracle oinstall 6559232 Apr 20 18:42 o1_mf_1_417_j7y10jj8_.arc
-rw-r-----. 1 oracle oinstall 11776 Apr 20 18:42 o1_mf_1_418_j7y10mrb_.arc
-rw-r-----. 1 oracle oinstall 3584 Apr 20 18:42 o1_mf_1_419_j7y10nnq_.arc
oracle@s126-noe-oracle-prod01 1039%sqldba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 13 11:44:58 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
SYS at P018 >alter system switch logfile;
System altered.
SYS at P018 >/
System altered.
SYS at P018 >/
System altered.
>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
oracle@s126-noe-oracle-prod01 1040%pwd
/dbprog/oracle/admin/P018/arch
oracle@s126-noe-oracle-prod01 1041%ls -lrt
total 404708
-rw-r-----. 1 oracle oinstall 167954432 Apr 20 18:42 o1_mf_1_412_j7wqw036_.arc
-rw-r-----. 1 oracle oinstall 4437504 Apr 20 18:42 o1_mf_1_413_j7ws51yw_.arc
-rw-r-----. 1 oracle oinstall 114102784 Apr 20 18:42 o1_mf_1_414_j7xxzoxv_.arc
-rw-r-----. 1 oracle oinstall 1536 Apr 20 18:42 o1_mf_1_415_j7xxzq8f_.arc
-rw-r-----. 1 oracle oinstall 3072 Apr 20 18:42 o1_mf_1_416_j7xxzs3l_.arc
-rw-r-----. 1 oracle oinstall 6559232 Apr 20 18:42 o1_mf_1_417_j7y10jj8_.arc
-rw-r-----. 1 oracle oinstall 11776 Apr 20 18:42 o1_mf_1_418_j7y10mrb_.arc
-rw-r-----. 1 oracle oinstall 3584 Apr 20 18:42 o1_mf_1_419_j7y10nnq_.arc
-rw-rw----. 1 oracle oinstall 121290240 Oct 13 11:45 archP018.log1_618_1070390955.dbf
-rw-rw----. 1 oracle oinstall 28672 Oct 13 11:45 archP018.log1_619_1070390955.dbf
-rw-rw----. 1 oracle oinstall 10752 Oct 13 11:45 archP018.log1_620_1070390955.dbf
oracle@s126-noe-oracle-prod01 1042%scp archP018.log1_6* s126noestid01.cloud.equinor.com:/dbprog/oracle/admin/T018/arch
\S
Kernel \r on an \m
oracle@s126noestid01.cloud.equinor.com's password:
archP018.log1_618_1070390955.dbf 100% 116MB 115.7MB/s 00:01
archP018.log1_619_1070390955.dbf 100% 28KB 9.5MB/s 00:00
************************* TARGET SERVER ***********************************
cdarch
oracle@s126noestid01 1009%ls -lrt
total 0
oracle@s126noestid01 1010%pwd
/dbprog/oracle/admin/T018/arch
oracle@s126noestid01 1011%sqldba
=======================================
restore archivelog from logseq 13713 until logseq 13790;
==========================================
recover database using backup controlfile until CANCEL;
1024 % nslookup s126noestid01
Server: 143.97.1.115
Address: 143.97.1.115#53
Name: s126noestid01.cloud.equinor.com
Address: 10.73.48.100
oracle@s126noestid01 1015%pwd
/dbprog/oracle/admin/T018/arch
oracle@s126noestid01 1016%ls -lrt
total 118488
-rw-r-----. 1 oracle oinstall 121290240 Oct 13 11:57 archP018.log1_618_1070390955.dbf
-rw-r-----. 1 oracle oinstall 28672 Oct 13 11:57 archP018.log1_619_1070390955.dbf
-rw-r-----. 1 oracle oinstall 10752 Oct 13 11:57 archP018.log1_620_1070390955.dbf
oracle@s126noestid01 1017% sqldba
>recover database using backup controlfile until CANCEL;
'/dbprog/oracle/admin/T018/arch/archP018.log1_618_1070390955.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
alter database open resetlogs;
select name,open_mode from v$database;
shut immediate
startup mount
nid target=/
Change database ID of database T535AZ? (Y/[N]) => Y
Database ID for database T535AZ changed to 3886015162.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
sqldba
startup mount
alter database open resetlogs;
select name,open_mode from v$database;
ALTER TABLESPACE TEMP ADD TEMPFILE '/db/ORACLE/U318_T/temp.4577.1034592649' SIZE 10240M REUSE AUTOEXTEND OFF; 2 (change db name)
=========================================================================
Project Specific Not Required.
Go to Vdb12 and do this steps:-
-----------------------
orasids -r Db_name
%orasids -r T535AZ
dbid=T535AZ node=ZNE-DB1021.STATOIL.NO system_pwd=Y5#G9VSPMGA75LZM8YZC sys_pwd=Q2#NQOE4UGSPSZKYF_42 status=T appl=SING
after come to Target Db and do follow steps:-
---------------------------------
Target db:-
-----------
alter user SYSTEM identified by J7_OH919VXBPGHC6RH0A;
alter user SYS identified by A3#L5FGZA_BH___U#PUZ;
alter database flashback on;
after this check osmossad in Vdb12 if osmossad is working fine the end.
******************************************************************************
===============SYS at U439E >alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/dblog/ORACLE/U439E/redo_03.log'
ORA-00312: online log 3 thread 1: '/dbfra/oracle/U439E/redo_03.log'
SYS at U439E >exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
oracle@mmp-tdb556 1020%cd /dblog/ORACLE/U439E/
oracle@mmp-tdb556 1021%cd /dbfra/oracle/U439E/
oracle@mmp-tdb556 1022%cd /dblog/ORACLE/U439E/
oracle@mmp-tdb556 1023%ls -lrt
total 201744
-rw-r----- 1 oracle oinstall 206585856 May 11 13:56 control_02.ctl
oracle@mmp-tdb556 1024%
oracle@mmp-tdb556 1024%
oracle@mmp-tdb556 1024%dsid
SID=U439E
oracle@mmp-tdb556 1025%
oracle@mmp-tdb556 1025%
oracle@mmp-tdb556 1025%rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed May 11 13:58:05 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: U439E (DBID=2291910332, not open)
RMAN> select group#,thread#,status from v$log;
using target database control file instead of recovery catalog
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CLEARING
3 1 CLEARING_CURRENT
2 1 CLEARING
RMAN> alter database clear logfile group 3;
Statement processed
RMAN> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CLEARING
3 1 CURRENT
2 1 CLEARING
RMAN> exit
Recovery Manager complete.
oracle@mmp-tdb556 1026%
oracle@mmp-tdb556 1026%
oracle@mmp-tdb556 1026%dsid
SID=U439E
oracle@mmp-tdb556 1027%
oracle@mmp-tdb556 1027%
oracle@mmp-tdb556 1027%sqldba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 11 13:59:30 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
SYS at U439E >alter database open resetlogs;
Database altered.
SYS at U439E >
===============================
If osmossad not get or any linser Error do follow steps:-
--------------------------------------------------
>show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER
parallel_force_local boolean FALSE
SYS at T535AZ >exit
psog tns
%cat /etc/oratab|grep T535AZ
T535AZ:/dbprog/oracle/product/12.1.0.2.28:Y
cdnet
ls -lrt
-rw-r-----. 1 oracle oinstall 373 Oct 31 2013 shrept.lst
drwxr-x---. 2 oracle oinstall 64 Jul 25 2014 samples
-rw-r-----. 1 oracle oinstall 371 Aug 27 02:30 ldap.ora
-rw-r-----. 1 oracle oinstall 1095 Aug 27 02:30 tnsnames.ora
-rw-r-----. 1 oracle oinstall 1211 Aug 27 02:30 sqlnet.ora
-rw-r--r--. 1 oracle oinstall 662 Aug 27 02:42 listener.ora
pwd
cat listener.ora
SQLNET.AUTHENTICATION_SERVICES = (NONE)
INBOUND_CONNECT_TIMEOUT_LISTENER = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF
USE_PLUG_AND_PLAY_LISTENER = OFF
USE_CKPFILE_LISTENER = OFF
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCPM)
(PROTOCOL = TCP)
(Host = zne-db1021.statoil.no)
(Port = 10001)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = T535AZ_DGMGRL)
(ORACLE_HOME = /dbprog/oracle/product/12.1.0.2.28)
(SID_NAME = T535AZ)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 0
LOGGING_LISTENER = OFF
TRACE_LEVEL_LISTENER = OFF
sqldba
set lines 200
set pages 200
col NAME for a15
col NETWORK_NAME for a20
col FAILOVER_TYPE for a20
col FAILOVER_METHOD for a20
col EDITION for a20
select NAME,CREATION_DATE,NETWORK_NAME,FAILOVER_TYPE,SERVICE_ID,GLOBAL_SERVICE,FAILOVER_METHOD,EDITION,MAX_LAG_TIME,GSM_FLAGS from dba_services;
NAME CREATION_DATE NETWORK_NAME FAILOVER_TYPE SERVICE_ID GLO FAILOVER_METHOD EDITION MAX_LAG_TIME GSM_FLAGS
--------------- ------------------- -------------------- -------------------- ---------- --- -------------------- -------------------- ------------------------------ ----------
SYS$BACKGROUND 28/03/2012 19:03:39 1 NO
SYS$USERS 28/03/2012 19:03:39 2 NO
P535 28/03/2012 19:03:54 P535 3 NO
P535_RW 09/03/2020 10:31:20 P535_RW TRANSACTION 4 NO BASIC ANY 0
P535_DGB 28/01/2021 14:06:12 P535_DGB 5 NO
T535AZ 09/10/2021 17:41:41 T535AZ 6 NO
#delete above files like P535_RW, P535_DGB and P535 files #
SYS at T535AZ >exec dbms_service.DELETE_SERVICE('P535')
PL/SQL procedure successfully completed.
exec dbms_service.STOP_SERVICE('P535_RW')
exec dbms_service.DELETE_SERVICE('P535_DGB')
#do like this up to P535_DGB after that run below script#
set lines 200
set pages 200
col NAME for a15
col NETWORK_NAME for a20
col FAILOVER_TYPE for a20
col FAILOVER_METHOD for a20
col EDITION for a20
select NAME,CREATION_DATE,NETWORK_NAME,FAILOVER_TYPE,SERVICE_ID,GLOBAL_SERVICE,FAILOVER_METHOD,EDITION,MAX_LAG_TIME,GSM_FLAGS from dba_services;
NAME CREATION_DATE NETWORK_NAME FAILOVER_TYPE SERVICE_ID GLO FAILOVER_METHOD EDITION MAX_LAG_TIME GSM_FLAGS
--------------- ------------------- -------------------- -------------------- ---------- --- -------------------- -------------------- ------------------------------ ----------
SYS$BACKGROUND 28/03/2012 19:03:39 1 NO
SYS$USERS 28/03/2012 19:03:39 2 NO
T535AZ 09/10/2021 17:41:41 T535AZ 6 NO
exec DBMS_SERVICE.CREATE_SERVICE(service_name => 'T535AZ_RW',network_name => 'T535AZ_RW', failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);
PL/SQL procedure successfully completed.
CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('T535AZ_RW');
END IF;
END;
/
Trigger created.
exec dbms_service.start_service('T535AZ_RW');(Start the Services)
set lines 200
set pages 200
col NAME for a15
col NETWORK_NAME for a20
col FAILOVER_TYPE for a20
col FAILOVER_METHOD for a20
col EDITION for a20
select NAME,CREATION_DATE,NETWORK_NAME,FAILOVER_TYPE,SERVICE_ID,GLOBAL_SERVICE,FAILOVER_METHOD,EDITION,MAX_LAG_TIME,GSM_FLAGS from dba_services;
NAME CREATION_DATE NETWORK_NAME FAILOVER_TYPE SERVICE_ID GLO FAILOVER_METHOD EDITION MAX_LAG_TIME GSM_FLAGS
--------------- ------------------- -------------------- -------------------- ---------- --- -------------------- -------------------- ------------------------------ ----------
SYS$BACKGROUND 28/03/2012 19:03:39 1 NO
SYS$USERS 28/03/2012 19:03:39 2 NO
T535AZ_RW 09/10/2021 17:57:43 T535AZ_RW SELECT 3 NO BASIC ANY 0
T535AZ 09/10/2021 17:41:41 T535AZ 6 NO
exit
now you can check osmossad in vdb12. now it's will be work.
========================================================================================================The*End===============================================================================================================
Comments
Post a Comment