It could be, like the herpes virus you mentioned. Herpesviruses are infamous for integrating themselves into our cell DNA genome and lie dormant until some stressors re-activate it.

And there's evidence that SARS-CoV-2 can integrate into the cell genome: https://www.pnas.org/content/118/21/e2105968118

But the research on this is very limited, so I still have my doubts if SARS-CoV-2 could really lie dormant and reactivate years later.