I did a hot RMAN backup of a server running Oracle 19c database, then moved the backup to a new server. This is the backupscript

BACKUP AS compressed BACKUPSET INCREMENTAL LEVEL=0 FORMAT 'Prod_Full_DB_%u_%s_%p' DATABASE plus archivelog;

When I completed the restore, I faced this error

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 8922136743 generated at 03/24/2024 04:19:33 needed for thread
1
ORA-00289: suggestion : /u03/archives/1_65472_1115909325.dbf
ORA-00280: change 8922136743 for thread 1 is in sequence #65472


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u03/archives/1_65472_1115909325.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

I solved this error by manually transferring the archives to the server. I also realized that the archive which was causing problem was made DURING the backup process. After the restore was completed I did some searching, and found a way to restore archives from the backup pieces:

run {
allocate channel t1 type disk;
set archivelog destination to '/u04/';
restore archivelog until sequence 65460;
release channel t1;
}

however, I am facing this error

archived log for thread 1 with sequence 1 is already on disk as file /u01/archives/1_1_1164473680.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/archives/1_2_1164473680.dbf
restore not done; all files read only, offline, excluded, or already restored

**I guess this might be because the database has already been restored.** any changes I should make when I am taking the backup, as the archive which was causing an issue, was made while the backup was being taken.

I have Postgres installed on windows 10. I have master-slave replication ( 1 pc is master and 2 other pc are slaves). I have archive logs saved on a file on master pc and slaves are connected to that file. That file has become quite big and i need to know how to remove log files with pg_archive cleaner. I also have automatic backup jobs on every day. 😊

I am having an issue "resetting" or fixing a backup server I have in BARMAN:

-bash-4.2$ barman check *server-name*
Server *server-name*:
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        not in recovery: OK
        systemid coherence: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: FAILED
        archiver errors: FAILED (duplicates: 1, unknown: 1)

Looking at the BARMAN documentation, it gives a procedure to stop WAL logging. >The cron command ensures that WAL streaming is started for those servers that have requested it, by transparently executing the receive-wal command. >In order to stop the operations started by the cron command, comment out the cron entry and execute: >

receive-wal --stop SERVER_NAME

>You might want to check barman list-server to make sure you get all of your servers. How do we "comment out the cron entry"? I don't see it in the barman user crontab if I do a

-l

. I assume we use some form of

cron

but I dont see any options? Currently I am not backing up this server, but the WAL logs are still getting copied. Any ideas or help would be very appreciated! Thanks!

we face issues with on execution og pg_basebackup once in a while. One cure seems to be to increase wal_keep_size. If you go down that rabbit hole the vuestion on which value is suitable pops up naturally. As the Cybertec Blog is one of sources of truth I am consulting the most I stumbled over this blog post on the matter. It says > When you for example see that your ‘find pg_xlog/ -cmin -60’ (file attributes changed within last hour) yields 3, you’ll know that you’re writing ca 1.2GB (3*16*24) per day and can set wal_keep_segments accordingly. I think that means, if you find 3 files change in the recent 60 minutes, a value of 3 * 24 * 16 MB would be suitable for wal_keep_size. is that a correct interpretation? In one particular case I get 47 wal segments from find pg_wal -ctime -60 -type f ! -empty ! -name *.backup | wc -l, which translates into wal_keep_size = 17.6 GiB (47 * 24 * 16 / 1024). That looks really like a lot to me, but maybe it's the reality we have to face. - The data_directory measures 247 GiB of size. - lsmem shows Total online memory: 31G - there are 6 other postgres instances (most of them significantly smaller (2.5 GiB, 100 MiB, ... for data_directory) then the one we are talking about here) sharing that same Machine Can anybody kindly comment whether my understanding and the conclusion I am drawing from that are reasonable?

Replication is working fine on the other servers, however on DB_B there's some problem; It is unable to receive archives. **Can someone help me out? If I have missed any parameters, please do let me know.** **EDIT:** Primary is DB_A and DR is DB_B Configurations on the primary

NAME               TYPE   VALUE                                                                                 
------------------ ------ ------------------------------------------------------------------------------------- 
log_archive_dest_5 string service=DB_B ASYNC valid_for=(online_logfile,all_roles) db_unique_name=DB_B 


NAME       TYPE   VALUE                              
---------- ------ ---------------------------------- 
fal_client string DB_A                            
fal_server string DB_C, DB_D, DB_E, DB_B 

NAME               TYPE   VALUE                                               
------------------ ------ --------------------------------------------------- 
log_archive_config string DG_CONFIG=(DB_C,DB_D,DB_E,DB_B,DB_A)

TNSPing also successful On DR

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
fal_client                           string      DB_B
fal_server                           string      DB_C, DB_D, DB_E, DB_A
                                                 
												 
												 
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
log_archive_config                   string      dg_config=(DB_C,DB_A,DB_E,DB_D,DB_B)

My main concern is knowing if the Oracle Archive Logs haven't been backed up in a while because something is broken that I may not know about. When I query v$log it appears to be good with First_Time being very recent. I would think v$log wouuld have lots of rows if the logs had not been backing up. SELECT * FROM v$log; Shows only a few, two say current (four returned). select * from v$log_history order by FIRST_TIME desc; Shows lots.

We're running a backup process based on pg_basebackup of a Postgres 14.5 database that creates a lot of WAL archive files and using up huge amounts of disk space. As we don't really need the WAL archive, I thought to disable WAL archiving and I changed these values (everything else is pure default): archive_mode = off # was 'on' before wal_level = minimal # was 'replica' before (there's only a single db instance running) max_wal_senders = 0 # was '10' before (but wal_level = minimal seems to require '0') Maybe there's no real need to lower the wal_level from replica to minimal, but the [WAL documentation](https://www.postgresql.org/docs/current/runtime-config-wal.html) states what seems exactly sufficient for my less than critical database: > minimal removes all logging except the information required to recover from a crash or immediate shutdown. Unfortunately, after the above configuration, the backup process now fails with this error: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0) In other words, max_wal_senders must be zero and also greater than zero at the same time! What can I do to get out of this deadlock?

So I inherited this database. At the very beginning, there were already some files on pg_wal. Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';, it results : **8182** files. Looking at the file's timestamp on pg_wal directory, I see some new files being created but the number of files stays the same. I am suspecting that the rotation takes place. Then I observe the pg_stat_archiver : archived_count = **2**; failed_count = 0 Some 3 hours later, I do count again SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'; It results : **8183**. Obviously a new file is created. Then I do pg_stat_archiver : archived_count = **3**; failed_count = 0 archived_count last_archived_wal last_archived_time failed_count last_failed_wal last_failed_time 3 000000010000009B00000083 22-09-13 13:14:20. 0 stats_reset 2022-09-13 08:14:19 This database is very low, not busy at all. Sometimes no one access it. But there is logical replication that sync some 300 records once a day to another server. Here is my settings : max_wal_size : 1024 Mb min_wal_size : 2048 Mb wal_keep_segment : 0 wal_rotation : ON wal_level : logical wal_compression : ON archive_on : ON archive_mode : test ! -f /Archive/%f && cp %p /Archive/%f archive_timeout : 900 Questions : 1. Why does the second count of pg_wal increase? I am expecting that it decreases because the archived number increases. 2. If wal_keep_segment = 0, why the number of WALs in pg_wal never 0 ? In the documentation, it says : > If wal_keep_segments is zero (the default), the system doesn't keep > any extra segments for standby purposes, 3. Why the minimum number of WALs, during low period, **does not** = min_wal_size + wal_keep_segment? Please enlighten me. Thank you so much

I am using logical replication. Currently I am using stored procedure to pump 3K data per hour to see if the WAL files increase. It does increase by 1 file every 12k records. But the WAL archiving is not running even though I have set it ON. Here is my setting : wal_level : Logical wal_log_hints : ON wal_keep_segments : 0 Hot_standby : ON archive_command : cp %p /Archive%f archive_mode : ON max_wal_size : 1 GB min_wal_size : 2 GB checkpoint_timeout : 300 s checkpoint_flush_after : 32 8kB unit wal_sync_method : fdatasync fsync : ON Then I run : SELECT * FROM pg_stat_archiver;. The result is below : Name |Value | ------------------+-----------------------------+ archived_count |0 | last_archived_wal | | last_archived_time| | failed_count |33552 | last_failed_wal |00000001000000960000002C | last_failed_time |2022-08-03 15:07:54.849 +0700| stats_reset |2022-06-30 13:46:05.298 +0700| I don't see anything from the /Archive directory either, its simply empty. What should I do to make the archiving running ? How can I make sure that the archiving is running, what parameters do I have to monitor ? I am using Postgres 12, Ubuntu 12.11 Thanks

I'm working on an application, which uses different DLLs, each of them doing something on an MS-SQL database. After a while, some entries in the database don't look as I would want them, and I would like to monitor all the modifications, which have led to this situation, something like:

`
at 12:00:01.234 : modify "Status" from 2 to 3
at 12:00:02.345 : modify "Destination" from "ABC01" to "ABC02".
at 12:00:02.456 : modity "Status" from 3 to 4

` Because currently, I see that "Status" is 4 and that "Destination" is "ABC02", but I have no idea about when and how this has happened. (My question is similar to asking to read the archive logs of an MS-SQL database, if this makes any sense :-) ) Thanks in advance **Edit: some clarification:** On the internet, there are quite some explanations on database monitoring, but in almost all cases, the idea is to measure and to improve the performance. For my question, this is not the case: I just want to understand, piece by piece, how the data in one particular table go from one value to the other one. **Edit: fn_dblog** In the meantime, I've started using fn_dblog, as follows: SELECT * FROM fn_dblog(null,null) WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%' ... but I have no idea how to see the timestamp. Does anybody have an idea? **Edit2 about fn_dblog:** As far as specific fn_dblog information is concerned, I've created a new question for this subject.

I'm working on an SQL server database, containing orders and machines, executing those orders. Not more than one active order can be assigned to a machine at the same time. In other words: this SQL request can never yield a result: SELECT MachineId FROM Orders WHERE (Orders.Status=1) GROUP BY MachineId HAVING COUNT(Id)>1 I have this SQL request open in a Microsoft SQL Server Management Studio environment, and every time I press F5, I indeed see no results. However, I am sure that there have been times where that SQL query did yield results, and I'm interested in those times and the results of that query at those times. Does anybody have an idea on how I can find this out? (The "archive-log" tag is just an idea) Thanks in advance

**To preface...** I have mentioned this before in a previous question, but I'll add it again: Our Senior Oracle DBA has just recently passed away, and I've been taking over. My level of experience is just a junior level. So there is a lot I still don't understand. So I appreciate all the tips I can get, as this is a very frightening position for me. **The Question...** We have been going through a series of storage issues. There was a point when our filesystem filled to 100% and all our backups stopped recording. We have resolved the space issue, but since then, I noticed that our Archivelog backups are no longer being generated automatically even though they're still scheduled on EM. I checked OEM job activity and it shows that the archivelog backup "successfully" completed but with an empty output log. What's going on? The RMAN configuration appears to be the same. Just FYI, I don't know how to automate RMAN backups. After our senior passed, I have just been dealing with what was already automated.

RMAN> backup device type disk tag 'ARCHIVELOGS_BACKUP_062421100006' archivelog a
ll not backed up delete all input;
Starting backup at 2021-06-24 10:00:11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5066 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=31060 RECID=93036 STAMP=1076061622
channel ORA_DISK_1: starting piece 1 at 2021-06-24 10:00:23
channel ORA_DISK_1: finished piece 1 at 2021-06-24 10:00:38
piece handle=/RMAN/FLASH/WEBPRD01/WEBPRD01/backupset/2021_06_24/o1_mf_annnn_ARCH
IVELOGS_BACKUP_0_jf93zr01_.bkp tag=ARCHIVELOGS_BACKUP_062421100006 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/RMAN/FLASH/WEBPRD01/WEBPRD01/archivelog/2021_06_24/o1_mf
_1_31060_jf93zjxr_.arc RECID=93036 STAMP=1076061622
archived log file name=/ora06/oracle/arch/WEBPRD01/log_1_31060_916831033.arc REC
ID=93035 STAMP=1076061622
Finished backup at 2021-06-24 10:00:39

Starting Control File and SPFILE Autobackup at 2021-06-24 10:00:39
piece handle=/RMAN/FLASH/WEBPRD01/WEBPRD01/autobackup/2021_06_24/o1_mf_s_1076061
639_jf9407w8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-06-24 10:00:40


RMAN> exit;

Recovery Manager complete.

Checking RMAN repository settings...
Backup Succeeded.

I have seen some recommendations in the Oracle Docs about what to do if the FRA is full. For example I have this config also: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE'; The Databases have Dataguard and RAC. Oracle says: 1. Make more disk space - I cannot BACKUP RECOVERY AREA - I am interrested in this option. What does it that is not the same as the standard full backup where FRA also gets cleaned? Is it faster? I see it deletes the flashback logs, maybe I need them? If you use some other alternatives, I would be much appreciated. Thanks in advance.

I have a production DB PostgreSQL 9.6 that is on master/standby structure. Every at 1 AM I restore a few huge tables(from the other DB server) to the master node during restore till finished the archive log generated around 100GB. Is there any good way to minimize the archive_log from the pg_restore task?

My database is running in archive log mode, but flashback is disabled because I do not need it. The FRA is set to /flasharch mount point. Now, I am going to change the FRA location to /flasharch01 mount point due to space limitation. What to do with archivelog in the old location. Do I need to move the old files to new location? Could you guys please guide me step by step? RMAN uses control file instead of recovery catalog. Should I create a recovery catalog? if yes.. please how? I need steps please. As, I can't increase the FRA location. The only way to change the location is the solution. Could you please share the steps how to move the old files and how to catalog them? RMAN using control file instead of recovery catalog.

I'm trying to enable archivelog mode in Oracle database. For that I shutdown the services and used startup mount. Instead of database getting mounted, I'm getting error ORA- 12528 TNS - All instances are blocking new connections. Any one can guide on this.

I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on archiving by setting up archive_mode = on, does Postgres have an internal mechanism to rotate WAL files out of pg_xlog? if so, what is the frequency? On a slightly different note, what is the impact of setting archive_mode = on but not configuring an archive_command - is there a default location Postgres will attempt to archive to?

I have replication with PostgreSQL, so I want to set archive_command = 'cp %p /var/lib/postgresql/11/main/archive/%f' but I also want to save WAL files to another safe place: S3. So is it possible to have multiple archive_commands? And can I put this command on the slave to push wal files to S3 instead of on the master - I want master to have less performance impact.

I have an Oracle 11g on RHEL server. My /oradata volume reach 95% disk usage : [ora11g@bocc7-1-qrec-01 ~]$ df -h /dev/sdb1 689G 571G 84G 95% /oradata The Flash Recovery Area (FRA) is the larger folder : [ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/* | sort -h 50G /oradata/qtest 175G /oradata/qrec 346G /oradata/flash_recovery_area Inside the FRA I can see that the archive logs take approx 230Gb: [ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/flash_recovery_area/qrec/* 232G /oradata/flash_recovery_area/qrec/archivelog 7.9G /oradata/flash_recovery_area/qrec/onlinelog I checked the v$recovery_file_dest table and my SPACE_LIMIT is 20Gb and my SPACE_USED is only 10Mb! Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production qrec> SELECT * FROM V$RECOVERY_FILE_DEST; NAME -------------------------------------------------------------------------------- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- --------------- /oradata/flash_recovery_area 2.1475E+10 10984448 0 1 _So, I deduced that it had nothing to do with this table._ Now I'm checking with RMAN : RMAN> crosscheck archivelog all; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=259 device type=DISK validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc RECID=1057 STAMP=969146207 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1057_965658004.arc RECID=1058 STAMP=969149808 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1058_965658004.arc RECID=1059 STAMP=969153409 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1059_965658004.arc RECID=1060 STAMP=969157010 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1060_965658004.arc RECID=1061 STAMP=969160610 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1061_965658004.arc RECID=1062 STAMP=969164210 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1062_965658004.arc RECID=1063 STAMP=969167813 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1063_965658004.arc RECID=1064 STAMP=969171413 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1064_965658004.arc RECID=1065 STAMP=969175014 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1065_965658004.arc RECID=1066 STAMP=969178614 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1066_965658004.arc RECID=1067 STAMP=969182214 validation succeeded for archived log archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1067_965658004.arc RECID=1068 STAMP=969185814 Crosschecked 12 objects RMAN> delete noprompt expired archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=259 device type=DISK specification does not match any archived log in the repository So the **1st question** is: Why does RMAN not see my **2752** ARC files here : [ora11g@bocc7-1-qrec-01 ~]$ ls -1 /oradata/flash_recovery_area/qrec/archivelog | wc -l 2752 And the **2nd question** is: What should I do to free some disk space by removing old archivelog files? (the older ones are more than 1 year old!!) : [ora11g@bocc7-1-qrec-01 archivelog]$ find -type f -printf '%T+ %p\n' | sort | head -n 1 2017-02-22+16:54:41.0000000000 ./log_2_1_936636608.arc

I am seeing many pg_log files full of following errors

2019-07-08 00:04:06 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST LOG:  archive command failed with exit code 1
2019-07-08 00:04:07 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST WARNING:  transaction log file "000000010000002100000025" could not be archived: too many failures
2019-07-08 00:05:07 CEST LOG:  archive command failed with exit code 1**

postgres.conf having settings

checkpoint_segments = 256
wal_buffers = 256MB
wal_level = archive
archive_mode = on
port = 7112
work_mem = 128MB
shared_buffers = 512MB
effective_cache_size = 32719MB
listen_addresses = '*'
logging_collector = on
log_line_prefix = '%t '
archive_command = 'copy "%p" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/%f"'**

I have checked pg_xlog folder for this file. This file is not present in pg_xlog but in pg_xlog\archive_status with following permissions

pg/pg_xlog\archive_status\000000010000002100000025.ready 

KDLF\adminkdlf:(I)(F)
VORDEFINIERT\Administratoren:(I)(F)
NT-AUTORITŽT\SYSTEM:(I)(F)
VORDEFINIERT\Benutzer:(I)(RX)

My disk is getting full. Please tell why this issue is coming because of this others logs are still not archived. I am not sure if I am missing something here.